注意是: Cpno 外键是 Cno,而 Cno 又是这张表 的 主键。
Cno:课程号 Cname: 课程名 Cpno:先修课 Ccredit:学分
建表语句:
CREATE TABLE `test`.`course` (
`Cno` varchar(10) NOT NULL,
`Cname` varchar(50) DEFAULT NULL,
`Cpno` varchar(10) DEFAULT NULL,
`Ccredit` smallint(10) DEFAULT NULL,
PRIMARY KEY (`Cno`),
KEY `Cpno` (`Cpno`),
CONSTRAINT `course_ibfk_1` FOREIGN KEY (`Cpno`) REFERENCES `course` (`Cno`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk;
建表成功后,插入数据:
insert into Course
values
('1','数据库系统','5',4),
('2','高等数续','',2),
('3','信息系统','1',4),
('4','操作系统','6',3),
('5','数据结构','7',4),
('6','Java程序设计','',2),
('7','编译原理','6',4)
提示错误:
error :1452
Cannot add or update a child row: a foreign key constraint fails (`test`.`course`, CONSTRAINT `course_ibfk_1` FOREIGN KEY (`Cpno`) REFERENCES `course` (`Cno`))
Cno:课程号 Cname: 课程名 Cpno:先修课 Ccredit:学分
建表语句:
CREATE TABLE `test`.`course` (
`Cno` varchar(10) NOT NULL,
`Cname` varchar(50) DEFAULT NULL,
`Cpno` varchar(10) DEFAULT NULL,
`Ccredit` smallint(10) DEFAULT NULL,
PRIMARY KEY (`Cno`),
KEY `Cpno` (`Cpno`),
CONSTRAINT `course_ibfk_1` FOREIGN KEY (`Cpno`) REFERENCES `course` (`Cno`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk;
建表成功后,插入数据:
insert into Course
values
('1','数据库系统','5',4),
('2','高等数续','',2),
('3','信息系统','1',4),
('4','操作系统','6',3),
('5','数据结构','7',4),
('6','Java程序设计','',2),
('7','编译原理','6',4)
提示错误:
error :1452
Cannot add or update a child row: a foreign key constraint fails (`test`.`course`, CONSTRAINT `course_ibfk_1` FOREIGN KEY (`Cpno`) REFERENCES `course` (`Cno`))
2. 用null代替'', 由于你的表中不会出现'', ''和null是不同的。mysql> CREATE TABLE `test`.`course` (
-> `Cno` varchar(10) NOT NULL,
-> `Cname` varchar(50) DEFAULT NULL,
-> `Cpno` varchar(10) DEFAULT NULL,
-> `Ccredit` smallint(10) DEFAULT NULL,
-> PRIMARY KEY (`Cno`),
-> KEY `Cpno` (`Cpno`),
-> CONSTRAINT `course_ibfk_1` FOREIGN KEY (`Cpno`) REFERENCES `course` (`Cno`)
-> ) ENGINE=InnoDB ;
Query OK, 0 rows affected (0.11 sec)mysql>
mysql> insert into Course
-> values
-> ('2','高等数续',null,2),
-> ('6','Java程序设计',null,2),
-> ('4','操作系统','6',3),
-> ('7','编译原理','6',4),
-> ('5','数据结构','7',4),
-> ('1','数据库系统','5',4),
-> ('3','信息系统','1',4);
Query OK, 7 rows affected (0.06 sec)
Records: 7 Duplicates: 0 Warnings: 0mysql> select * from Course;
+-----+--------------+------+---------+
| Cno | Cname | Cpno | Ccredit |
+-----+--------------+------+---------+
| 1 | 数据库系统 | 5 | 4 |
| 2 | 高等数续 | NULL | 2 |
| 3 | 信息系统 | 1 | 4 |
| 4 | 操作系统 | 6 | 3 |
| 5 | 数据结构 | 7 | 4 |
| 6 | Java程序设计 | NULL | 2 |
| 7 | 编译原理 | 6 | 4 |
+-----+--------------+------+---------+
7 rows in set (0.00 sec)mysql>
+-----+--------------+------+---------+
| Cno | Cname | Cpno | Ccredit |
+-----+--------------+------+---------+
| 1 | | 7 | 4 |
| 2 | | NULL | 2 |
| 3 | | 1 | 4 |
| 4 | | 6 | 3 |
| 5 | | 7 | 4 |
| 6 | Java | NULL | 2 |
| 7 | | 6 | 4 |
+-----+--------------+------+---------+
7 rows in set (0.00 sec)
set names 'gbk'; 试一下。
mysql> set names 'gbk';
Query OK, 0 rows affected (0.01 sec)mysql> select * from Course;
+-----+--------------+------+---------+
| Cno | Cname | Cpno | Ccredit |
+-----+--------------+------+---------+
| 1 | | 7 | 4 |
| 2 | | NULL | 2 |
| 3 | | 1 | 4 |
| 4 | | 6 | 3 |
| 5 | | 7 | 4 |
| 6 | Java | NULL | 2 |
| 7 | | 6 | 4 |
+-----+--------------+------+---------+
7 rows in set (0.00 sec)
---------------------------
这怎么办啊