mirror of
https://github.com/MariaDB/server.git
synced 2025-02-15 01:45:33 +01:00
0301ef38b4
- MDEV-34392(commit cc810e64d4) adds
the check for nullability of foreign key column when foreign key
relation is of UPDATE_CASCADE or UPDATE SET NULL. This check
makes DDL fail when it violates foreign key nullability.
This patch basically does the nullability check for foreign key
column only for strict sql mode
55 lines
2.8 KiB
Text
55 lines
2.8 KiB
Text
--- 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;
|