mysql> CREATE DATABASE testFK;
Query OK, 1 row affected (0.02 sec)
mysql> USE testFK;
Database changed
mysql> CREATE TABLE course(cNo int NOT NULL,cName VARCHAR(12),PRIMARY KEY(cNo));
Query OK, 0 rows affected (0.06 sec)
mysql> INSERT INTO course VALUES(1,'zucheng'),(2,'shujuku'),(3,'shuxue');
Query OK, 3 rows affected (0.03 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> CREATE TABLE Student(sNo int NOT NULL,cNo INT NOT NULL,sName VARCHAR(12),PRIMARY KE
Y(sNo),FOREIGN KEY(cNo) REFERENCES course(cNO) ON DELETE CASCADE ON UPDATE CASCADE);
Query OK, 0 rows affected (0.08 sec)
mysql> INSERT INTO Student VALUES(1,1,'zucheng'),(2,1,'shujuku'),(3,2,'shuxue');
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM student;
+-----+-----+---------+
| sNo | cNo | sName |
+-----+-----+---------+
| 1 | 1 | zucheng |
| 2 | 1 | shujuku |
| 3 | 4 | shuxue |
+-----+-----+---------+
3 rows in set (0.00 sec)
mysql> DELETE * FROM course WHERE cNo=4;
mysql> SELECT * FROM student;
+-----+-----+---------+
| sNo | cNo | sName |
+-----+-----+---------+
| 1 | 1 | zucheng |
| 2 | 1 | shujuku |
+-----+-----+---------+
2 rows in set (0.00 sec)
mysql> UPDATE course set cNo=3 WHERE cNo=1;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> SELECT * FROM student;
+-----+-----+---------+
| sNo | cNo | sName |
+-----+-----+---------+
| 1 | 3 | zucheng |
| 2 | 3 | shujuku |
+-----+-----+---------+
2 rows in set (0.00 sec)
mysql> UPDATE student SET cNo=4 WHERE sNo=1;
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`tes
tfk/student`, CONSTRAINT `student_ibfk_1` FOREIGN KEY (`cNo`) REFERENCES `course` (`cNo`)
ON DELETE CASCADE ON UPDATE CASCADE)
mysql> UPDATE student SET cNo=4 WHERE sNo=1;
参考完整性ON DELETE/UPDATE CASCADE是被引用表(父表)中违反时引用表(子表)的联级...
最新推荐文章于 2021-12-30 13:22:23 发布