1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
|
--- foreign_sql_mode.result 2025-01-21 17:23:46.014938931 +0530
+++ foreign_sql_mode.reject 2025-01-21 17:24:11.783981181 +0530
@@ -3,20 +3,20 @@
CREATE TABLE t1(f1 INT, f2 INT, PRIMARY KEY(f1), KEY(f2))ENGINE=InnoDB;
CREATE TABLE t2(f1 INT, FOREIGN KEY(f1) REFERENCES t1(f2) ON UPDATE CASCADE)ENGINE=InnoDB;
ALTER TABLE t2 MODIFY COLUMN f1 INT NOT NULL;
-ERROR HY000: Column 'f1' cannot be NOT NULL: needed in a foreign key constraint 't2_ibfk_1' SET NULL
INSERT INTO t1 VALUES(1, 1);
INSERT INTO t2 VALUES(1);
UPDATE t1 SET f2= NULL;
+ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`f1`) REFERENCES `t1` (`f2`) ON UPDATE CASCADE)
DELETE FROM t2;
SELECT * FROM t1;
f1 f2
-1 NULL
+1 1
DROP TABLE t2, t1;
# modify child column NOT NULL ON UPDATE SET NULL
CREATE TABLE t1(f1 INT, f2 INT, PRIMARY KEY(f1), KEY(f2))ENGINE=InnoDB;
CREATE TABLE t2(f1 INT, f2 INT, FOREIGN KEY(f1) REFERENCES t1(f1) ON UPDATE SET NULL)ENGINE=InnoDB;
ALTER TABLE t2 MODIFY COLUMN f1 INT NOT NULL;
-ERROR HY000: Column 'f1' cannot be NOT NULL: needed in a foreign key constraint 't2_ibfk_1' SET NULL
+ERROR HY000: Error on rename of 'OLD_FILE_NAME' to 'NEW_FILE_NAME' (errno: 150 "Foreign key constraint is incorrectly formed")
INSERT INTO t1 VALUES(1, 1);
INSERT INTO t2 VALUES(1, 1);
UPDATE t1 SET f1= 2;
@@ -28,7 +28,7 @@
CREATE TABLE t1(f1 INT, f2 INT, PRIMARY KEY(f1), KEY(f2))ENGINE=InnoDB;
CREATE TABLE t2(f1 INT, f2 INT, FOREIGN KEY (f2) REFERENCES t1(f2) ON DELETE SET NULL)ENGINE=InnoDB;
ALTER TABLE t2 MODIFY COLUMN f2 INT NOT NULL;
-ERROR HY000: Column 'f2' cannot be NOT NULL: needed in a foreign key constraint 't2_ibfk_1' SET NULL
+ERROR HY000: Error on rename of 'OLD_FILE_NAME' to 'NEW_FILE_NAME' (errno: 150 "Foreign key constraint is incorrectly formed")
DROP TABLE t2, t1;
# modify parent column NULL ON UPDATE CASCADE child column NOT NULL
CREATE TABLE `t#1`(f1 INT, f2 INT NOT NULL, PRIMARY KEY(f1), KEY(f2))ENGINE=InnoDB;
@@ -36,11 +36,10 @@
FOREIGN KEY(f1) REFERENCES `t#1`(f2)
ON UPDATE CASCADE)ENGINE=InnoDB;
ALTER TABLE `t#1` MODIFY COLUMN f2 INT;
-ERROR HY000: Cannot change column 'f2': used in a foreign key constraint 't#2_ibfk_1' of table 'test.t#2'
INSERT INTO `t#1` VALUES(1, 1);
INSERT INTO `t#2` VALUES(1);
UPDATE `t#1` SET f2= NULL;
-ERROR 23000: Column 'f2' cannot be null
+ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t#2`, CONSTRAINT `t#2_ibfk_1` FOREIGN KEY (`f1`) REFERENCES `t#1` (`f2`) ON UPDATE CASCADE)
DELETE FROM `t#2`;
SELECT * FROM `t#1`;
f1 f2
@@ -56,6 +55,5 @@
PRIMARY KEY(f1, f2),
FOREIGN KEY(f2, f3) REFERENCES t1(f2, f1)
ON UPDATE CASCADE)ENGINE=InnoDB;
-ERROR HY000: Can't create table `test`.`t2` (errno: 150 "Foreign key constraint is incorrectly formed")
DROP TABLE IF EXISTS t2;
DROP TABLE IF EXISTS t1;
|
