mirror of
https://github.com/MariaDB/server.git
synced 2025-12-19 02:35:42 +01:00
158 lines
8.6 KiB
Text
158 lines
8.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 '1' SET NULL
|
|
DROP TABLE t2, t1;
|
|
# modify child column NOT NULL ON DELETE 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 DELETE CASCADE)ENGINE=InnoDB;
|
|
ALTER TABLE t2 MODIFY COLUMN f1 INT NOT 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 '1' SET NULL
|
|
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 '1' SET NULL
|
|
DROP TABLE t2, t1;
|
|
# modify child column NOT NULL ON UPDATE RESTRICT..parent column 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 UPDATE RESTRICT)ENGINE=InnoDB;
|
|
ALTER TABLE t2 MODIFY COLUMN f2 INT NOT NULL;
|
|
DROP TABLE t2, t1;
|
|
# modify child column NOT NULL ON DELETE RESTRICT..parent column 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 RESTRICT)ENGINE=InnoDB;
|
|
ALTER TABLE t2 MODIFY COLUMN f2 INT NOT NULL;
|
|
DROP TABLE t2, t1;
|
|
# modify child column NOT NULL ON UPDATE NO ACTION..PARENT COLUMN 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 UPDATE NO ACTION)ENGINE=InnoDB;
|
|
ALTER TABLE t2 MODIFY COLUMN f2 INT NOT NULL;
|
|
DROP TABLE t2, t1;
|
|
# modify child column NOT NULL ON DELETE NO ACTION..PARENT COLUMN 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 NO ACTION)ENGINE=InnoDB;
|
|
ALTER TABLE t2 MODIFY COLUMN f2 INT NOT 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 '1' of table 'test.t#2'
|
|
DROP TABLE `t#2`, `t#1`;
|
|
# modify parent column NULL ON DELETE CASCADE child column NOT NULL
|
|
CREATE TABLE t1(f1 INT, f2 INT NOT NULL, PRIMARY KEY(f1), KEY(f2))ENGINE=InnoDB;
|
|
CREATE TABLE t2(f1 INT NOT NULL, FOREIGN KEY(f1) REFERENCES t1(f2) ON DELETE CASCADE)ENGINE=InnoDB;
|
|
ALTER TABLE t1 MODIFY COLUMN f2 INT;
|
|
DROP TABLE t2, t1;
|
|
# modify parent column NULL ON UPDATE SET NULL child column NOT NULL
|
|
CREATE TABLE t1(f1 INT, f2 INT NOT NULL, PRIMARY KEY(f1), KEY(f2))ENGINE=InnoDB;
|
|
CREATE TABLE t2(f1 INT NOT NULL, FOREIGN KEY(f1) REFERENCES t1(f2) ON UPDATE SET NULL)ENGINE=InnoDB;
|
|
ERROR HY000: Can't create table `test`.`t2` (errno: 150 "Foreign key constraint is incorrectly formed")
|
|
DROP TABLE t1;
|
|
# modify parent column NULL ON DELETE SET NULL child NOT NULL
|
|
CREATE TABLE t1(f1 INT, f2 INT NOT NULL, PRIMARY KEY(f1), KEY(f2))ENGINE=InnoDB;
|
|
CREATE TABLE t2(f1 INT NOT NULL, FOREIGN KEY(f1) REFERENCES t1(f2) ON DELETE SET NULL)ENGINE=InnoDB;
|
|
ERROR HY000: Can't create table `test`.`t2` (errno: 150 "Foreign key constraint is incorrectly formed")
|
|
DROP TABLE t1;
|
|
# modify parent column NULL ON UPDATE RESTRICT child column NOT NULL
|
|
CREATE TABLE t1(f1 INT, f2 INT NOT NULL, PRIMARY KEY(f1), KEY(f2))ENGINE=InnoDB;
|
|
CREATE TABLE t2(f1 INT NOT NULL, FOREIGN KEY(f1) REFERENCES t1(f2) ON UPDATE RESTRICT)ENGINE=InnoDB;
|
|
ALTER TABLE t1 MODIFY COLUMN f2 INT;
|
|
DROP TABLE t2, t1;
|
|
# modify parent column NULL ON DELETE RESTRICT child column NOT NULL
|
|
CREATE TABLE t1(f1 INT, f2 INT NOT NULL, PRIMARY KEY(f1), KEY(f2))ENGINE=InnoDB;
|
|
CREATE TABLE t2(f1 INT NOT NULL, FOREIGN KEY(f1) REFERENCES t1(f2) ON UPDATE RESTRICT)ENGINE=InnoDB;
|
|
ALTER TABLE t1 MODIFY COLUMN f2 INT;
|
|
DROP TABLE t2, t1;
|
|
# modify parent column NULL ON UPDATE NO ACTION child column NOT NULL
|
|
CREATE TABLE t1(f1 INT, f2 INT NOT NULL, PRIMARY KEY(f1), KEY(f2))ENGINE=InnoDB;
|
|
CREATE TABLE t2(f1 INT NOT NULL, FOREIGN KEY(f1) REFERENCES t1(f2) ON UPDATE NO ACTION)ENGINE=InnoDB;
|
|
ALTER TABLE t1 MODIFY COLUMN f2 INT;
|
|
DROP TABLE t2, t1;
|
|
# modify parent column NULL ON DELETE NO ACTION child column NOT NULL
|
|
CREATE TABLE t1(f1 INT, f2 INT NOT NULL, PRIMARY KEY(f1), KEY(f2))ENGINE=InnoDB;
|
|
CREATE TABLE t2(f1 INT NOT NULL, FOREIGN KEY(f1) REFERENCES t1(f2) ON DELETE NO ACTION)ENGINE=InnoDB;
|
|
ALTER TABLE t1 MODIFY COLUMN f2 INT;
|
|
DROP TABLE t2, t1;
|
|
# foreign key constraint for multiple columns
|
|
# modify parent column NULL ON UPDATE CASCADE child column NOT NULL
|
|
CREATE TABLE t1(f1 INT NOT NULL, f2 INT NOT NULL,
|
|
INDEX(f1, f2))ENGINE=InnoDB;
|
|
CREATE TABLE t2(f1 INT NOT NULL, f2 INT NOT NULL,
|
|
INDEX(f1, f2),
|
|
FOREIGN KEY(f1, f2) REFERENCES t1(f1, f2) ON
|
|
UPDATE CASCADE)ENGINE=InnoDB;
|
|
ALTER TABLE t1 MODIFY COLUMN f1 INT;
|
|
ERROR HY000: Cannot change column 'f1': used in a foreign key constraint '1' of table 'test.t2'
|
|
DROP TABLE t2, t1;
|
|
# modify child column NOT NULL ON UPDATE CASCADE parent column NULL
|
|
CREATE TABLE t1(f1 INT, f2 INT, INDEX(f1, f2))ENGINE=InnoDB;
|
|
CREATE TABLE t2(f1 INT, f2 INT, INDEX(f1, f2),
|
|
FOREIGN KEY(f1, f2) REFERENCES t1(f1, f2) ON
|
|
UPDATE CASCADE)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 '1' SET NULL
|
|
DROP TABLE t2, t1;
|
|
# allow foreign key constraints when parent table created later
|
|
SET FOREIGN_KEY_CHECKS=0;
|
|
CREATE TABLE t2(f1 INT, FOREIGN KEY(f1) REFERENCES t1(f2) ON UPDATE CASCADE)ENGINE=InnoDB;
|
|
SET FOREIGN_KEY_CHECKS=1;
|
|
ALTER TABLE t2 MODIFY COLUMN f1 INT NOT NULL;
|
|
CREATE TABLE t1(f1 INT, f2 INT, PRIMARY KEY(f1), KEY(f2))ENGINE=InnoDB;
|
|
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 `1` FOREIGN KEY (`f1`) REFERENCES `t1` (`f2`) ON UPDATE CASCADE)
|
|
SELECT * FROM t2;
|
|
f1
|
|
1
|
|
SET FOREIGN_KEY_CHECKS=0;
|
|
UPDATE t1 SET f2= NULL;
|
|
SELECT * FROM t2;
|
|
f1
|
|
1
|
|
DROP TABLE t2, t1;
|
|
# Modify column + Drop column & Drop foreign key constraint
|
|
CREATE TABLE t1(f1 INT, f2 INT, KEY(f1), KEY(f2))ENGINE=InnoDB;
|
|
CREATE TABLE t2(f1 INT, f2 INT, f3 INT,
|
|
FOREIGN KEY fdx(f2) REFERENCES t1(f1),
|
|
FOREIGN KEY fdx2(f3) REFERENCES t1(f2))ENGINE=InnoDB;
|
|
ALTER TABLE t2 MODIFY f2 INT NOT NULL, DROP FOREIGN KEY fdx;
|
|
ALTER TABLE t2 ADD FOREIGN KEY fdx (f2) REFERENCES t1(f1);
|
|
ALTER TABLE t2 DROP COLUMN f2, DROP FOREIGN KEY fdx;
|
|
DROP TABLE t2, t1;
|
|
# Drop foreign index & modify column
|
|
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;
|
|
SET FOREIGN_KEY_CHECKS=0;
|
|
ALTER TABLE `t#2` DROP INDEX f1;
|
|
SET FOREIGN_KEY_CHECKS=1;
|
|
ALTER TABLE `t#1` MODIFY COLUMN f2 INT;
|
|
ERROR HY000: Cannot change column 'f2': used in a foreign key constraint '1' of table 'test.t#2'
|
|
DROP TABLE `t#2`, `t#1`;
|
|
# Drop referenced index and modify column
|
|
CREATE TABLE `t#1`(f1 INT, f2 INT, PRIMARY KEY(f1), KEY(f2))ENGINE=InnoDB;
|
|
CREATE TABLE `t#2`(f1 INT, FOREIGN KEY(f1) REFERENCES `t#1`(f2) ON UPDATE CASCADE)ENGINE=InnoDB;
|
|
SET FOREIGN_KEY_CHECKS=0;
|
|
ALTER TABLE `t#1` DROP INDEX f2;
|
|
SET FOREIGN_KEY_CHECKS=1;
|
|
ALTER TABLE `t#2` MODIFY COLUMN f1 INT NOT NULL;
|
|
ERROR HY000: Column 'f1' cannot be NOT NULL: needed in a foreign key constraint '1' SET NULL
|
|
DROP TABLE `t#2`, `t#1`;
|
|
# Self referential modifying column
|
|
CREATE TABLE t1(f1 INT, f2 INT, index(f2), foreign key(f1) references t1(f2) ON UPDATE CASCADE)engine=innodb;
|
|
ALTER TABLE t1 MODIFY COLUMN f2 INT NOT NULL;
|
|
ALTER TABLE t1 MODIFY COLUMN f1 INT NOT NULL;
|
|
ALTER TABLE t1 MODIFY COLUMN f1 INT;
|
|
DROP TABLE t1;
|