mirror of
https://github.com/MariaDB/server.git
synced 2025-02-14 17:35:35 +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
65 lines
2.6 KiB
Text
65 lines
2.6 KiB
Text
call mtr.add_suppression("InnoDB: In ALTER TABLE .* has or is referenced in foreign key constraints which are not compatible with the new table definition.");
|
|
# modify child column NOT NULL on UPDATE CASCADE..parent column NULL
|
|
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;
|
|
DELETE FROM t2;
|
|
SELECT * FROM t1;
|
|
f1 f2
|
|
1 NULL
|
|
UPDATE t1 SET f2 = NULL;
|
|
SELECT * FROM t1;
|
|
f1 f2
|
|
1 NULL
|
|
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
|
|
INSERT INTO t1 VALUES(1, 1);
|
|
INSERT INTO t2 VALUES(1, 1);
|
|
UPDATE t1 SET f1= 2;
|
|
SELECT * FROM t2;
|
|
f1 f2
|
|
NULL 1
|
|
DROP TABLE t2, t1;
|
|
# modify child column NOT NULL ON DELETE 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 (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
|
|
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;
|
|
CREATE TABLE `t#2`(f1 INT NOT NULL,
|
|
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
|
|
DELETE FROM `t#2`;
|
|
SELECT * FROM `t#1`;
|
|
f1 f2
|
|
1 1
|
|
DROP TABLE `t#2`, `t#1`;
|
|
CREATE TABLE t1(f1 INT NOT NULL AUTO_INCREMENT,
|
|
f2 INT DEFAULT NULL,
|
|
PRIMARY KEY(f1),
|
|
FOREIGN KEY(f2) REFERENCES t1(f1))ENGINE=InnoDB;
|
|
CREATE TABLE t2 (f1 INT NOT NULL,
|
|
f2 INT NOT NULL,
|
|
f3 INT DEFAULT NULL,
|
|
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;
|