mysql 外键能重复吗_mysql 作为外键的值不能重复吗

CREATE TABLE `student` (

`id` int(10) unsigned NOT NULL PRIMARY KEY,

`name` varchar(50) NOT NULL DEFAULT ''

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

CREATE TABLE `course` (

`id` int(10) unsigned NOT NULL PRIMARY KEY,

`name` varchar(50) NOT NULL DEFAULT ''

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

CREATE TABLE `score` (

`student_id` int(10) unsigned,

`course_id` int(10) unsigned,

`score` int(3) NOT NULL,

INDEX stu_idx (student_id),

INDEX cou_idx (course_id),

FOREIGN KEY (`student_id`) REFERENCES `student` (`id`),

FOREIGN KEY (`course_id`) REFERENCES `course` (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

INSERT INTO `student`(`id`, `name`) VALUES (1, '张三');

INSERT INTO `student`(`id`, `name`) VALUES (2, '李四');

INSERT INTO `course` (`id`, `name`) VALUES (1, '语文');

INSERT INTO `course` (`id`, `name`) VALUES (2, '数学');

INSERT INTO `score`(`student_id`, `course_id`, `score`) VALUES (1, 1, '80');

INSERT INTO `score`(`student_id`, `course_id`, `score`) VALUES (1, 2, '82');

INSERT INTO `score`(`student_id`, `course_id`, `score`) VALUES (2, 1, '90');

INSERT INTO `score`(`student_id`, `course_id`, `score`) VALUES (2, 2, '92');

再插入一条相同的记录,并不会出错:

INSERT INTO `score`(`student_id`, `course_id`, `score`) VALUES (2, 2, '92');

如果你在成绩表score中加入了唯一约束 UNIQUE KEY (`student_id`, `course_id`)

则会出错: Duplicate entry '2-2' for key 'student_id'

插入一个student表中没有的学生编号(3),因为外键约束,会出错:

INSERT INTO `score`(`student_id`, `course_id`, `score`) VALUES (3, 2, '92');

出错: Cannot add or update a child row:

a foreign key constraint fails (`yabase`.`score`,

CONSTRAINT `score_ibfk_1`

FOREIGN KEY (`student_id`) REFERENCES `student` (`id`))

MySQL的MyISAM引擎不支持外键,可见外键不是数据库必须的,外键约束其实可以在应用程序层面实现.

还有,表连接也不需要外键参与,所以,像下面关系,其实并不需要外键约束:

文章表 post

分类表 tag

关系表 relation