polardbxengine/mysql-test/r/foreign_key.result

drop table if exists t_34455;
create table t_34455 (
a int not null,
foreign key (a) references t3 (a) match full match partial);
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'match partial)' at line 3
create table t_34455 (
a int not null,
foreign key (a) references t3 (a) on delete set default match full);
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'match full)' at line 3
create table t_34455 (
a int not null,
foreign key (a) references t3 (a) on update set default match full);
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'match full)' at line 3
create table t_34455 (
a int not null,
foreign key (a) references t3 (a)
on delete set default on delete set default);
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'delete set default)' at line 4
create table t_34455 (
a int not null,
foreign key (a) references t3 (a)
on update set default on update set default);
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'update set default)' at line 4
create table t_34455 (a int not null);
alter table t_34455
add foreign key (a) references t3 (a) match full match partial);
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'match partial)' at line 2
alter table t_34455
add foreign key (a) references t3 (a) on delete set default match full);
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'match full)' at line 2
alter table t_34455
add foreign key (a) references t3 (a) on update set default match full);
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'match full)' at line 2
alter table t_34455
add foreign key (a) references t3 (a)
on delete set default on delete set default);
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'delete set default)' at line 3
alter table t_34455
add foreign key (a) references t3 (a)
on update set default on update set default);
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'update set default)' at line 3
drop table t_34455;
#
# WL#6929: Move FOREIGN KEY constraints to the global data dictionary
#
# Extra coverage of @@foreign_key_checks
#
SET @@foreign_key_checks= 0;
CREATE TABLE t1(a INT PRIMARY KEY, b INT, FOREIGN KEY (b) REFERENCES non(a));
ALTER TABLE t1 ADD FOREIGN KEY (b) REFERENCES non(a);
DROP TABLE t1;
CREATE TABLE t1(a INT PRIMARY KEY);
CREATE TABLE t2(a INT PRIMARY KEY, b INT, FOREIGN KEY (b) REFERENCES t1(a));
DROP TABLE t1;
DROP TABLE t2;
SET @@foreign_key_checks= 1;
CREATE TABLE t1(a INT PRIMARY KEY, b INT, FOREIGN KEY (b) REFERENCES non(a));
ERROR HY000: Failed to open the referenced table 'non'
CREATE TABLE t1(a INT PRIMARY KEY, b INT REFERENCES non(a));
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) NOT NULL,
  `b` int(11) DEFAULT NULL,
  PRIMARY KEY (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
ALTER TABLE t1 ADD FOREIGN KEY (b) REFERENCES non(a);
ERROR HY000: Failed to open the referenced table 'non'
DROP TABLE t1;
CREATE TABLE t1(a INT PRIMARY KEY);
CREATE TABLE t2(a INT PRIMARY KEY, b INT, FOREIGN KEY (b) REFERENCES t1(a));
DROP TABLE t1;
ERROR HY000: Cannot drop table 't1' referenced by a foreign key constraint 't2_ibfk_1' on table 't2'.
DROP TABLE t2, t1;
SET @@foreign_key_checks= DEFAULT;
# Test coverage of identifier length related to foreign keys.
#
CREATE TABLE t1(a INT PRIMARY KEY);
CREATE TABLE t2(a INT PRIMARY KEY, b INT);
ALTER TABLE t2 ADD CONSTRAINT
name567890123456789012345678901234567890123456789012345678901234
FOREIGN KEY
name567890123456789012345678901234567890123456789012345678901234
(b) REFERENCES t1(a);
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `a` int(11) NOT NULL,
  `b` int(11) DEFAULT NULL,
  PRIMARY KEY (`a`),
  KEY `name567890123456789012345678901234567890123456789012345678901234` (`b`),
  CONSTRAINT `name567890123456789012345678901234567890123456789012345678901234` FOREIGN KEY (`b`) REFERENCES `t1` (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
SELECT CONSTRAINT_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE REFERENCED_TABLE_NAME = 't1';
CONSTRAINT_NAME
name567890123456789012345678901234567890123456789012345678901234
DROP TABLE t2;
CREATE TABLE t2(a INT PRIMARY KEY, b INT);
ALTER TABLE t2 ADD FOREIGN KEY
name5678901234567890123456789012345678901234567890123456789012345
(b) REFERENCES t1(a);
ERROR 42000: Identifier name 'name5678901234567890123456789012345678901234567890123456789012345' is too long
ALTER TABLE t2 ADD CONSTRAINT
name5678901234567890123456789012345678901234567890123456789012345
FOREIGN KEY (b) REFERENCES t1(a);
ERROR 42000: Identifier name 'name5678901234567890123456789012345678901234567890123456789012345' is too long
DROP TABLE t2;
CREATE TABLE t2(a INT PRIMARY KEY, b INT UNIQUE);
ALTER TABLE t2 ADD FOREIGN KEY
name5678901234567890123456789012345678901234567890123456789012345
(b) REFERENCES t1(a);
ERROR 42000: Identifier name 'name5678901234567890123456789012345678901234567890123456789012345' is too long
ALTER TABLE t2 ADD CONSTRAINT
name5678901234567890123456789012345678901234567890123456789012345
FOREIGN KEY (b) REFERENCES t1(a);
ERROR 42000: Identifier name 'name5678901234567890123456789012345678901234567890123456789012345' is too long
DROP TABLE t2;
DROP TABLE t1;
CREATE TABLE t1(a INT PRIMARY KEY, b INT,
FOREIGN KEY(b) REFERENCES name5678901234567890123456789012345678901234567890123456789012345.t2(a));
ERROR 42000: Identifier name 'name5678901234567890123456789012345678901234567890123456789012345' is too long
CREATE TABLE t1(a INT PRIMARY KEY, b INT,
FOREIGN KEY(b) REFERENCES name5678901234567890123456789012345678901234567890123456789012345(a));
ERROR 42000: Incorrect table name 'name5678901234567890123456789012345678901234567890123456789012345'
CREATE TABLE t1(a INT PRIMARY KEY, b INT,
FOREIGN KEY(b) REFERENCES t2(name5678901234567890123456789012345678901234567890123456789012345));
ERROR 42000: Incorrect column name 'name5678901234567890123456789012345678901234567890123456789012345'
SET @@foreign_key_checks= 0;
CREATE TABLE t1(a INT PRIMARY KEY, b INT,
FOREIGN KEY(b) REFERENCES name5678901234567890123456789012345678901234567890123456789012345.t2(a));
ERROR 42000: Identifier name 'name5678901234567890123456789012345678901234567890123456789012345' is too long
CREATE TABLE t1(a INT PRIMARY KEY, b INT,
FOREIGN KEY(b) REFERENCES name5678901234567890123456789012345678901234567890123456789012345(a));
ERROR 42000: Incorrect table name 'name5678901234567890123456789012345678901234567890123456789012345'
CREATE TABLE t1(a INT PRIMARY KEY, b INT,
FOREIGN KEY(b) REFERENCES t2(name5678901234567890123456789012345678901234567890123456789012345));
ERROR 42000: Incorrect column name 'name5678901234567890123456789012345678901234567890123456789012345'
SET @@foreign_key_checks= DEFAULT;
#
# Bug#24666169: I_S.TABLE_CONSTRAINTS.CONSTRAINT_NAME IS NOT UPDATED
#               AFTER RENAME TABLE
#
SET @@foreign_key_checks= 1;
#
# Tests for FK name behavior.
CREATE TABLE t1(c1 INT PRIMARY KEY);
CREATE TABLE t2(c1 INT, FOREIGN KEY (c1) REFERENCES t1(c1));
ALTER TABLE t2 RENAME TO t3;
SHOW CREATE TABLE t3;
Table	Create Table
t3	CREATE TABLE `t3` (
  `c1` int(11) DEFAULT NULL,
  KEY `c1` (`c1`),
  CONSTRAINT `t3_ibfk_1` FOREIGN KEY (`c1`) REFERENCES `t1` (`c1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
INSERT INTO t3 VALUES(1);
ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t3`, CONSTRAINT `t3_ibfk_1` FOREIGN KEY (`c1`) REFERENCES `t1` (`c1`))
ALTER TABLE t3 RENAME TO t4, ALGORITHM= INPLACE;
SHOW CREATE TABLE t4;
Table	Create Table
t4	CREATE TABLE `t4` (
  `c1` int(11) DEFAULT NULL,
  KEY `c1` (`c1`),
  CONSTRAINT `t4_ibfk_1` FOREIGN KEY (`c1`) REFERENCES `t1` (`c1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
INSERT INTO t4 VALUES(1);
ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t4`, CONSTRAINT `t4_ibfk_1` FOREIGN KEY (`c1`) REFERENCES `t1` (`c1`))
ALTER TABLE t4 RENAME TO t5;
SHOW CREATE TABLE t5;
Table	Create Table
t5	CREATE TABLE `t5` (
  `c1` int(11) DEFAULT NULL,
  KEY `c1` (`c1`),
  CONSTRAINT `t5_ibfk_1` FOREIGN KEY (`c1`) REFERENCES `t1` (`c1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
INSERT INTO t5 VALUES(1);
ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t5`, CONSTRAINT `t5_ibfk_1` FOREIGN KEY (`c1`) REFERENCES `t1` (`c1`))
RENAME TABLE t5 to t6;
SHOW CREATE TABLE t6;
Table	Create Table
t6	CREATE TABLE `t6` (
  `c1` int(11) DEFAULT NULL,
  KEY `c1` (`c1`),
  CONSTRAINT `t6_ibfk_1` FOREIGN KEY (`c1`) REFERENCES `t1` (`c1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
INSERT INTO t6 VALUES(1);
ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t6`, CONSTRAINT `t6_ibfk_1` FOREIGN KEY (`c1`) REFERENCES `t1` (`c1`))
DROP TABLE t6, t1;
#
# Tests of FK name generation
CREATE TABLE t1(a INT PRIMARY KEY);
CREATE TABLE t2(a INT, b INT, FOREIGN KEY(a) REFERENCES t1(a));
SELECT constraint_name FROM information_schema.referential_constraints
WHERE table_name = 't2' ORDER BY constraint_name;
CONSTRAINT_NAME
t2_ibfk_1
SELECT constraint_name FROM information_schema.table_constraints
WHERE table_name = 't2' ORDER BY constraint_name;
constraint_name
t2_ibfk_1
# Add FK
ALTER TABLE t2 ADD FOREIGN KEY(b) REFERENCES t1(a);
SELECT constraint_name FROM information_schema.referential_constraints
WHERE table_name = 't2' ORDER BY constraint_name;
CONSTRAINT_NAME
t2_ibfk_1
t2_ibfk_2
SELECT constraint_name FROM information_schema.table_constraints
WHERE table_name = 't2' ORDER BY constraint_name;
constraint_name
t2_ibfk_1
t2_ibfk_2
# Remove first FK and add a new FK.
ALTER TABLE t2 DROP FOREIGN KEY t2_ibfk_1;
ALTER TABLE t2 ADD FOREIGN KEY(a) REFERENCES t1(a);
SELECT constraint_name FROM information_schema.referential_constraints
WHERE table_name = 't2' ORDER BY constraint_name;
CONSTRAINT_NAME
t2_ibfk_2
t2_ibfk_3
SELECT constraint_name FROM information_schema.table_constraints
WHERE table_name = 't2' ORDER BY constraint_name;
constraint_name
t2_ibfk_2
t2_ibfk_3
# Rename table in different ways.
ALTER TABLE t2 RENAME TO t3;
SELECT constraint_name FROM information_schema.referential_constraints
WHERE table_name = 't3' ORDER BY constraint_name;
CONSTRAINT_NAME
t3_ibfk_2
t3_ibfk_3
SELECT constraint_name FROM information_schema.table_constraints
WHERE table_name = 't3' ORDER BY constraint_name;
constraint_name
t3_ibfk_2
t3_ibfk_3
ALTER TABLE t3 RENAME TO t4, ALGORITHM= INPLACE;
SELECT constraint_name FROM information_schema.referential_constraints
WHERE table_name = 't4' ORDER BY constraint_name;
CONSTRAINT_NAME
t4_ibfk_2
t4_ibfk_3
SELECT constraint_name FROM information_schema.table_constraints
WHERE table_name = 't4' ORDER BY constraint_name;
constraint_name
t4_ibfk_2
t4_ibfk_3
ALTER TABLE t4 RENAME TO t5;
SELECT constraint_name FROM information_schema.referential_constraints
WHERE table_name = 't5' ORDER BY constraint_name;
CONSTRAINT_NAME
t5_ibfk_2
t5_ibfk_3
SELECT constraint_name FROM information_schema.table_constraints
WHERE table_name = 't5' ORDER BY constraint_name;
constraint_name
t5_ibfk_2
t5_ibfk_3
RENAME TABLE t5 TO t6;
SELECT constraint_name FROM information_schema.referential_constraints
WHERE table_name = 't6' ORDER BY constraint_name;
CONSTRAINT_NAME
t6_ibfk_2
t6_ibfk_3
SELECT constraint_name FROM information_schema.table_constraints
WHERE table_name = 't6' ORDER BY constraint_name;
constraint_name
t6_ibfk_2
t6_ibfk_3
# Simulate dump+restore and test rename
DROP TABLE t6;
CREATE TABLE `t6` (
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL,
KEY `b` (`b`),
KEY `a` (`a`),
CONSTRAINT `t6_ibfk_2` FOREIGN KEY (`b`) REFERENCES `t1` (`a`),
CONSTRAINT `t6_ibfk_3` FOREIGN KEY (`a`) REFERENCES `t1` (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Warnings:
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	1681	Integer display width is deprecated and will be removed in a future release.
SELECT constraint_name FROM information_schema.referential_constraints
WHERE table_name = 't6' ORDER BY constraint_name;
CONSTRAINT_NAME
t6_ibfk_2
t6_ibfk_3
SELECT constraint_name FROM information_schema.table_constraints
WHERE table_name = 't6' ORDER BY constraint_name;
constraint_name
t6_ibfk_2
t6_ibfk_3
RENAME TABLE t6 TO t2;
SELECT constraint_name FROM information_schema.referential_constraints
WHERE table_name = 't2' ORDER BY constraint_name;
CONSTRAINT_NAME
t2_ibfk_2
t2_ibfk_3
SELECT constraint_name FROM information_schema.table_constraints
WHERE table_name = 't2' ORDER BY constraint_name;
constraint_name
t2_ibfk_2
t2_ibfk_3
# Remove all FKs and add one back
ALTER TABLE t2 DROP FOREIGN KEY t2_ibfk_2, DROP FOREIGN KEY t2_ibfk_3;
ALTER TABLE t2 ADD FOREIGN KEY(a) REFERENCES t1(a);
SELECT constraint_name FROM information_schema.referential_constraints
WHERE table_name = 't2' ORDER BY constraint_name;
CONSTRAINT_NAME
t2_ibfk_1
SELECT constraint_name FROM information_schema.table_constraints
WHERE table_name = 't2' ORDER BY constraint_name;
constraint_name
t2_ibfk_1
# Add a foreign key with close to generated name
ALTER TABLE t2 ADD CONSTRAINT t3_ibfk_2 FOREIGN KEY(b) REFERENCES t1(a);
SELECT constraint_name FROM information_schema.referential_constraints
WHERE table_name = 't2' ORDER BY constraint_name;
CONSTRAINT_NAME
t2_ibfk_1
t3_ibfk_2
SELECT constraint_name FROM information_schema.table_constraints
WHERE table_name = 't2' ORDER BY constraint_name;
constraint_name
t2_ibfk_1
t3_ibfk_2
# Then rename so that the given name now matches a generated name
RENAME TABLE t2 TO t3;
SELECT constraint_name FROM information_schema.referential_constraints
WHERE table_name = 't3' ORDER BY constraint_name;
CONSTRAINT_NAME
t3_ibfk_1
t3_ibfk_2
SELECT constraint_name FROM information_schema.table_constraints
WHERE table_name = 't3' ORDER BY constraint_name;
constraint_name
t3_ibfk_1
t3_ibfk_2
# Finally rename it again. The given name is now seen as generated and renamed.
RENAME TABLE t3 TO t4;
SELECT constraint_name FROM information_schema.referential_constraints
WHERE table_name = 't4' ORDER BY constraint_name;
CONSTRAINT_NAME
t4_ibfk_1
t4_ibfk_2
SELECT constraint_name FROM information_schema.table_constraints
WHERE table_name = 't4' ORDER BY constraint_name;
constraint_name
t4_ibfk_1
t4_ibfk_2
DROP TABLE t4;
# Make a foreign key with given name matching a generated name
CREATE TABLE t2(a INT, b INT);
ALTER TABLE t2 ADD CONSTRAINT t2_ibfk_1 FOREIGN KEY(a) REFERENCES t1(a);
ALTER TABLE t2 ADD FOREIGN KEY(b) REFERENCES t1(a);
SELECT constraint_name FROM information_schema.referential_constraints
WHERE table_name = 't2' ORDER BY constraint_name;
CONSTRAINT_NAME
t2_ibfk_1
t2_ibfk_2
SELECT constraint_name FROM information_schema.table_constraints
WHERE table_name = 't2' ORDER BY constraint_name;
constraint_name
t2_ibfk_1
t2_ibfk_2
DROP TABLE t2;
# Test FK name case sensitivity
CREATE TABLE t2(a INT, b INT);
ALTER TABLE t2 ADD CONSTRAINT FK FOREIGN KEY(a) REFERENCES t1(a);
SELECT constraint_name FROM information_schema.referential_constraints
WHERE table_name = 't2' ORDER BY constraint_name;
CONSTRAINT_NAME
FK
SELECT constraint_name FROM information_schema.table_constraints
WHERE table_name = 't2' ORDER BY constraint_name;
constraint_name
FK
ALTER TABLE t2 ADD CONSTRAINT fk FOREIGN KEY(b) REFERENCES t1(a);
ERROR 42000: Duplicate key name 'fk'
ALTER TABLE t2 DROP FOREIGN KEY FK;
# Name matching generated name, but different case.
ALTER TABLE t2 ADD CONSTRAINT T2_IBFK_1 FOREIGN KEY(a) REFERENCES t1(a);
ALTER TABLE t2 ADD FOREIGN KEY(b) REFERENCES t1(a);
ERROR HY000: Duplicate foreign key constraint name 't2_ibfk_1'
ALTER TABLE t2 DROP FOREIGN KEY T2_IBFK_1;
DROP TABLE t2;
# Check long FK generated names due to long table names.
CREATE TABLE t2 (a INT, FOREIGN KEY (a) REFERENCES t1(a));
RENAME TABLE t2 TO t123456789012345678901234567890123456789012345678901234567;
ERROR 42000: Identifier name 't123456789012345678901234567890123456789012345678901234567_ibfk_1' is too long
RENAME TABLE t2 TO t12345678901234567890123456789012345678901234567890123456;
SELECT constraint_name FROM information_schema.referential_constraints
WHERE table_name = 't12345678901234567890123456789012345678901234567890123456'
  ORDER BY constraint_name;
CONSTRAINT_NAME
t12345678901234567890123456789012345678901234567890123456_ibfk_1
SELECT constraint_name FROM information_schema.table_constraints
WHERE table_name = 't12345678901234567890123456789012345678901234567890123456'
  ORDER BY constraint_name;
constraint_name
t12345678901234567890123456789012345678901234567890123456_ibfk_1
DROP TABLE t12345678901234567890123456789012345678901234567890123456;
CREATE TABLE t123456789012345678901234567890123456789012345678901234567(
a INT, FOREIGN KEY (a) REFERENCES t1(a));
ERROR 42000: Identifier name 't123456789012345678901234567890123456789012345678901234567_ibfk_1' is too long
CREATE TABLE t123456789012345678901234567890123456789012345678901234567890123(
a INT, CONSTRAINT fk FOREIGN KEY (a) REFERENCES t1(a));
DROP TABLE t123456789012345678901234567890123456789012345678901234567890123;
DROP TABLE t1;
# FK Referencing virtual column
CREATE TABLE t1(a INT PRIMARY KEY,
b INT GENERATED ALWAYS AS (a+1) VIRTUAL UNIQUE);
CREATE TABLE t2(a INT, FOREIGN KEY (a) REFERENCES t1(b));
ERROR HY000: Foreign key 't2_ibfk_1' uses virtual column 'b' which is not supported.
CREATE TABLE t2(a INT);
ALTER TABLE t2 ADD FOREIGN KEY(a) REFERENCES t1(b);
ERROR HY000: Foreign key 't2_ibfk_1' uses virtual column 'b' which is not supported.
DROP TABLE t1, t2;
# FK on generated stored column
CREATE TABLE t1(a INT PRIMARY KEY);
CREATE TABLE t2(a INT, b INT GENERATED ALWAYS AS (a+1) STORED UNIQUE);
CREATE TABLE t3(a INT, b INT GENERATED ALWAYS AS (a+1) STORED UNIQUE,
FOREIGN KEY (b) REFERENCES t1(a));
ALTER TABLE t2 ADD FOREIGN KEY(b) REFERENCES t1(a);
ALTER TABLE t2 DROP FOREIGN KEY t2_ibfk_1;
DROP TABLE t3;
CREATE TABLE t3(a INT, b INT GENERATED ALWAYS AS (a+1) STORED UNIQUE,
FOREIGN KEY (b) REFERENCES t1(a) ON UPDATE CASCADE);
ERROR HY000: Cannot define foreign key with ON UPDATE CASCADE clause on a generated column.
ALTER TABLE t2 ADD FOREIGN KEY(b) REFERENCES t1(a) ON UPDATE CASCADE;
ERROR HY000: Cannot define foreign key with ON UPDATE CASCADE clause on a generated column.
CREATE TABLE t3(a INT, b INT GENERATED ALWAYS AS (a+1) STORED UNIQUE,
FOREIGN KEY (b) REFERENCES t1(a) ON DELETE SET NULL);
ERROR HY000: Cannot define foreign key with ON DELETE SET NULL clause on a generated column.
ALTER TABLE t2 ADD FOREIGN KEY(b) REFERENCES t1(a) ON DELETE SET NULL;
ERROR HY000: Cannot define foreign key with ON DELETE SET NULL clause on a generated column.
CREATE TABLE t3(a INT, b INT GENERATED ALWAYS AS (a+1) STORED UNIQUE,
FOREIGN KEY (b) REFERENCES t1(a) ON UPDATE SET NULL);
ERROR HY000: Cannot define foreign key with ON UPDATE SET NULL clause on a generated column.
ALTER TABLE t2 ADD FOREIGN KEY(b) REFERENCES t1(a) ON UPDATE SET NULL;
ERROR HY000: Cannot define foreign key with ON UPDATE SET NULL clause on a generated column.
# FK on Base column of generated stored column.
CREATE TABLE t3(a INT, b INT GENERATED ALWAYS AS (a+1) STORED UNIQUE,
FOREIGN KEY (a) REFERENCES t1(a));
ALTER TABLE t2 ADD FOREIGN KEY(a) REFERENCES t1(a);
ALTER TABLE t2 DROP FOREIGN KEY t2_ibfk_1;
DROP TABLE t3;
CREATE TABLE t3(a INT, b INT GENERATED ALWAYS AS (a+1) STORED UNIQUE,
FOREIGN KEY (a) REFERENCES t1(a) ON UPDATE CASCADE);
ERROR HY000: Cannot add foreign key constraint
ALTER TABLE t2 ADD FOREIGN KEY(a) REFERENCES t1(a) ON UPDATE CASCADE;
ERROR HY000: Cannot add foreign key constraint
CREATE TABLE t3(a INT, b INT GENERATED ALWAYS AS (a+1) STORED UNIQUE,
FOREIGN KEY (a) REFERENCES t1(a) ON DELETE SET NULL);
ERROR HY000: Cannot add foreign key constraint
ALTER TABLE t2 ADD FOREIGN KEY(a) REFERENCES t1(a) ON DELETE SET NULL;
ERROR HY000: Cannot add foreign key constraint
CREATE TABLE t3(a INT, b INT GENERATED ALWAYS AS (a+1) STORED UNIQUE,
FOREIGN KEY (a) REFERENCES t1(a) ON UPDATE SET NULL);
ERROR HY000: Cannot add foreign key constraint
ALTER TABLE t2 ADD FOREIGN KEY(a) REFERENCES t1(a) ON UPDATE SET NULL;
ERROR HY000: Cannot add foreign key constraint
DROP TABLE t2, t1;
# FK on virtual column not supported.
CREATE TABLE t1(a INT PRIMARY KEY);
CREATE TABLE t2(a INT, b INT GENERATED ALWAYS AS (a+1) VIRTUAL UNIQUE,
FOREIGN KEY(b) REFERENCES t1(a));
ERROR HY000: Foreign key 't2_ibfk_1' uses virtual column 'b' which is not supported.
CREATE TABLE t2(a INT, b INT GENERATED ALWAYS AS (a+1) VIRTUAL UNIQUE);
ALTER TABLE t2 ADD FOREIGN KEY (b) REFERENCES t1(a);
ERROR HY000: Foreign key 't2_ibfk_1' uses virtual column 'b' which is not supported.
DROP TABLE t2;
CREATE TABLE t2(a INT, b INT, FOREIGN KEY(b) REFERENCES t1(a));
ALTER TABLE t2 MODIFY COLUMN b INT GENERATED ALWAYS AS (a+1) VIRTUAL;
ERROR HY000: 'Changing the STORED status' is not supported for generated columns.
DROP TABLE t2, t1;
# Trying to drop columns used in multi-column FKs.
CREATE TABLE t1(a INT PRIMARY KEY, b INT, INDEX(a, b));
CREATE TABLE t2(a INT, b INT, FOREIGN KEY(a, b) REFERENCES t1(a, b));
ALTER TABLE t2 DROP COLUMN a;
ERROR HY000: Cannot drop column 'a': needed in a foreign key constraint 't2_ibfk_1'
ALTER TABLE t2 DROP COLUMN b;
ERROR HY000: Cannot drop column 'b': needed in a foreign key constraint 't2_ibfk_1'
DROP TABLE t2;
# Use explicitly named index to check where index name is != column name.
CREATE TABLE t2(a INT, b INT, INDEX idx(a, b),
FOREIGN KEY(a, b) REFERENCES t1(a, b));
ALTER TABLE t2 DROP COLUMN a;
ERROR HY000: Cannot drop column 'a': needed in a foreign key constraint 't2_ibfk_1'
ALTER TABLE t2 DROP COLUMN b;
ERROR HY000: Cannot drop column 'b': needed in a foreign key constraint 't2_ibfk_1'
DROP TABLE t2, t1;
# Index with prefix cannot be used for supporting FK.
CREATE TABLE t1 (PK VARCHAR(100) PRIMARY KEY);
CREATE TABLE t2 (FK VARCHAR(100), FOREIGN KEY(FK) REFERENCES t1 (PK), KEY(FK));
ALTER TABLE t2 DROP INDEX FK, ADD INDEX FK2(FK(10));
ERROR HY000: Cannot drop index 'FK': needed in a foreign key constraint
DROP TABLE t2, t1;
# Bug#25817660: Combination of virtual index, foreign key and trigger
#               result in assert failure
CREATE TABLE t1(fld1 INT NOT NULL PRIMARY KEY);
CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a:=0;
CREATE TABLE t2(fld1 INT NOT NULL, fld2 INT AS (fld1) VIRTUAL, KEY(fld2),
FOREIGN KEY(fld1) REFERENCES t1(fld1) ON UPDATE CASCADE);
INSERT INTO t1 VALUES(1);
INSERT INTO t2 VALUES(1, DEFAULT);
UPDATE t1 SET fld1= 2;
SELECT * FROM t1;
fld1
2
SELECT * FROM t2;
fld1	fld2
2	2
# The same test but with server restart before UPDATE.
# Triggers different code path in InnoDB which was not
# covered by original fix for the bug.
# restart
UPDATE t1 SET fld1= 3;
SELECT * FROM t1;
fld1
3
SELECT * FROM t2;
fld1	fld2
3	3
DROP TABLE t2;
DROP TABLE t1;
#
# Bug#20021917: WORK AROUND FOR CHARSET CONVERSION WITH FKS CAN
# RESULT IN WRONG DATA
#
CREATE TABLE t1 (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100) UNIQUE);
CREATE TABLE t2 (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100), fname VARCHAR(100), FOREIGN KEY (fname) REFERENCES t1 (name) ON UPDATE CASCADE ON DELETE CASCADE);
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT NULL,
  `fname` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fname` (`fname`),
  CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`fname`) REFERENCES `t1` (`name`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
ALTER TABLE t2 CONVERT TO CHARACTER SET latin1;
ERROR HY000: Referencing column 'fname' and referenced column 'name' in foreign key constraint 't2_ibfk_1' are incompatible.
SET foreign_key_checks= OFF;
ALTER TABLE t2 CONVERT TO CHARACTER SET latin1;
SET foreign_key_checks= ON;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT NULL,
  `fname` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fname` (`fname`),
  CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`fname`) REFERENCES `t1` (`name`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1
INSERT INTO t1(name) VALUES ('test1');
INSERT INTO t2(name, fname) VALUES ('test1', 'test1');
UPDATE t1 SET name=CONCAT('St', UNHEX('C3A5') ,'le') WHERE name = 'test1';
# Should not get any rows here
SELECT t1.name, t2.fname FROM t1, t2 WHERE t1.name <> t2.fname;
name	fname
Ståle	StÃ¥le
DROP TABLE t2;
DROP TABLE t1;
#
# WL#6049: Meta-data locking for FOREIGN KEY tables
#
# Test case to check MDL on tables related by FK constraints.
#
CREATE TABLE grandparent (gpf1 INT PRIMARY KEY, gpf2 INT);
INSERT INTO grandparent VALUES (1,10), (2,20);
CREATE TABLE parent (
pf1 INT PRIMARY KEY, pf2 INT, sleep_dummy INT,
CONSTRAINT pc1 FOREIGN KEY (pf2) REFERENCES grandparent (gpf1)
ON DELETE NO ACTION ON UPDATE NO ACTION);
INSERT INTO parent VALUES (1,1,0), (2,2,0);
CREATE TABLE child (
cf1 INT PRIMARY KEY, cf2 INT,
CONSTRAINT cc1 FOREIGN KEY (cf2) REFERENCES parent (pf1)
ON DELETE NO ACTION ON UPDATE NO ACTION);
INSERT INTO child VALUES (1,1), (2,2);
connect  con_A,localhost,root,,test;
SET @@session.lock_wait_timeout= 1;
UPDATE parent SET pf2= 2, sleep_dummy= SLEEP(2);
connection default;
set @conA_id = <conA>;
# Waiting for connection A to start udpate
SET @@session.lock_wait_timeout= 1;
# Updates not invoving the FK related column should succeed
UPDATE grandparent SET gpf2= 4;
UPDATE grandparent SET gpf2= 100 * gpf1;
# DDL on child will have conflicting locks.
ALTER TABLE child ADD COLUMN (i INT);
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
SET @@session.lock_wait_timeout= DEFAULT;
connection con_A;
disconnect con_A;
connection default;
DROP TABLE child;
DROP TABLE parent;
DROP TABLE grandparent;
#
# Systemic test coverage for metadata locks related to foreign
# keys acquired by various DDL statements.
#
# Also provides coverage for data-dictionary cache invalidation
# and cases when we fail to acquire necessary locks.
SET @old_lock_wait_timeout= @@lock_wait_timeout;
connect  con1, localhost, root,,;
connect  con2, localhost, root,,;
SET @old_lock_wait_timeout= @@lock_wait_timeout;
connection default;
#
# 1) CREATE TABLE
#
# 1.1) CREATE TABLE must acquire X lock on parent table
#      (if table doesn't exist).
CREATE TABLE parent (pk INT PRIMARY KEY);
connection con1;
BEGIN;
SELECT * FROM parent;
pk
connection default;
CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES parent(pk));;
connection con1;
# Wait until CREATE TABLE is blocked by con1 and then resume it.
COMMIT;
connection default;
# Reap CREATE TABLE
# 1.2) CREATE TABLE for existing table should not acquire X lock
#      parent table
connection con1;
BEGIN;
SELECT * FROM parent;
pk
connection default;
CREATE TABLE IF NOT EXISTS child (fk INT, FOREIGN KEY (fk) REFERENCES parent(pk));
Warnings:
Note	1050	Table 'child' already exists
connection con1;
COMMIT;
connection default;
DROP TABLE child;
# 1.3) CREATE TABLE which acquires lock on parent table and fails
#      due to lock timeout.
connection con1;
BEGIN;
SELECT * FROM parent;
pk
connection default;
SET @@lock_wait_timeout= 1;
CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES parent(pk));
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
SET @@lock_wait_timeout= @old_lock_wait_timeout;
connection con1;
COMMIT;
connection default;
DROP TABLE parent;
# 1.4) CREATE TABLE which adds parent to orphan child must acquire X
#      on child table.
SET FOREIGN_KEY_CHECKS=0;
CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES parent(pk));
SET FOREIGN_KEY_CHECKS=1;
connection con1;
BEGIN;
SELECT * FROM child;
fk
connection default;
CREATE TABLE parent (pk INT PRIMARY KEY);;
connection con1;
# Wait until CREATE TABLE is blocked by con1 and then resume it.
COMMIT;
connection default;
# Reap CREATE TABLE
# 1.5) CREATE TABLE for existing table must not acquire X lock
#      on child table
connection con1;
BEGIN;
SELECT * FROM child;
fk
connection default;
CREATE TABLE IF NOT EXISTS parent (pk INT PRIMARY KEY);
Warnings:
Note	1050	Table 'parent' already exists
connection con1;
COMMIT;
connection default;
SET FOREIGN_KEY_CHECKS=0;
DROP TABLE parent;
SET FOREIGN_KEY_CHECKS=1;
# 1.6) CREATE TABLE which acquires lock on child table and fails
#      due to lock timeout.
connection con1;
BEGIN;
SELECT * FROM child;
fk
connection default;
SET @@lock_wait_timeout= 1;
CREATE TABLE parent (pk INT PRIMARY KEY);
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
SET @@lock_wait_timeout= @old_lock_wait_timeout;
connection con1;
COMMIT;
connection default;
DROP TABLE child;
# 1.7) CREATE TABLE which adds FK should invalidate entries
#      in TC/TDC and DD caches for the parent table.
CREATE TABLE parent (pk INT PRIMARY KEY);
SELECT * FROM parent;
pk
connection con1;
CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES parent(pk) ON DELETE CASCADE);
connection default;
LOCK TABLE parent WRITE;
connection con1;
# The above LOCK TABLE should have noticed new table definition
# and acquired SNRW lock on child table.
SELECT * FROM child;;
connection default;
# Wait until SELECT is blocked by default connection and then resume it.
UNLOCK TABLES;
connection con1;
# Reap SELECT
fk
connection default;
DROP TABLES child, parent;
#
# 2) CREATE TABLE ... LIKE
#
# 2.1) CREATE TABLE ... LIKE doesn't copy foreign keys
#      so it should not any locks on FK parent tables.
CREATE TABLE parent (pk INT PRIMARY KEY);
CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES parent(pk));
connection con1;
LOCK TABLES parent WRITE;
connection default;
CREATE TABLE child2 LIKE child;
connection con1;
UNLOCK TABLES;
connection default;
# 2.2) CREATE TABLE LIKE which adds parent to orphan child must
#      acquire X on child table.
SET FOREIGN_KEY_CHECKS=0;
DROP TABLES child2, parent;
SET FOREIGN_KEY_CHECKS=1;
CREATE TABLE parent_source (pk INT PRIMARY KEY);
connection con1;
BEGIN;
SELECT * FROM child;
fk
connection default;
CREATE TABLE parent LIKE parent_source;;
connection con1;
# Wait until CREATE TABLE is blocked by con1 and then resume it.
COMMIT;
connection default;
# Reap CREATE TABLE
# 2.3) CREATE TABLE LIKE for existing table must not acquire X lock
#      on child table
connection con1;
BEGIN;
SELECT * FROM child;
fk
connection default;
CREATE TABLE IF NOT EXISTS parent LIKE parent_source;
Warnings:
Note	1050	Table 'parent' already exists
connection con1;
COMMIT;
connection default;
SET FOREIGN_KEY_CHECKS=0;
DROP TABLE parent;
SET FOREIGN_KEY_CHECKS=1;
# 2.4) CREATE TABLE LIKE which acquires lock on child table and fails
#      due to lock timeout.
connection con1;
BEGIN;
SELECT * FROM child;
fk
connection default;
SET @@lock_wait_timeout= 1;
CREATE TABLE IF NOT EXISTS parent LIKE parent_source;
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
SET @@lock_wait_timeout= @old_lock_wait_timeout;
connection con1;
COMMIT;
connection default;
DROP TABLE child, parent_source;
#
# 3) CREATE TABLE ... SELECT
#
# 3.1) CREATE TABLE ... SELECT must start by acquiring SU lock on parent
#      table (if table doesn't exist).
CREATE TABLE parent (pk INT PRIMARY KEY);
CREATE TABLE source (fk INT);
INSERT INTO source VALUES (NULL);
connection con1;
BEGIN;
SELECT * FROM source FOR UPDATE;
fk
NULL
connection default;
CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES parent(pk)) SELECT fk FROM source;;
connection con1;
# Wait until CREATE TABLE is blocked by con1 because of row locks.
# Demonstrate that CREATE TABLE holds SU lock on parent
# which allows DML but not DDL.
INSERT INTO parent VALUES (1);
connection con2;
SET @@lock_wait_timeout= 1;
ALTER TABLE parent ADD COLUMN a INT;
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
SET @@lock_wait_timeout= @old_lock_wait_timeout;
connection con1;
# Resume CREATE TABLE.
COMMIT;
connection default;
# Reap CREATE TABLE
DROP TABLES child, source;
# 3.2) CREATE TABLE ... SELECT should upgrade SU lock on parent to X
#      before commit (i.e eventually X lock should be acquired).
connection con1;
BEGIN;
SELECT * FROM parent;
pk
1
connection default;
CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES parent(pk)) SELECT NULL AS fk;;
connection con1;
# Wait until CREATE TABLE is blocked by con1 and then resume it.
COMMIT;
connection default;
# Reap CREATE TABLE
# 3.3) CREATE TABLE ... SELECT for existing table should not acquire
#      any locks on parent table.
connection con1;
CREATE TABLE parent2 (pk INT PRIMARY KEY);
LOCK TABLE parent2 WRITE;
connection default;
CREATE TABLE IF NOT EXISTS child (fk INT, FOREIGN KEY (fk) REFERENCES parent2(pk)) SELECT NULL AS fk;
Warnings:
Note	1050	Table 'child' already exists
connection con1;
UNLOCK TABLES;
connection default;
DROP TABLE child;
DROP TABLE parent2;
# 3.4) CREATE TABLE ... SELECT which tries to acquire SU lock on parent
#      table and fails due to lock timeout.
connection con1;
LOCK TABLE parent WRITE;
connection default;
SET @@lock_wait_timeout= 1;
CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES parent(pk)) SELECT NULL AS fk;
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
SET @@lock_wait_timeout= @old_lock_wait_timeout;
connection con1;
UNLOCK TABLES;
# 3.5) CREATE TABLE ... SELECT which tries to upgrade to X lock on
#      parent table and fails due to lock timeout.
BEGIN;
SELECT * FROM parent;
pk
1
connection default;
SET @@lock_wait_timeout= 1;
CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES parent(pk)) SELECT NULL AS fk;
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
SET @@lock_wait_timeout= @old_lock_wait_timeout;
connection con1;
COMMIT;
connection default;
DROP TABLE parent;
# 3.6) CREATE TABLE ... SELECT which adds parent to orphan child must
#      acquire X on child table.
SET FOREIGN_KEY_CHECKS=0;
CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES parent(pk));
SET FOREIGN_KEY_CHECKS=1;
connection con1;
BEGIN;
SELECT * FROM child;
fk
connection default;
CREATE TABLE parent (pk INT PRIMARY KEY) SELECT 1 AS pk;;
connection con1;
# Wait until CREATE TABLE is blocked by con1 and then resume it.
COMMIT;
connection default;
# Reap CREATE TABLE
# 3.7) CREATE TABLE ... SELECT for existing table must not
#      acquire X lock on child table
connection con1;
BEGIN;
SELECT * FROM child;
fk
connection default;
CREATE TABLE IF NOT EXISTS parent (pk INT PRIMARY KEY) SELECT 1 AS pk;
Warnings:
Note	1050	Table 'parent' already exists
connection con1;
COMMIT;
connection default;
SET FOREIGN_KEY_CHECKS=0;
DROP TABLE parent;
SET FOREIGN_KEY_CHECKS=1;
# 3.8) CREATE TABLE ... SELECT which acquires lock on child table
#      and fails due to lock timeout.
connection con1;
BEGIN;
SELECT * FROM child;
fk
connection default;
SET @@lock_wait_timeout= 1;
CREATE TABLE parent (pk INT PRIMARY KEY) SELECT 1 AS pk;
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
SET @@lock_wait_timeout= @old_lock_wait_timeout;
connection con1;
COMMIT;
connection default;
DROP TABLE child;
# 3.9) CREATE TABLE ... SELECT which adds FK should invalidate entries
#      in TC/TDC and DD caches for the parent table.
CREATE TABLE parent (pk INT PRIMARY KEY);
SELECT * FROM parent;
pk
connection con1;
CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES parent(pk) ON DELETE CASCADE) SELECT NULL AS fk;
connection default;
LOCK TABLE parent WRITE;
connection con1;
# The above LOCK TABLE should have noticed new table definition
# and acquired SNRW lock on child table.
SELECT * FROM child;;
connection default;
# Wait until SELECT is blocked by default connection and then resume it.
UNLOCK TABLES;
connection con1;
# Reap SELECT
fk
NULL
connection default;
DROP TABLES child, parent;
#
# 4) DROP TABLES
#
# 4.1) DROP TABLES must acquire X lock on parent table for FKs
#      when child is dropped.
CREATE TABLE parent (pk INT PRIMARY KEY);
CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES parent(pk));
connection con1;
BEGIN;
SELECT * FROM parent;
pk
connection default;
DROP TABLES child;;
connection con1;
# Wait until DROP TABLES is blocked by con1 and then resume it.
COMMIT;
connection default;
# Reap DROP TABLES
CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES parent(pk));
# 4.2) DROP TABLES which acquires lock on parent table and fails
#      due to lock timeout.
connection con1;
BEGIN;
SELECT * FROM parent;
pk
connection default;
SET @@lock_wait_timeout= 1;
DROP TABLES child;
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
SET @@lock_wait_timeout= @old_lock_wait_timeout;
connection con1;
COMMIT;
# 4.3) DROP TABLES which tries to remove parent table must acquire X
#      lock on child table.
connection con1;
BEGIN;
SELECT * FROM child;
fk
connection default;
DROP TABLES parent;;
connection con1;
# Wait until DROP TABLES is blocked by con1 and then resume it.
COMMIT;
connection default;
# Reap DROP TABLES
ERROR HY000: Cannot drop table 'parent' referenced by a foreign key constraint 'child_ibfk_1' on table 'child'.
# 4.4) DROP TABLES which acquires lock on child table and fails
#      due to lock timeout.
connection con1;
BEGIN;
SELECT * FROM child;
fk
connection default;
SET @@lock_wait_timeout= 1;
DROP TABLES parent;
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
SET @@lock_wait_timeout= @old_lock_wait_timeout;
connection con1;
COMMIT;
# 4.5) DROP TABLES which deletes child should invalidate entries
#      in TC/TDC and DD caches for the parent table.
DROP TABLES child;
CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES parent(pk) ON DELETE CASCADE);
SELECT * FROM parent;
pk
connection con1;
DROP TABLES child;
connection default;
LOCK TABLE parent WRITE;
connection con1;
# The above LOCK TABLE should have noticed new table definition
# and avoid acquiring SNRW lock on child table.
SELECT * FROM child;
ERROR 42S02: Table 'test.child' doesn't exist
connection default;
UNLOCK TABLES;
DROP TABLES parent;
#
# 5) RENAME TABLES
#
# 5.1) RENAME TABLES must acquire X lock on parent table for FKs
#      when child is renamed.
CREATE TABLE parent (pk INT PRIMARY KEY);
CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES parent(pk));
connection con1;
BEGIN;
SELECT * FROM parent;
pk
connection default;
RENAME TABLES child TO child1;;
connection con1;
# Wait until RENAME TABLES is blocked by con1 and then resume it.
COMMIT;
connection default;
# Reap RENAME TABLES
RENAME TABLES child1 TO child;
# 5.2) RENAME TABLES which acquires lock on parent table and fails
#      due to lock timeout.
connection con1;
BEGIN;
SELECT * FROM parent;
pk
connection default;
SET @@lock_wait_timeout= 1;
RENAME TABLES child TO child1;
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
SET @@lock_wait_timeout= @old_lock_wait_timeout;
connection con1;
COMMIT;
# 5.3) RENAME TABLES which tries to rename parent table must acquire X
#      lock on child table.
connection con1;
BEGIN;
SELECT * FROM child;
fk
connection default;
RENAME TABLES parent TO parent1;;
connection con1;
# Wait until RENAME TABLES is blocked by con1 and then resume it.
COMMIT;
connection default;
# Reap RENAME TABLES
RENAME TABLES parent1 TO parent;
# 5.4) RENAME TABLES which acquires lock on child table and fails
#      due to lock timeout.
connection con1;
BEGIN;
SELECT * FROM child;
fk
connection default;
SET @@lock_wait_timeout= 1;
RENAME TABLES parent TO parent1;
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
SET @@lock_wait_timeout= @old_lock_wait_timeout;
connection con1;
COMMIT;
# 5.5) RENAME TABLES which adds parent table for orphan child
#      must acquire X lock on this child table.
connection default;
DROP TABLE child;
SET FOREIGN_KEY_CHECKS=0;
CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES parent1(pk));
SET FOREIGN_KEY_CHECKS=1;
connection con1;
BEGIN;
SELECT * FROM child;
fk
connection default;
RENAME TABLES parent TO parent1;;
connection con1;
# Wait until RENAME TABLES is blocked by con1 and then resume it.
COMMIT;
connection default;
# Reap RENAME TABLES
RENAME TABLES parent1 TO parent;
# 5.6) RENAME TABLES which acquires lock on orphan child table
#      and fails due to timeout.
DROP TABLE child;
SET FOREIGN_KEY_CHECKS=0;
CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES parent1(pk));
SET FOREIGN_KEY_CHECKS=1;
connection con1;
BEGIN;
SELECT * FROM child;
fk
connection default;
SET @@lock_wait_timeout= 1;
RENAME TABLES parent TO parent1;
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
SET @@lock_wait_timeout= @old_lock_wait_timeout;
connection con1;
COMMIT;
connection default;
DROP TABLE child;
CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES parent(pk) ON DELETE CASCADE);
# 5.7) RENAME TABLES on the child table should invalidate entries
#      in TC/TDC and DD caches for the parent table.
SELECT * FROM parent;
pk
connection con1;
RENAME TABLES child TO child1;
connection default;
LOCK TABLE parent WRITE;
connection con1;
# The above LOCK TABLE should have noticed new table definition
# and acquire SNRW lock on new child table name.
SELECT * FROM child1;;
connection default;
# Wait until SELECT is blocked by default connection and then resume it.
UNLOCK TABLES;
connection con1;
# Reap SELECT
fk
connection default;
DROP TABLES child1, parent;
#
# 6) Simple ALTER TABLE ... RENAME
#
# 6.1) ALTER TABLE ... RENAME must acquire X lock on parent table
#      for FKs when child is renamed.
CREATE TABLE parent (pk INT PRIMARY KEY);
CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES parent(pk));
connection con1;
BEGIN;
SELECT * FROM parent;
pk
connection default;
ALTER TABLE child RENAME TO child1;;
connection con1;
# Wait until ALTER TABLE is blocked by con1 and then resume it.
COMMIT;
connection default;
# Reap ALTER TABLE
RENAME TABLES child1 TO child;
# 6.2) ALTER TABLE ... RENAME which acquires lock on parent table and
#      fails due to lock timeout.
connection con1;
BEGIN;
SELECT * FROM parent;
pk
connection default;
SET @@lock_wait_timeout= 1;
ALTER TABLE child RENAME TO child1;
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
SET @@lock_wait_timeout= @old_lock_wait_timeout;
connection con1;
COMMIT;
# 6.3) ALTER TABLE ... RENAME which tries to rename parent table must
#      acquire X lock on child table.
connection con1;
BEGIN;
SELECT * FROM child;
fk
connection default;
ALTER TABLE parent RENAME TO parent1;;
connection con1;
# Wait until ALTER TABLE is blocked by con1 and then resume it.
COMMIT;
connection default;
# Reap ALTER TABLE
RENAME TABLES parent1 TO parent;
# 6.4) ALTER TABLE ... RENAME which acquires lock on child table and
#      fails due to lock timeout.
connection con1;
BEGIN;
SELECT * FROM child;
fk
connection default;
SET @@lock_wait_timeout= 1;
ALTER TABLE parent RENAME TO parent1;
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
SET @@lock_wait_timeout= @old_lock_wait_timeout;
connection con1;
COMMIT;
# 6.5) ALTER TABLE ... RENAME which adds parent table for orphan child
#      must acquire X lock on this child table.
connection default;
DROP TABLE child;
SET FOREIGN_KEY_CHECKS=0;
CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES parent1(pk));
SET FOREIGN_KEY_CHECKS=1;
connection con1;
BEGIN;
SELECT * FROM child;
fk
connection default;
ALTER TABLE parent RENAME TO parent1;;
connection con1;
# Wait until ALTER TABLE is blocked by con1 and then resume it.
COMMIT;
connection default;
# Reap ALTER TABLE
RENAME TABLES parent1 TO parent;
# 6.6) ALTER TABLE ... RENAME which acquires lock on orphan child table
#      and fails due to timeout.
DROP TABLE child;
SET FOREIGN_KEY_CHECKS=0;
CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES parent1(pk));
SET FOREIGN_KEY_CHECKS=1;
connection con1;
BEGIN;
SELECT * FROM child;
fk
connection default;
SET @@lock_wait_timeout= 1;
ALTER TABLE parent RENAME TO parent1;
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
SET @@lock_wait_timeout= @old_lock_wait_timeout;
connection con1;
COMMIT;
connection default;
DROP TABLE child;
CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES parent(pk) ON DELETE CASCADE);
# 6.7) ALTER TABLE ... RENAME on the child table should invalidate
#      entries in TC/TDC and DD caches for the parent table.
SELECT * FROM parent;
pk
connection con1;
ALTER TABLE child RENAME TO child1;
connection default;
LOCK TABLE parent WRITE;
connection con1;
# The above LOCK TABLE should have noticed new table definition
# and acquire SNRW lock on new child table name.
SELECT * FROM child1;;
connection default;
# Wait until SELECT is blocked by default connection and then resume it.
UNLOCK TABLES;
connection con1;
# Reap SELECT
fk
connection default;
DROP TABLES child1, parent;
#
# 7) ALTER TABLE ... INPLACE
#
# 7.1) ALTER TABLE ... ADD FOREIGN KEY ... INPLACE must start by
#      acquiring SU lock on parent table.
#
# This test uses debug_sync feature so resides in foreign_key_debug.test
#
# 7.2) ALTER TABLE ... ADD FOREIGN KEY ... INPLACE should upgrade SU
#      lock on parent to X before commit (i.e eventually X lock should
#      be acquired).
CREATE TABLE parent (pk INT PRIMARY KEY);
CREATE TABLE child (fk INT);
connection con1;
BEGIN;
SELECT * FROM parent;
pk
connection default;
SET FOREIGN_KEY_CHECKS=0;
ALTER TABLE child ADD CONSTRAINT fk FOREIGN KEY (fk) REFERENCES parent(pk), ALGORITHM=INPLACE;;
connection con1;
# Wait until ALTER TABLE is blocked by con1 and then resume it.
COMMIT;
connection default;
# Reap ALTER TABLE
SET FOREIGN_KEY_CHECKS=1;
ALTER TABLE child DROP FOREIGN KEY fk;
# 7.3) ALTER TABLE ... ADD FOREIGN KEY ... INPLACE which tries to
#      acquire SU lock on parent table and fails due to lock timeout.
connection con1;
LOCK TABLE parent WRITE;
connection default;
SET @@lock_wait_timeout= 1;
ALTER TABLE child ADD CONSTRAINT fk FOREIGN KEY (fk) REFERENCES parent(pk), ALGORITHM=INPLACE;
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
SET @@lock_wait_timeout= @old_lock_wait_timeout;
connection con1;
UNLOCK TABLES;
# 7.4) ALTER TABLE ... ADD FOREIGN KEY ... INPLACE which tries to
#      upgrade to X lock on parent table and fails due to lock timeout.
BEGIN;
SELECT * FROM parent;
pk
connection default;
SET @@lock_wait_timeout= 1;
SET FOREIGN_KEY_CHECKS=0;
ALTER TABLE child ADD CONSTRAINT fk FOREIGN KEY (fk) REFERENCES parent(pk), ALGORITHM=INPLACE;
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
SET FOREIGN_KEY_CHECKS=1;
SET @@lock_wait_timeout= @old_lock_wait_timeout;
connection con1;
COMMIT;
# 7.5) ALTER TABLE ... ADD FOREIGN KEY ... INPLACE should invalidate
#      entries in TC/TDC and DD caches for the parent table.
SELECT * FROM parent;
pk
connection con1;
SET FOREIGN_KEY_CHECKS=0;
ALTER TABLE child ADD CONSTRAINT fk FOREIGN KEY (fk) REFERENCES parent(pk) ON DELETE CASCADE, ALGORITHM=INPLACE;
SET FOREIGN_KEY_CHECKS=1;
connection default;
LOCK TABLE parent WRITE;
connection con1;
# The above LOCK TABLE should have noticed new table definition
# and acquired SNRW lock on child table.
SELECT * FROM child;;
connection default;
# Wait until SELECT is blocked by default connection and then resume it.
UNLOCK TABLES;
connection con1;
# Reap SELECT
fk
# 7.6) ALTER TABLE ... DROP FOREIGN KEY ... INPLACE should acquire
#      lock on parent to X before commit.
connection con1;
BEGIN;
SELECT * FROM parent;
pk
connection default;
ALTER TABLE child DROP FOREIGN KEY fk, ALGORITHM=INPLACE;;
connection con1;
# Wait until ALTER TABLE is blocked by con1 and then resume it.
COMMIT;
connection default;
# Reap ALTER TABLE
ALTER TABLE child ADD CONSTRAINT fk FOREIGN KEY (fk) REFERENCES parent(pk) ON DELETE CASCADE;
# 7.7) ALTER TABLE ... DROP FOREIGN KEY ... INPLACE which tries to
#      upgrade to X lock on parent table and fails due to lock timeout.
connection con1;
BEGIN;
SELECT * FROM parent;
pk
connection default;
SET @@lock_wait_timeout= 1;
ALTER TABLE child DROP FOREIGN KEY fk, ALGORITHM=INPLACE;
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
SET @@lock_wait_timeout= @old_lock_wait_timeout;
connection con1;
COMMIT;
# 7.8) ALTER TABLE ... DROP FOREIGN KEY ... INPLACE should invalidate entries
#      in TC/TDC and DD caches for the parent table.
SELECT * FROM parent;
pk
connection con1;
ALTER TABLE child DROP FOREIGN KEY fk, ALGORITHM=INPLACE;
connection default;
LOCK TABLE parent WRITE;
connection con1;
# The above LOCK TABLE should have noticed new table definition
# and avoid acquiring SNRW lock on child table.
SELECT * FROM child;
fk
connection default;
UNLOCK TABLES;
# 7.9) ALTER TABLE ... INPLACE which changes parent key must acquire X
#      lock on child table.
DROP TABLES child, parent;
CREATE TABLE parent (pk INT NOT NULL, UNIQUE u(pk));
CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES parent(pk));
connection con1;
BEGIN;
SELECT * FROM child;
fk
connection default;
ALTER TABLE parent RENAME KEY u TO u1, ALGORITHM=INPLACE;;
connection con1;
# Wait until ALTER TABLE is blocked by con1 and then resume it.
COMMIT;
connection default;
# Reap ALTER TABLE
# 7.10) ALTER TABLE ... INPLACE which changes parent key, so tries to
#       acquire X lock on child table, but fails due to timeout.
connection con1;
BEGIN;
SELECT * FROM child;
fk
connection default;
SET @@lock_wait_timeout= 1;
ALTER TABLE parent RENAME KEY u1 TO u, ALGORITHM=INPLACE;
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
SET @@lock_wait_timeout= @old_lock_wait_timeout;
connection con1;
COMMIT;
# 7.11) ALTER TABLE ... RENAME ... INPLACE must acquire X lock on
#       parent table for FKs when child is renamed.
connection con1;
BEGIN;
SELECT * FROM parent;
pk
connection default;
ALTER TABLE child RENAME TO child1, ADD COLUMN a INT, ALGORITHM=INPLACE;;
connection con1;
# Wait until ALTER TABLE is blocked by con1 and then resume it.
COMMIT;
connection default;
# Reap ALTER TABLE
RENAME TABLES child1 TO child;
# 7.12) ALTER TABLE ... RENAME ... INPLACE which acquires lock on
#       parent table and fails due to lock timeout.
connection con1;
BEGIN;
SELECT * FROM parent;
pk
connection default;
SET @@lock_wait_timeout= 1;
ALTER TABLE child RENAME TO child1, ADD COLUMN b INT, ALGORITHM=INPLACE;
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
SET @@lock_wait_timeout= @old_lock_wait_timeout;
connection con1;
COMMIT;
# 7.13) ALTER TABLE ... RENAME ... INPLACE which tries to rename parent
#       table must acquire X lock on child table.
connection con1;
BEGIN;
SELECT * FROM child;
fk	a
connection default;
ALTER TABLE parent RENAME TO parent1, ADD COLUMN a INT, ALGORITHM=INPLACE;;
connection con1;
# Wait until ALTER TABLE is blocked by con1 and then resume it.
COMMIT;
connection default;
# Reap ALTER TABLE
RENAME TABLES parent1 TO parent;
# 7.14) ALTER TABLE ... RENAME ... INPLACE which acquires lock on child
#       table and fails due to lock timeout.
connection con1;
BEGIN;
SELECT * FROM child;
fk	a
connection default;
SET @@lock_wait_timeout= 1;
ALTER TABLE parent RENAME TO parent1, ADD COLUMN b INT, ALGORITHM=INPLACE;
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
SET @@lock_wait_timeout= @old_lock_wait_timeout;
connection con1;
COMMIT;
# 7.15) ALTER TABLE ... RENAME ... INPLACE which adds parent table for
#       orphan child must acquire X lock on this child table.
connection default;
DROP TABLE child;
SET FOREIGN_KEY_CHECKS=0;
CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES parent1(pk));
SET FOREIGN_KEY_CHECKS=1;
connection con1;
BEGIN;
SELECT * FROM child;
fk
connection default;
ALTER TABLE parent RENAME TO parent1, ADD COLUMN c INT, ALGORITHM=INPLACE;;
connection con1;
# Wait until ALTER TABLE is blocked by con1 and then resume it.
COMMIT;
connection default;
# Reap ALTER TABLE
RENAME TABLES parent1 TO parent;
# 7.16) ALTER TABLE ... RENAME ... INPLACE which acquires lock on
#       orphan child table and fails due to timeout.
DROP TABLE child;
SET FOREIGN_KEY_CHECKS=0;
CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES parent1(pk));
SET FOREIGN_KEY_CHECKS=1;
connection con1;
BEGIN;
SELECT * FROM child;
fk
connection default;
SET @@lock_wait_timeout= 1;
ALTER TABLE parent RENAME TO parent1, ADD COLUMN d INT, ALGORITHM=INPLACE;
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
SET @@lock_wait_timeout= @old_lock_wait_timeout;
connection con1;
COMMIT;
connection default;
DROP TABLE child;
CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES parent(pk) ON DELETE CASCADE);
# 7.17) ALTER TABLE ... RENAME ... INPLACE on the child table should
#       invalidate entries in TC/TDC and DD caches for the parent table.
SELECT * FROM parent;
pk	a	c
connection con1;
ALTER TABLE child RENAME TO child1, ADD COLUMN a INT, ALGORITHM=INPLACE;
connection default;
LOCK TABLE parent WRITE;
connection con1;
# The above LOCK TABLE should have noticed new table definition
# and acquire SNRW lock on new child table name.
SELECT * FROM child1;;
connection default;
# Wait until SELECT is blocked by default connection and then resume it.
UNLOCK TABLES;
connection con1;
# Reap SELECT
fk	a
connection default;
DROP TABLES child1, parent;
#
# 8) ALTER TABLE ... COPY
#
# 8.1) ALTER TABLE ... ADD FOREIGN KEY ... COPY must start by
#      acquiring SU lock on parent table.
# 8.1') ALTER TABLE ... ADD FOREIGN KEY ... COPY due to workaround
#       must upgrade SU lock on parent table SRO lock.
#
# These tests use debug_sync feature so reside in foreign_key_debug.test
#
# 8.2) ALTER TABLE ... ADD FOREIGN KEY ... COPY should upgrade SU (SRO)
#      lock on parent to X before commit (i.e eventually X lock should
#      be acquired).
CREATE TABLE parent (pk INT PRIMARY KEY);
CREATE TABLE child (fk INT);
connection con1;
BEGIN;
SELECT * FROM parent;
pk
connection default;
ALTER TABLE child ADD CONSTRAINT fk FOREIGN KEY (fk) REFERENCES parent(pk), ALGORITHM=COPY;;
connection con1;
# Wait until ALTER TABLE is blocked by con1 and then resume it.
COMMIT;
connection default;
# Reap ALTER TABLE
ALTER TABLE child DROP FOREIGN KEY fk;
# 8.3) ALTER TABLE ... ADD FOREIGN KEY ... COPY which tries to
#      acquire SU lock on parent table and fails due to lock timeout.
connection con1;
LOCK TABLE parent WRITE;
connection default;
SET @@lock_wait_timeout= 1;
ALTER TABLE child ADD CONSTRAINT fk FOREIGN KEY (fk) REFERENCES parent(pk), ALGORITHM=COPY;
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
SET @@lock_wait_timeout= @old_lock_wait_timeout;
connection con1;
UNLOCK TABLES;
# 8.3') ALTER TABLE ... ADD FOREIGN KEY ... COPY which due to workaround
#       tries to upgrade SU lock on parent table to SRO and fails due to
#       lock timeout.
connection con1;
BEGIN;
DELETE FROM parent;
connection default;
SET @@lock_wait_timeout= 1;
ALTER TABLE child ADD CONSTRAINT fk FOREIGN KEY (fk) REFERENCES parent(pk), ALGORITHM=COPY;
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
SET @@lock_wait_timeout= @old_lock_wait_timeout;
connection con1;
COMMIT;
# 8.4) ALTER TABLE ... ADD FOREIGN KEY ... COPY which tries to
#      upgrade to X lock on parent table and fails due to lock timeout.
BEGIN;
SELECT * FROM parent;
pk
connection default;
SET @@lock_wait_timeout= 1;
ALTER TABLE child ADD CONSTRAINT fk FOREIGN KEY (fk) REFERENCES parent(pk), ALGORITHM=COPY;
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
SET @@lock_wait_timeout= @old_lock_wait_timeout;
connection con1;
COMMIT;
# 8.5) ALTER TABLE ... ADD FOREIGN KEY ... COPY should invalidate
#      entries in TC/TDC and DD caches for the parent table.
SELECT * FROM parent;
pk
connection con1;
ALTER TABLE child ADD CONSTRAINT fk FOREIGN KEY (fk) REFERENCES parent(pk) ON DELETE CASCADE, ALGORITHM=COPY;
connection default;
LOCK TABLE parent WRITE;
connection con1;
# The above LOCK TABLE should have noticed new table definition
# and acquired SNRW lock on child table.
SELECT * FROM child;;
connection default;
# Wait until SELECT is blocked by default connection and then resume it.
UNLOCK TABLES;
connection con1;
# Reap SELECT
fk
# 8.6) ALTER TABLE ... DROP FOREIGN KEY ... COPY should acquire
#      lock on parent to X before commit.
connection con1;
BEGIN;
SELECT * FROM parent;
pk
connection default;
ALTER TABLE child DROP FOREIGN KEY fk, ALGORITHM=COPY;;
connection con1;
# Wait until ALTER TABLE is blocked by con1 and then resume it.
COMMIT;
connection default;
# Reap ALTER TABLE
ALTER TABLE child ADD CONSTRAINT fk FOREIGN KEY (fk) REFERENCES parent(pk) ON DELETE CASCADE;
# 8.7) ALTER TABLE ... DROP FOREIGN KEY ... COPY which tries to
#      upgrade to X lock on parent table and fails due to lock timeout.
connection con1;
BEGIN;
SELECT * FROM parent;
pk
connection default;
SET @@lock_wait_timeout= 1;
ALTER TABLE child DROP FOREIGN KEY fk, ALGORITHM=COPY;
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
SET @@lock_wait_timeout= @old_lock_wait_timeout;
connection con1;
COMMIT;
# 8.8) ALTER TABLE ... DROP FOREIGN KEY ... COPY should invalidate entries
#      in TC/TDC and DD caches for the parent table.
SELECT * FROM parent;
pk
connection con1;
ALTER TABLE child DROP FOREIGN KEY fk, ALGORITHM=COPY;
connection default;
LOCK TABLE parent WRITE;
connection con1;
# The above LOCK TABLE should have noticed new table definition
# and avoid acquiring SNRW lock on child table.
SELECT * FROM child;
fk
connection default;
UNLOCK TABLES;
# 8.9) ALTER TABLE ... COPY which changes parent key must acquire X
#      lock on child table.
DROP TABLES child, parent;
CREATE TABLE parent (pk INT NOT NULL, UNIQUE u(pk));
CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES parent(pk));
connection con1;
BEGIN;
SELECT * FROM child;
fk
connection default;
ALTER TABLE parent RENAME KEY u TO u1, ALGORITHM=COPY;;
connection con1;
# Wait until ALTER TABLE is blocked by con1 and then resume it.
COMMIT;
connection default;
# Reap ALTER TABLE
# 8.10) ALTER TABLE ... COPY which changes parent key, so tries to
#       acquire X lock on child table, but fails due to timeout.
connection con1;
BEGIN;
SELECT * FROM child;
fk
connection default;
SET @@lock_wait_timeout= 1;
ALTER TABLE parent RENAME KEY u1 TO u, ALGORITHM=COPY;
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
SET @@lock_wait_timeout= @old_lock_wait_timeout;
connection con1;
COMMIT;
# 8.11) ALTER TABLE ... RENAME ... COPY must acquire X lock on
#       parent table for FKs when child is renamed.
connection con1;
BEGIN;
SELECT * FROM parent;
pk
connection default;
ALTER TABLE child RENAME TO child1, ADD COLUMN a INT, ALGORITHM=COPY;;
connection con1;
# Wait until ALTER TABLE is blocked by con1 and then resume it.
COMMIT;
connection default;
# Reap ALTER TABLE
RENAME TABLES child1 TO child;
# 8.12) ALTER TABLE ... RENAME ... COPY which acquires lock on
#       parent table and fails due to lock timeout.
connection con1;
BEGIN;
SELECT * FROM parent;
pk
connection default;
SET @@lock_wait_timeout= 1;
ALTER TABLE child RENAME TO child1, ADD COLUMN b INT, ALGORITHM=COPY;
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
SET @@lock_wait_timeout= @old_lock_wait_timeout;
connection con1;
COMMIT;
# 8.13) ALTER TABLE ... RENAME ... COPY which tries to rename parent
#       table must acquire X lock on child table.
connection con1;
BEGIN;
SELECT * FROM child;
fk	a
connection default;
ALTER TABLE parent RENAME TO parent1, ADD COLUMN a INT, ALGORITHM=COPY;;
connection con1;
# Wait until ALTER TABLE is blocked by con1 and then resume it.
COMMIT;
connection default;
# Reap ALTER TABLE
RENAME TABLES parent1 TO parent;
# 8.14) ALTER TABLE ... RENAME ... COPY which acquires lock on child
#       table and fails due to lock timeout.
connection con1;
BEGIN;
SELECT * FROM child;
fk	a
connection default;
SET @@lock_wait_timeout= 1;
ALTER TABLE parent RENAME TO parent1, ADD COLUMN b INT, ALGORITHM=COPY;
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
SET @@lock_wait_timeout= @old_lock_wait_timeout;
connection con1;
COMMIT;
# 8.15) ALTER TABLE ... RENAME ... COPY which adds parent table for
#       orphan child must acquire X lock on this child table.
connection default;
DROP TABLE child;
SET FOREIGN_KEY_CHECKS=0;
CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES parent1(pk));
SET FOREIGN_KEY_CHECKS=1;
connection con1;
BEGIN;
SELECT * FROM child;
fk
connection default;
ALTER TABLE parent RENAME TO parent1, ADD COLUMN c INT, ALGORITHM=COPY;;
connection con1;
# Wait until ALTER TABLE is blocked by con1 and then resume it.
COMMIT;
connection default;
# Reap ALTER TABLE
RENAME TABLES parent1 TO parent;
# 8.16) ALTER TABLE ... RENAME ... COPY which acquires lock on
#       orphan child table and fails due to timeout.
DROP TABLE child;
SET FOREIGN_KEY_CHECKS=0;
CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES parent1(pk));
SET FOREIGN_KEY_CHECKS=1;
connection con1;
BEGIN;
SELECT * FROM child;
fk
connection default;
SET @@lock_wait_timeout= 1;
ALTER TABLE parent RENAME TO parent1, ADD COLUMN d INT, ALGORITHM=COPY;
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
SET @@lock_wait_timeout= @old_lock_wait_timeout;
connection con1;
COMMIT;
connection default;
DROP TABLE child;
CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES parent(pk) ON DELETE CASCADE);
# 8.17) ALTER TABLE ... RENAME ... COPY on the child table should
#       invalidate entries in TC/TDC and DD caches for the parent table.
SELECT * FROM parent;
pk	a	c
connection con1;
ALTER TABLE child RENAME TO child1, ADD COLUMN a INT, ALGORITHM=COPY;
connection default;
LOCK TABLE parent WRITE;
connection con1;
# The above LOCK TABLE should have noticed new table definition
# and acquire SNRW lock on new child table name.
SELECT * FROM child1;;
connection default;
# Wait until SELECT is blocked by default connection and then resume it.
UNLOCK TABLES;
connection con1;
# Reap SELECT
fk	a
connection default;
DROP TABLES child1, parent;
connection con1;
disconnect con1;
connection con2;
disconnect con2;
connection default;
#
# Validation of FK and referred column names.
#
# Too long constraint name.
CREATE TABLE t (pk INTEGER PRIMARY KEY, fk_i INTEGER,
CONSTRAINT xxxxxxxxx1xxxxxxxxx2xxxxxxxxx3xxxxxxxxx4xxxxxxxxx5xxxxxxxxx6xxxxx
FOREIGN KEY (fk_i) REFERENCES x(x));
ERROR 42000: Identifier name 'xxxxxxxxx1xxxxxxxxx2xxxxxxxxx3xxxxxxxxx4xxxxxxxxx5xxxxxxxxx6xxxxx' is too long
# Too long column name.
CREATE TABLE t (pk INTEGER PRIMARY KEY, fk_i INTEGER,
FOREIGN KEY (fk_i) REFERENCES x(xxxxxxxxx1xxxxxxxxx2xxxxxxxxx3xxxxxxxxx4xxxxxxxxx5xxxxxxxxx6xxxxx));
ERROR 42000: Incorrect column name 'xxxxxxxxx1xxxxxxxxx2xxxxxxxxx3xxxxxxxxx4xxxxxxxxx5xxxxxxxxx6xxxxx'
# Column name having trailing space.
CREATE TABLE t (pk INTEGER PRIMARY KEY, fk_i INTEGER,
FOREIGN KEY (fk_i) REFERENCES x(`x `));
ERROR 42000: Incorrect column name 'x '
#
# Test DROP of table with FKs under LOCK TABLES.
#
CREATE TABLE parent(pk INTEGER PRIMARY KEY, i INTEGER, fk_i INTEGER,
UNIQUE KEY parent_i_key(i),
FOREIGN KEY (fk_i) REFERENCES parent(i));
CREATE TABLE child(pk INTEGER PRIMARY KEY, fk_i INTEGER,
FOREIGN KEY (fk_i) REFERENCES parent(i));
SET @@session.foreign_key_checks= 1;
# Drop only parent with FKC == 0.
LOCK TABLE parent WRITE;
DROP TABLES parent;
ERROR HY000: Cannot drop table 'parent' referenced by a foreign key constraint 'child_ibfk_1' on table 'child'.
UNLOCK TABLE;
SET @@session.foreign_key_checks= 0;
# Drop only parent.
LOCK TABLE parent WRITE;
DROP TABLES parent;
UNLOCK TABLE;
SET @@session.foreign_key_checks= 1;
CREATE TABLE parent(pk INTEGER PRIMARY KEY, i INTEGER,
UNIQUE KEY parent_i_key(i));
# Drop both child and parent.
LOCK TABLES child WRITE, parent WRITE;
DROP TABLES child, parent;
UNLOCK TABLES;
SET @@session.foreign_key_checks= DEFAULT;
#
# Test ALTER TABLE ... ADD FOREIGN KEY under LOCK TABLES.
#
CREATE TABLE parent (pk INT PRIMARY KEY);
CREATE TABLE child (fk INT);
LOCK TABLES child WRITE;
ALTER TABLE child ADD CONSTRAINT fk FOREIGN KEY (fk) REFERENCES parent(pk);
ERROR HY000: Table 'parent' was not locked with LOCK TABLES
UNLOCK TABLES;
# With parent table locked for read it should be possible to add FK.
LOCK TABLES child WRITE, parent READ;
ALTER TABLE child ADD CONSTRAINT fk FOREIGN KEY (fk) REFERENCES parent(pk);
UNLOCK TABLES;
#
# Test ALTER TABLE ... RENAME with FKs under LOCK TABLES.
#
# Bug 26647340 "LIFT LIMITATION ON ALTER TABLE RENAME + TABLES WITH
#               FOREIGN KEYS + LOCK TABLES".
#
# Renaming of tables participating in FKs is allowed.
LOCK TABLES child WRITE;
ALTER TABLE child RENAME TO child1;
UNLOCK TABLES;
LOCK TABLES parent WRITE;
ALTER TABLE parent RENAME TO parent1;
UNLOCK TABLES;
# Check that it doesn't break FK invariants for LOCK TABLES.
LOCK TABLES child1 WRITE, parent1 WRITE;
ALTER TABLE child1 RENAME TO child;
INSERT INTO child VALUES (NULL);
DELETE FROM parent1;
UNLOCK TABLES;
LOCK TABLES child WRITE, parent1 WRITE;
ALTER TABLE parent1 RENAME TO parent;
INSERT INTO child VALUES (NULL);
UNLOCK TABLES;
# It is also allowed to add foreign key along with rename.
ALTER TABLE child DROP FOREIGN KEY fk;
LOCK TABLES child WRITE, parent WRITE;
ALTER TABLE child RENAME TO child1, ADD CONSTRAINT fk FOREIGN KEY (fk) REFERENCES parent(pk);
# And FK invariants for LOCK TABLES are preserved too.
INSERT INTO child1 VALUES (NULL);
DELETE FROM parent;
UNLOCK TABLES;
DROP TABLE child1;
# We also allow renames which add parent for previously orphan FKs.
# Provided that we have write lock on these children.
SET FOREIGN_KEY_CHECKS=0;
CREATE TABLE child (fk INT, FOREIGN KEY(fk) REFERENCES parent1(pk) ON DELETE CASCADE);
SET FOREIGN_KEY_CHECKS=1;
LOCK TABLE parent WRITE;
ALTER TABLE parent RENAME TO parent1;
ERROR HY000: Table 'child' was locked with a READ lock and can't be updated
UNLOCK TABLES;
LOCK TABLE parent WRITE, child READ;
ALTER TABLE parent RENAME TO parent1;
ERROR HY000: Table 'child' was locked with a READ lock and can't be updated
UNLOCK TABLES;
LOCK TABLE parent WRITE, child WRITE;
ALTER TABLE parent RENAME TO parent1;
# Invariants should be fine
INSERT INTO child VALUES (NULL);
DELETE FROM parent1;
UNLOCK TABLES;
DROP TABLES child, parent1;
#
# Bug #25722221 "RENAME COLUMN DID NOT UPDATE FOREIGN_KEY_COLUMN_USAGE
#                FOR FK CONSTRAINT".
# Bug #26659110 "LIFT LIMITATION ON RENAMING PARENT COLUMNS WHICH ARE
#                REFERENCED BY FOREIGN KEYS".
#
CREATE TABLE t1 (pk INT PRIMARY KEY, fk INT,
FOREIGN KEY (fk) REFERENCES t1 (pk));
SELECT constraint_name, table_name, column_name, referenced_column_name
FROM information_schema.key_column_usage
WHERE table_schema='test' AND table_name='t1';
constraint_name	table_name	column_name	referenced_column_name
PRIMARY	t1	pk	NULL
t1_ibfk_1	t1	fk	pk
ALTER TABLE t1 CHANGE pk id INT;
SELECT constraint_name, table_name, column_name, referenced_column_name
FROM information_schema.key_column_usage
WHERE table_schema='test' AND table_name='t1';
constraint_name	table_name	column_name	referenced_column_name
PRIMARY	t1	id	NULL
t1_ibfk_1	t1	fk	id
# Renaming of parent columns using COPY algorithm is not supported.
ALTER TABLE t1 CHANGE id pk INT, ALGORITHM=COPY;
ERROR 0A000: ALGORITHM=COPY is not supported. Reason: Columns participating in a foreign key are renamed. Try ALGORITHM=INPLACE.
SELECT constraint_name, table_name, column_name, referenced_column_name
FROM information_schema.key_column_usage
WHERE table_schema='test' AND table_name='t1';
constraint_name	table_name	column_name	referenced_column_name
PRIMARY	t1	id	NULL
t1_ibfk_1	t1	fk	id
ALTER TABLE t1 CHANGE id pk INT, ALGORITHM=INPLACE;
SELECT constraint_name, table_name, column_name, referenced_column_name
FROM information_schema.key_column_usage
WHERE table_schema='test' AND table_name='t1';
constraint_name	table_name	column_name	referenced_column_name
PRIMARY	t1	pk	NULL
t1_ibfk_1	t1	fk	pk
ALTER TABLE t1 RENAME COLUMN pk TO id;
SELECT constraint_name, table_name, column_name, referenced_column_name
FROM information_schema.key_column_usage
WHERE table_schema='test' AND table_name='t1';
constraint_name	table_name	column_name	referenced_column_name
PRIMARY	t1	id	NULL
t1_ibfk_1	t1	fk	id
# Renaming of parent columns using COPY algorithm is not supported.
ALTER TABLE t1 RENAME COLUMN id TO pk, ALGORITHM=COPY;
ERROR 0A000: ALGORITHM=COPY is not supported. Reason: Columns participating in a foreign key are renamed. Try ALGORITHM=INPLACE.
SELECT constraint_name, table_name, column_name, referenced_column_name
FROM information_schema.key_column_usage
WHERE table_schema='test' AND table_name='t1';
constraint_name	table_name	column_name	referenced_column_name
PRIMARY	t1	id	NULL
t1_ibfk_1	t1	fk	id
ALTER TABLE t1 RENAME COLUMN id TO pk, ALGORITHM=INPLACE;
SELECT constraint_name, table_name, column_name, referenced_column_name
FROM information_schema.key_column_usage
WHERE table_schema='test' AND table_name='t1';
constraint_name	table_name	column_name	referenced_column_name
PRIMARY	t1	pk	NULL
t1_ibfk_1	t1	fk	pk
DROP TABLE t1;
CREATE TABLE t1 (pk INT PRIMARY KEY);
CREATE TABLE t2 (fk INT, FOREIGN KEY (fk) REFERENCES t1 (pk));
SELECT constraint_name, table_name, column_name, referenced_column_name
FROM information_schema.key_column_usage
WHERE table_schema='test' AND table_name='t2';
constraint_name	table_name	column_name	referenced_column_name
t2_ibfk_1	t2	fk	pk
ALTER TABLE t1 CHANGE pk id INT;
SELECT constraint_name, table_name, column_name, referenced_column_name
FROM information_schema.key_column_usage
WHERE table_schema='test' AND table_name='t2';
constraint_name	table_name	column_name	referenced_column_name
t2_ibfk_1	t2	fk	id
# Renaming of parent columns using COPY algorithm is not supported.
ALTER TABLE t1 CHANGE id pk INT, ALGORITHM=COPY;
ERROR 0A000: ALGORITHM=COPY is not supported. Reason: Columns participating in a foreign key are renamed. Try ALGORITHM=INPLACE.
SELECT constraint_name, table_name, column_name, referenced_column_name
FROM information_schema.key_column_usage
WHERE table_schema='test' AND table_name='t2';
constraint_name	table_name	column_name	referenced_column_name
t2_ibfk_1	t2	fk	id
ALTER TABLE t1 CHANGE id pk INT, ALGORITHM=INPLACE;
SELECT constraint_name, table_name, column_name, referenced_column_name
FROM information_schema.key_column_usage
WHERE table_schema='test' AND table_name='t2';
constraint_name	table_name	column_name	referenced_column_name
t2_ibfk_1	t2	fk	pk
ALTER TABLE t1 RENAME COLUMN pk TO id;
SELECT constraint_name, table_name, column_name, referenced_column_name
FROM information_schema.key_column_usage
WHERE table_schema='test' AND table_name='t2';
constraint_name	table_name	column_name	referenced_column_name
t2_ibfk_1	t2	fk	id
# Renaming of parent columns using COPY algorithm is not supported.
ALTER TABLE t1 RENAME COLUMN id TO pk, ALGORITHM=COPY;
ERROR 0A000: ALGORITHM=COPY is not supported. Reason: Columns participating in a foreign key are renamed. Try ALGORITHM=INPLACE.
SELECT constraint_name, table_name, column_name, referenced_column_name
FROM information_schema.key_column_usage
WHERE table_schema='test' AND table_name='t2';
constraint_name	table_name	column_name	referenced_column_name
t2_ibfk_1	t2	fk	id
ALTER TABLE t1 RENAME COLUMN id TO pk, ALGORITHM=INPLACE;
SELECT constraint_name, table_name, column_name, referenced_column_name
FROM information_schema.key_column_usage
WHERE table_schema='test' AND table_name='t2';
constraint_name	table_name	column_name	referenced_column_name
t2_ibfk_1	t2	fk	pk
DROP TABLES t2, t1;
# Coverage for cases with multiple foreign keys.
CREATE TABLE t1 (pk INT PRIMARY KEY, u1 INT, u2 INT, fk1 INT, fk2 INT,
UNIQUE (u1), UNIQUE (u2),
FOREIGN KEY (fk1) REFERENCES t1 (u1),
FOREIGN KEY (fk2) REFERENCES t1 (u2));
SELECT constraint_name, table_name, column_name, referenced_column_name
FROM information_schema.key_column_usage
WHERE table_schema='test' AND table_name='t1';
constraint_name	table_name	column_name	referenced_column_name
PRIMARY	t1	pk	NULL
u1	t1	u1	NULL
u2	t1	u2	NULL
t1_ibfk_1	t1	fk1	u1
t1_ibfk_2	t1	fk2	u2
ALTER TABLE t1 RENAME COLUMN u1 TO u3;
SELECT constraint_name, table_name, column_name, referenced_column_name
FROM information_schema.key_column_usage
WHERE table_schema='test' AND table_name='t1';
constraint_name	table_name	column_name	referenced_column_name
PRIMARY	t1	pk	NULL
u1	t1	u3	NULL
u2	t1	u2	NULL
t1_ibfk_1	t1	fk1	u3
t1_ibfk_2	t1	fk2	u2
ALTER TABLE t1 RENAME COLUMN u3 TO u4, RENAME COLUMN u2 TO u5;
SELECT constraint_name, table_name, column_name, referenced_column_name
FROM information_schema.key_column_usage
WHERE table_schema='test' AND table_name='t1';
constraint_name	table_name	column_name	referenced_column_name
PRIMARY	t1	pk	NULL
u1	t1	u4	NULL
u2	t1	u5	NULL
t1_ibfk_1	t1	fk1	u4
t1_ibfk_2	t1	fk2	u5
DROP TABLE t1;
CREATE TABLE t1 (pk INT PRIMARY KEY, u1 INT, u2 INT, UNIQUE (u1), UNIQUE (u2));
CREATE TABLE t2 (fk1 INT, fk2 INT,
FOREIGN KEY (fk1) REFERENCES t1 (u1),
FOREIGN KEY (fk2) REFERENCES t1 (u2));
SELECT constraint_name, table_name, column_name, referenced_column_name
FROM information_schema.key_column_usage
WHERE table_schema='test' AND table_name='t2';
constraint_name	table_name	column_name	referenced_column_name
t2_ibfk_1	t2	fk1	u1
t2_ibfk_2	t2	fk2	u2
ALTER TABLE t1 RENAME COLUMN u1 TO u3;
SELECT constraint_name, table_name, column_name, referenced_column_name
FROM information_schema.key_column_usage
WHERE table_schema='test' AND table_name='t2';
constraint_name	table_name	column_name	referenced_column_name
t2_ibfk_1	t2	fk1	u3
t2_ibfk_2	t2	fk2	u2
ALTER TABLE t1 RENAME COLUMN u3 TO u4, RENAME COLUMN u2 TO u5;
SELECT constraint_name, table_name, column_name, referenced_column_name
FROM information_schema.key_column_usage
WHERE table_schema='test' AND table_name='t2';
constraint_name	table_name	column_name	referenced_column_name
t2_ibfk_1	t2	fk1	u4
t2_ibfk_2	t2	fk2	u5
DROP TABLES t2, t1;
#
# WL#6049, bug#26654674 "TABLE_CACHE_MANAGER::FREE_TABLE(THD*,
#                        ENUM_TDC_REMOVE_TABLE_TYPE, TABLE_SHARE*)".
#
CREATE TABLE t1 (u INT NOT NULL, UNIQUE u(u));
# First, check that we correctly handle open HANDLERs during
# parent invalidation.
HANDLER t1 OPEN;
CREATE TABLE t2 (fk INT, FOREIGN KEY (fk) REFERENCES t1 (u));
HANDLER t1 CLOSE;
HANDLER t1 OPEN AS a;
HANDLER t1 OPEN AS b;
CREATE TABLE t3 (fk INT, FOREIGN KEY (fk) REFERENCES t1 (u));
HANDLER a CLOSE;
HANDLER b CLOSE;
# Then, check that we correctly handle HANDLERs on child table
# during parent definiton change.
HANDLER t2 OPEN;
HANDLER t3 OPEN AS a;
HANDLER t3 OPEN AS b;
ALTER TABLE t1 RENAME KEY u TO u1;
HANDLER t2 CLOSE;
HANDLER a CLOSE;
HANDLER b CLOSE;
# Now, let us check what happens when parent is renamed.
HANDLER t2 OPEN;
HANDLER t3 OPEN AS a;
HANDLER t3 OPEN AS b;
ALTER TABLE t1 RENAME TO t4;
HANDLER t2 CLOSE;
HANDLER a CLOSE;
HANDLER b CLOSE;
# Finally, check what happens when parent table is deleted.
# Do clean-up as side-effect.
HANDLER t2 OPEN;
HANDLER t3 OPEN AS a;
HANDLER t3 OPEN AS b;
SET FOREIGN_KEY_CHECKS=0;
DROP TABLE t4;
DROP TABLES t2, t3;
SET FOREIGN_KEY_CHECKS=1;
#
# Additional test coverage for foreign keys and prepared statement
# validation.
#
CREATE TABLE t1 (i INT PRIMARY KEY);
CREATE TABLE t2 (j INT, FOREIGN KEY (j) REFERENCES t1 (i) ON DELETE CASCADE);
CREATE TABLE t3 (k INT);
CREATE TRIGGER bi_t3 BEFORE INSERT ON t3 FOR EACH ROW
BEGIN
IF @a = 1234567890 THEN
DELETE FROM t1;
END IF;
END|
PREPARE stmt FROM 'INSERT INTO t3 VALUES (1)';
EXECUTE stmt;
DROP TABLES t2, t1;
# Statement should succeed even though we won't be able check
# prelocked set element for child table.
EXECUTE stmt;
CREATE TABLE t1 (i INT PRIMARY KEY);
CREATE TABLE t2 (j INT, FOREIGN KEY (j) REFERENCES t1 (i) ON DELETE CASCADE);
EXECUTE stmt;
DROP TABLES t2, t1;
CREATE VIEW t2 AS SELECT 1 AS j;
# Again statement should succeed, even though we have view instead
# of child table.
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
DROP TABLE t3;
DROP VIEW t2;
#
# Test for bug #27041477 "ASSERTION `HAS_PRELOCKING_LIST ||
#                         THD->MDL_CONTEXT.OWNS_EQUAL_OR_STRONGER_LOCK".
#
CREATE TABLE t0 (i INT);
CREATE TRIGGER t0_bi BEFORE INSERT ON t0 FOR EACH ROW DELETE FROM t1;
CREATE TABLE t1 (pk INT PRIMARY KEY);
CREATE TABLE t2 (fk INT, FOREIGN KEY (fk) REFERENCES t1 (pk) ON UPDATE SET NULL);
LOCK TABLE t1 READ;
DELETE FROM t1;
ERROR HY000: Table 't2' was not locked with LOCK TABLES
UNLOCK TABLES;
LOCK TABLES t0 WRITE;
UPDATE t1 SET pk = 10;
ERROR HY000: Table 't2' was locked with a READ lock and can't be updated
UNLOCK TABLES;
DROP TABLES t2, t1, t0;
#
# Additional coverage for bug #25915132 "INPLACE ALTER TABLE WITH
# FOREIGN KEYS CAUSES TABLE DEFINITION MISMATCH".
#
#
# Handling of foreign key names during various RENAME variants.
# We check that table definition is updated correctly and that
# asserts about MDL on foreign key names do not fail.
#
CREATE TABLE t1 (pk INT PRIMARY KEY);
CREATE TABLE t2 (fk1 INT, fk2 INT, fk3 INT,
CONSTRAINT a FOREIGN KEY (fk1) REFERENCES t1 (pk),
CONSTRAINT t2_ibfk_1 FOREIGN KEY (fk2) REFERENCES t1 (pk));
ALTER TABLE t2 ADD FOREIGN KEY (fk3) REFERENCES t1 (pk);
CREATE SCHEMA mysqltest;
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `fk1` int(11) DEFAULT NULL,
  `fk2` int(11) DEFAULT NULL,
  `fk3` int(11) DEFAULT NULL,
  KEY `a` (`fk1`),
  KEY `t2_ibfk_1` (`fk2`),
  KEY `fk3` (`fk3`),
  CONSTRAINT `a` FOREIGN KEY (`fk1`) REFERENCES `t1` (`pk`),
  CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`fk2`) REFERENCES `t1` (`pk`),
  CONSTRAINT `t2_ibfk_2` FOREIGN KEY (`fk3`) REFERENCES `t1` (`pk`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
SELECT * FROM information_schema.referential_constraints WHERE constraint_schema='test';
CONSTRAINT_CATALOG	CONSTRAINT_SCHEMA	CONSTRAINT_NAME	UNIQUE_CONSTRAINT_CATALOG	UNIQUE_CONSTRAINT_SCHEMA	UNIQUE_CONSTRAINT_NAME	MATCH_OPTION	UPDATE_RULE	DELETE_RULE	TABLE_NAME	REFERENCED_TABLE_NAME
def	test	a	def	test	PRIMARY	NONE	NO ACTION	NO ACTION	t2	t1
def	test	t2_ibfk_1	def	test	PRIMARY	NONE	NO ACTION	NO ACTION	t2	t1
def	test	t2_ibfk_2	def	test	PRIMARY	NONE	NO ACTION	NO ACTION	t2	t1
# Simple RENAME TABLE
RENAME TABLE t2 TO t3;
SHOW CREATE TABLE t3;
Table	Create Table
t3	CREATE TABLE `t3` (
  `fk1` int(11) DEFAULT NULL,
  `fk2` int(11) DEFAULT NULL,
  `fk3` int(11) DEFAULT NULL,
  KEY `a` (`fk1`),
  KEY `t2_ibfk_1` (`fk2`),
  KEY `fk3` (`fk3`),
  CONSTRAINT `a` FOREIGN KEY (`fk1`) REFERENCES `t1` (`pk`),
  CONSTRAINT `t3_ibfk_1` FOREIGN KEY (`fk2`) REFERENCES `t1` (`pk`),
  CONSTRAINT `t3_ibfk_2` FOREIGN KEY (`fk3`) REFERENCES `t1` (`pk`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
SELECT * FROM information_schema.referential_constraints WHERE constraint_schema='test';
CONSTRAINT_CATALOG	CONSTRAINT_SCHEMA	CONSTRAINT_NAME	UNIQUE_CONSTRAINT_CATALOG	UNIQUE_CONSTRAINT_SCHEMA	UNIQUE_CONSTRAINT_NAME	MATCH_OPTION	UPDATE_RULE	DELETE_RULE	TABLE_NAME	REFERENCED_TABLE_NAME
def	test	a	def	test	PRIMARY	NONE	NO ACTION	NO ACTION	t3	t1
def	test	t3_ibfk_1	def	test	PRIMARY	NONE	NO ACTION	NO ACTION	t3	t1
def	test	t3_ibfk_2	def	test	PRIMARY	NONE	NO ACTION	NO ACTION	t3	t1
# Two cross database RENAME TABLE variants
RENAME TABLE t3 TO mysqltest.t3;
SHOW CREATE TABLE mysqltest.t3;
Table	Create Table
t3	CREATE TABLE `t3` (
  `fk1` int(11) DEFAULT NULL,
  `fk2` int(11) DEFAULT NULL,
  `fk3` int(11) DEFAULT NULL,
  KEY `a` (`fk1`),
  KEY `t2_ibfk_1` (`fk2`),
  KEY `fk3` (`fk3`),
  CONSTRAINT `a` FOREIGN KEY (`fk1`) REFERENCES `test`.`t1` (`pk`),
  CONSTRAINT `t3_ibfk_1` FOREIGN KEY (`fk2`) REFERENCES `test`.`t1` (`pk`),
  CONSTRAINT `t3_ibfk_2` FOREIGN KEY (`fk3`) REFERENCES `test`.`t1` (`pk`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
SELECT * FROM information_schema.referential_constraints WHERE constraint_schema='mysqltest';
CONSTRAINT_CATALOG	CONSTRAINT_SCHEMA	CONSTRAINT_NAME	UNIQUE_CONSTRAINT_CATALOG	UNIQUE_CONSTRAINT_SCHEMA	UNIQUE_CONSTRAINT_NAME	MATCH_OPTION	UPDATE_RULE	DELETE_RULE	TABLE_NAME	REFERENCED_TABLE_NAME
def	mysqltest	a	def	test	PRIMARY	NONE	NO ACTION	NO ACTION	t3	t1
def	mysqltest	t3_ibfk_1	def	test	PRIMARY	NONE	NO ACTION	NO ACTION	t3	t1
def	mysqltest	t3_ibfk_2	def	test	PRIMARY	NONE	NO ACTION	NO ACTION	t3	t1
RENAME TABLE mysqltest.t3 TO t4;
SHOW CREATE TABLE t4;
Table	Create Table
t4	CREATE TABLE `t4` (
  `fk1` int(11) DEFAULT NULL,
  `fk2` int(11) DEFAULT NULL,
  `fk3` int(11) DEFAULT NULL,
  KEY `a` (`fk1`),
  KEY `t2_ibfk_1` (`fk2`),
  KEY `fk3` (`fk3`),
  CONSTRAINT `a` FOREIGN KEY (`fk1`) REFERENCES `t1` (`pk`),
  CONSTRAINT `t4_ibfk_1` FOREIGN KEY (`fk2`) REFERENCES `t1` (`pk`),
  CONSTRAINT `t4_ibfk_2` FOREIGN KEY (`fk3`) REFERENCES `t1` (`pk`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
SELECT * FROM information_schema.referential_constraints WHERE constraint_schema='test';
CONSTRAINT_CATALOG	CONSTRAINT_SCHEMA	CONSTRAINT_NAME	UNIQUE_CONSTRAINT_CATALOG	UNIQUE_CONSTRAINT_SCHEMA	UNIQUE_CONSTRAINT_NAME	MATCH_OPTION	UPDATE_RULE	DELETE_RULE	TABLE_NAME	REFERENCED_TABLE_NAME
def	test	a	def	test	PRIMARY	NONE	NO ACTION	NO ACTION	t4	t1
def	test	t4_ibfk_1	def	test	PRIMARY	NONE	NO ACTION	NO ACTION	t4	t1
def	test	t4_ibfk_2	def	test	PRIMARY	NONE	NO ACTION	NO ACTION	t4	t1
# Simple ALTER TABLE RENAME variants.
ALTER TABLE t4 RENAME TO t5;
SHOW CREATE TABLE t5;
Table	Create Table
t5	CREATE TABLE `t5` (
  `fk1` int(11) DEFAULT NULL,
  `fk2` int(11) DEFAULT NULL,
  `fk3` int(11) DEFAULT NULL,
  KEY `a` (`fk1`),
  KEY `t2_ibfk_1` (`fk2`),
  KEY `fk3` (`fk3`),
  CONSTRAINT `a` FOREIGN KEY (`fk1`) REFERENCES `t1` (`pk`),
  CONSTRAINT `t5_ibfk_1` FOREIGN KEY (`fk2`) REFERENCES `t1` (`pk`),
  CONSTRAINT `t5_ibfk_2` FOREIGN KEY (`fk3`) REFERENCES `t1` (`pk`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
SELECT * FROM information_schema.referential_constraints WHERE constraint_schema='test';
CONSTRAINT_CATALOG	CONSTRAINT_SCHEMA	CONSTRAINT_NAME	UNIQUE_CONSTRAINT_CATALOG	UNIQUE_CONSTRAINT_SCHEMA	UNIQUE_CONSTRAINT_NAME	MATCH_OPTION	UPDATE_RULE	DELETE_RULE	TABLE_NAME	REFERENCED_TABLE_NAME
def	test	a	def	test	PRIMARY	NONE	NO ACTION	NO ACTION	t5	t1
def	test	t5_ibfk_1	def	test	PRIMARY	NONE	NO ACTION	NO ACTION	t5	t1
def	test	t5_ibfk_2	def	test	PRIMARY	NONE	NO ACTION	NO ACTION	t5	t1
ALTER TABLE t5 RENAME TO mysqltest.t5;
SHOW CREATE TABLE mysqltest.t5;
Table	Create Table
t5	CREATE TABLE `t5` (
  `fk1` int(11) DEFAULT NULL,
  `fk2` int(11) DEFAULT NULL,
  `fk3` int(11) DEFAULT NULL,
  KEY `a` (`fk1`),
  KEY `t2_ibfk_1` (`fk2`),
  KEY `fk3` (`fk3`),
  CONSTRAINT `a` FOREIGN KEY (`fk1`) REFERENCES `test`.`t1` (`pk`),
  CONSTRAINT `t5_ibfk_1` FOREIGN KEY (`fk2`) REFERENCES `test`.`t1` (`pk`),
  CONSTRAINT `t5_ibfk_2` FOREIGN KEY (`fk3`) REFERENCES `test`.`t1` (`pk`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
SELECT * FROM information_schema.referential_constraints WHERE constraint_schema='mysqltest';
CONSTRAINT_CATALOG	CONSTRAINT_SCHEMA	CONSTRAINT_NAME	UNIQUE_CONSTRAINT_CATALOG	UNIQUE_CONSTRAINT_SCHEMA	UNIQUE_CONSTRAINT_NAME	MATCH_OPTION	UPDATE_RULE	DELETE_RULE	TABLE_NAME	REFERENCED_TABLE_NAME
def	mysqltest	a	def	test	PRIMARY	NONE	NO ACTION	NO ACTION	t5	t1
def	mysqltest	t5_ibfk_1	def	test	PRIMARY	NONE	NO ACTION	NO ACTION	t5	t1
def	mysqltest	t5_ibfk_2	def	test	PRIMARY	NONE	NO ACTION	NO ACTION	t5	t1
ALTER TABLE mysqltest.t5 RENAME TO t6;
SHOW CREATE TABLE t6;
Table	Create Table
t6	CREATE TABLE `t6` (
  `fk1` int(11) DEFAULT NULL,
  `fk2` int(11) DEFAULT NULL,
  `fk3` int(11) DEFAULT NULL,
  KEY `a` (`fk1`),
  KEY `t2_ibfk_1` (`fk2`),
  KEY `fk3` (`fk3`),
  CONSTRAINT `a` FOREIGN KEY (`fk1`) REFERENCES `t1` (`pk`),
  CONSTRAINT `t6_ibfk_1` FOREIGN KEY (`fk2`) REFERENCES `t1` (`pk`),
  CONSTRAINT `t6_ibfk_2` FOREIGN KEY (`fk3`) REFERENCES `t1` (`pk`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
SELECT * FROM information_schema.referential_constraints WHERE constraint_schema='test';
CONSTRAINT_CATALOG	CONSTRAINT_SCHEMA	CONSTRAINT_NAME	UNIQUE_CONSTRAINT_CATALOG	UNIQUE_CONSTRAINT_SCHEMA	UNIQUE_CONSTRAINT_NAME	MATCH_OPTION	UPDATE_RULE	DELETE_RULE	TABLE_NAME	REFERENCED_TABLE_NAME
def	test	a	def	test	PRIMARY	NONE	NO ACTION	NO ACTION	t6	t1
def	test	t6_ibfk_1	def	test	PRIMARY	NONE	NO ACTION	NO ACTION	t6	t1
def	test	t6_ibfk_2	def	test	PRIMARY	NONE	NO ACTION	NO ACTION	t6	t1
# ALTER TABLE INPLACE with RENAME clause.
ALTER TABLE t6 ADD COLUMN i INT, RENAME TO t7, ALGORITHM=INPLACE;
SHOW CREATE TABLE t7;
Table	Create Table
t7	CREATE TABLE `t7` (
  `fk1` int(11) DEFAULT NULL,
  `fk2` int(11) DEFAULT NULL,
  `fk3` int(11) DEFAULT NULL,
  `i` int(11) DEFAULT NULL,
  KEY `a` (`fk1`),
  KEY `t2_ibfk_1` (`fk2`),
  KEY `fk3` (`fk3`),
  CONSTRAINT `a` FOREIGN KEY (`fk1`) REFERENCES `t1` (`pk`),
  CONSTRAINT `t7_ibfk_1` FOREIGN KEY (`fk2`) REFERENCES `t1` (`pk`),
  CONSTRAINT `t7_ibfk_2` FOREIGN KEY (`fk3`) REFERENCES `t1` (`pk`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
SELECT * FROM information_schema.referential_constraints WHERE constraint_schema='test';
CONSTRAINT_CATALOG	CONSTRAINT_SCHEMA	CONSTRAINT_NAME	UNIQUE_CONSTRAINT_CATALOG	UNIQUE_CONSTRAINT_SCHEMA	UNIQUE_CONSTRAINT_NAME	MATCH_OPTION	UPDATE_RULE	DELETE_RULE	TABLE_NAME	REFERENCED_TABLE_NAME
def	test	a	def	test	PRIMARY	NONE	NO ACTION	NO ACTION	t7	t1
def	test	t7_ibfk_1	def	test	PRIMARY	NONE	NO ACTION	NO ACTION	t7	t1
def	test	t7_ibfk_2	def	test	PRIMARY	NONE	NO ACTION	NO ACTION	t7	t1
ALTER TABLE t7 ADD COLUMN j INT, RENAME TO mysqltest.t7, ALGORITHM=INPLACE;
SHOW CREATE TABLE mysqltest.t7;
Table	Create Table
t7	CREATE TABLE `t7` (
  `fk1` int(11) DEFAULT NULL,
  `fk2` int(11) DEFAULT NULL,
  `fk3` int(11) DEFAULT NULL,
  `i` int(11) DEFAULT NULL,
  `j` int(11) DEFAULT NULL,
  KEY `a` (`fk1`),
  KEY `t2_ibfk_1` (`fk2`),
  KEY `fk3` (`fk3`),
  CONSTRAINT `a` FOREIGN KEY (`fk1`) REFERENCES `test`.`t1` (`pk`),
  CONSTRAINT `t7_ibfk_1` FOREIGN KEY (`fk2`) REFERENCES `test`.`t1` (`pk`),
  CONSTRAINT `t7_ibfk_2` FOREIGN KEY (`fk3`) REFERENCES `test`.`t1` (`pk`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
SELECT * FROM information_schema.referential_constraints WHERE constraint_schema='mysqltest';
CONSTRAINT_CATALOG	CONSTRAINT_SCHEMA	CONSTRAINT_NAME	UNIQUE_CONSTRAINT_CATALOG	UNIQUE_CONSTRAINT_SCHEMA	UNIQUE_CONSTRAINT_NAME	MATCH_OPTION	UPDATE_RULE	DELETE_RULE	TABLE_NAME	REFERENCED_TABLE_NAME
def	mysqltest	a	def	test	PRIMARY	NONE	NO ACTION	NO ACTION	t7	t1
def	mysqltest	t7_ibfk_1	def	test	PRIMARY	NONE	NO ACTION	NO ACTION	t7	t1
def	mysqltest	t7_ibfk_2	def	test	PRIMARY	NONE	NO ACTION	NO ACTION	t7	t1
ALTER TABLE mysqltest.t7 ADD COLUMN k INT, RENAME TO t8, ALGORITHM=INPLACE;
SHOW CREATE TABLE t8;
Table	Create Table
t8	CREATE TABLE `t8` (
  `fk1` int(11) DEFAULT NULL,
  `fk2` int(11) DEFAULT NULL,
  `fk3` int(11) DEFAULT NULL,
  `i` int(11) DEFAULT NULL,
  `j` int(11) DEFAULT NULL,
  `k` int(11) DEFAULT NULL,
  KEY `a` (`fk1`),
  KEY `t2_ibfk_1` (`fk2`),
  KEY `fk3` (`fk3`),
  CONSTRAINT `a` FOREIGN KEY (`fk1`) REFERENCES `t1` (`pk`),
  CONSTRAINT `t8_ibfk_1` FOREIGN KEY (`fk2`) REFERENCES `t1` (`pk`),
  CONSTRAINT `t8_ibfk_2` FOREIGN KEY (`fk3`) REFERENCES `t1` (`pk`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
SELECT * FROM information_schema.referential_constraints WHERE constraint_schema='test';
CONSTRAINT_CATALOG	CONSTRAINT_SCHEMA	CONSTRAINT_NAME	UNIQUE_CONSTRAINT_CATALOG	UNIQUE_CONSTRAINT_SCHEMA	UNIQUE_CONSTRAINT_NAME	MATCH_OPTION	UPDATE_RULE	DELETE_RULE	TABLE_NAME	REFERENCED_TABLE_NAME
def	test	a	def	test	PRIMARY	NONE	NO ACTION	NO ACTION	t8	t1
def	test	t8_ibfk_1	def	test	PRIMARY	NONE	NO ACTION	NO ACTION	t8	t1
def	test	t8_ibfk_2	def	test	PRIMARY	NONE	NO ACTION	NO ACTION	t8	t1
# ALTER TABLE COPY with RENAME clause.
ALTER TABLE t8 ADD COLUMN l INT, RENAME TO t9, ALGORITHM=COPY;
SHOW CREATE TABLE t9;
Table	Create Table
t9	CREATE TABLE `t9` (
  `fk1` int(11) DEFAULT NULL,
  `fk2` int(11) DEFAULT NULL,
  `fk3` int(11) DEFAULT NULL,
  `i` int(11) DEFAULT NULL,
  `j` int(11) DEFAULT NULL,
  `k` int(11) DEFAULT NULL,
  `l` int(11) DEFAULT NULL,
  KEY `a` (`fk1`),
  KEY `t2_ibfk_1` (`fk2`),
  KEY `fk3` (`fk3`),
  CONSTRAINT `a` FOREIGN KEY (`fk1`) REFERENCES `t1` (`pk`),
  CONSTRAINT `t9_ibfk_1` FOREIGN KEY (`fk2`) REFERENCES `t1` (`pk`),
  CONSTRAINT `t9_ibfk_2` FOREIGN KEY (`fk3`) REFERENCES `t1` (`pk`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
SELECT * FROM information_schema.referential_constraints WHERE constraint_schema='test';
CONSTRAINT_CATALOG	CONSTRAINT_SCHEMA	CONSTRAINT_NAME	UNIQUE_CONSTRAINT_CATALOG	UNIQUE_CONSTRAINT_SCHEMA	UNIQUE_CONSTRAINT_NAME	MATCH_OPTION	UPDATE_RULE	DELETE_RULE	TABLE_NAME	REFERENCED_TABLE_NAME
def	test	a	def	test	PRIMARY	NONE	NO ACTION	NO ACTION	t9	t1
def	test	t9_ibfk_1	def	test	PRIMARY	NONE	NO ACTION	NO ACTION	t9	t1
def	test	t9_ibfk_2	def	test	PRIMARY	NONE	NO ACTION	NO ACTION	t9	t1
ALTER TABLE t9 ADD COLUMN m INT, RENAME TO mysqltest.t9, ALGORITHM=COPY;
SHOW CREATE TABLE mysqltest.t9;
Table	Create Table
t9	CREATE TABLE `t9` (
  `fk1` int(11) DEFAULT NULL,
  `fk2` int(11) DEFAULT NULL,
  `fk3` int(11) DEFAULT NULL,
  `i` int(11) DEFAULT NULL,
  `j` int(11) DEFAULT NULL,
  `k` int(11) DEFAULT NULL,
  `l` int(11) DEFAULT NULL,
  `m` int(11) DEFAULT NULL,
  KEY `a` (`fk1`),
  KEY `t2_ibfk_1` (`fk2`),
  KEY `fk3` (`fk3`),
  CONSTRAINT `a` FOREIGN KEY (`fk1`) REFERENCES `test`.`t1` (`pk`),
  CONSTRAINT `t9_ibfk_1` FOREIGN KEY (`fk2`) REFERENCES `test`.`t1` (`pk`),
  CONSTRAINT `t9_ibfk_2` FOREIGN KEY (`fk3`) REFERENCES `test`.`t1` (`pk`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
SELECT * FROM information_schema.referential_constraints WHERE constraint_schema='mysqltest';
CONSTRAINT_CATALOG	CONSTRAINT_SCHEMA	CONSTRAINT_NAME	UNIQUE_CONSTRAINT_CATALOG	UNIQUE_CONSTRAINT_SCHEMA	UNIQUE_CONSTRAINT_NAME	MATCH_OPTION	UPDATE_RULE	DELETE_RULE	TABLE_NAME	REFERENCED_TABLE_NAME
def	mysqltest	a	def	test	PRIMARY	NONE	NO ACTION	NO ACTION	t9	t1
def	mysqltest	t9_ibfk_1	def	test	PRIMARY	NONE	NO ACTION	NO ACTION	t9	t1
def	mysqltest	t9_ibfk_2	def	test	PRIMARY	NONE	NO ACTION	NO ACTION	t9	t1
ALTER TABLE mysqltest.t9 ADD COLUMN n INT, RENAME TO t10, ALGORITHM=COPY;
SHOW CREATE TABLE t10;
Table	Create Table
t10	CREATE TABLE `t10` (
  `fk1` int(11) DEFAULT NULL,
  `fk2` int(11) DEFAULT NULL,
  `fk3` int(11) DEFAULT NULL,
  `i` int(11) DEFAULT NULL,
  `j` int(11) DEFAULT NULL,
  `k` int(11) DEFAULT NULL,
  `l` int(11) DEFAULT NULL,
  `m` int(11) DEFAULT NULL,
  `n` int(11) DEFAULT NULL,
  KEY `a` (`fk1`),
  KEY `t2_ibfk_1` (`fk2`),
  KEY `fk3` (`fk3`),
  CONSTRAINT `a` FOREIGN KEY (`fk1`) REFERENCES `t1` (`pk`),
  CONSTRAINT `t10_ibfk_1` FOREIGN KEY (`fk2`) REFERENCES `t1` (`pk`),
  CONSTRAINT `t10_ibfk_2` FOREIGN KEY (`fk3`) REFERENCES `t1` (`pk`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
SELECT * FROM information_schema.referential_constraints WHERE constraint_schema='test';
CONSTRAINT_CATALOG	CONSTRAINT_SCHEMA	CONSTRAINT_NAME	UNIQUE_CONSTRAINT_CATALOG	UNIQUE_CONSTRAINT_SCHEMA	UNIQUE_CONSTRAINT_NAME	MATCH_OPTION	UPDATE_RULE	DELETE_RULE	TABLE_NAME	REFERENCED_TABLE_NAME
def	test	a	def	test	PRIMARY	NONE	NO ACTION	NO ACTION	t10	t1
def	test	t10_ibfk_1	def	test	PRIMARY	NONE	NO ACTION	NO ACTION	t10	t1
def	test	t10_ibfk_2	def	test	PRIMARY	NONE	NO ACTION	NO ACTION	t10	t1
# ALTER TABLE INPLACE + ADD FOREIGN KEY with RENAME clause.
SET FOREIGN_KEY_CHECKS=0;
ALTER TABLE t10 ADD FOREIGN KEY (i) REFERENCES t1 (pk),
ADD CONSTRAINT t10_ibfk_4 FOREIGN KEY (j) REFERENCES t1 (pk),
RENAME TO t11, ALGORITHM=INPLACE;
SHOW CREATE TABLE t11;
Table	Create Table
t11	CREATE TABLE `t11` (
  `fk1` int(11) DEFAULT NULL,
  `fk2` int(11) DEFAULT NULL,
  `fk3` int(11) DEFAULT NULL,
  `i` int(11) DEFAULT NULL,
  `j` int(11) DEFAULT NULL,
  `k` int(11) DEFAULT NULL,
  `l` int(11) DEFAULT NULL,
  `m` int(11) DEFAULT NULL,
  `n` int(11) DEFAULT NULL,
  KEY `a` (`fk1`),
  KEY `t2_ibfk_1` (`fk2`),
  KEY `fk3` (`fk3`),
  KEY `i` (`i`),
  KEY `t10_ibfk_4` (`j`),
  CONSTRAINT `a` FOREIGN KEY (`fk1`) REFERENCES `t1` (`pk`),
  CONSTRAINT `t11_ibfk_1` FOREIGN KEY (`fk2`) REFERENCES `t1` (`pk`),
  CONSTRAINT `t11_ibfk_2` FOREIGN KEY (`fk3`) REFERENCES `t1` (`pk`),
  CONSTRAINT `t11_ibfk_3` FOREIGN KEY (`i`) REFERENCES `t1` (`pk`),
  CONSTRAINT `t11_ibfk_4` FOREIGN KEY (`j`) REFERENCES `t1` (`pk`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
SELECT * FROM information_schema.referential_constraints WHERE constraint_schema='test';
CONSTRAINT_CATALOG	CONSTRAINT_SCHEMA	CONSTRAINT_NAME	UNIQUE_CONSTRAINT_CATALOG	UNIQUE_CONSTRAINT_SCHEMA	UNIQUE_CONSTRAINT_NAME	MATCH_OPTION	UPDATE_RULE	DELETE_RULE	TABLE_NAME	REFERENCED_TABLE_NAME
def	test	a	def	test	PRIMARY	NONE	NO ACTION	NO ACTION	t11	t1
def	test	t11_ibfk_1	def	test	PRIMARY	NONE	NO ACTION	NO ACTION	t11	t1
def	test	t11_ibfk_2	def	test	PRIMARY	NONE	NO ACTION	NO ACTION	t11	t1
def	test	t11_ibfk_3	def	test	PRIMARY	NONE	NO ACTION	NO ACTION	t11	t1
def	test	t11_ibfk_4	def	test	PRIMARY	NONE	NO ACTION	NO ACTION	t11	t1
ALTER TABLE t11 ADD FOREIGN KEY (k) REFERENCES test.t1 (pk),
ADD CONSTRAINT t11_ibfk_6 FOREIGN KEY (l) REFERENCES test.t1 (pk),
RENAME TO mysqltest.t11, ALGORITHM=INPLACE;
SHOW CREATE TABLE mysqltest.t11;
Table	Create Table
t11	CREATE TABLE `t11` (
  `fk1` int(11) DEFAULT NULL,
  `fk2` int(11) DEFAULT NULL,
  `fk3` int(11) DEFAULT NULL,
  `i` int(11) DEFAULT NULL,
  `j` int(11) DEFAULT NULL,
  `k` int(11) DEFAULT NULL,
  `l` int(11) DEFAULT NULL,
  `m` int(11) DEFAULT NULL,
  `n` int(11) DEFAULT NULL,
  KEY `a` (`fk1`),
  KEY `t2_ibfk_1` (`fk2`),
  KEY `fk3` (`fk3`),
  KEY `i` (`i`),
  KEY `t10_ibfk_4` (`j`),
  KEY `k` (`k`),
  KEY `t11_ibfk_6` (`l`),
  CONSTRAINT `a` FOREIGN KEY (`fk1`) REFERENCES `test`.`t1` (`pk`),
  CONSTRAINT `t11_ibfk_1` FOREIGN KEY (`fk2`) REFERENCES `test`.`t1` (`pk`),
  CONSTRAINT `t11_ibfk_2` FOREIGN KEY (`fk3`) REFERENCES `test`.`t1` (`pk`),
  CONSTRAINT `t11_ibfk_3` FOREIGN KEY (`i`) REFERENCES `test`.`t1` (`pk`),
  CONSTRAINT `t11_ibfk_4` FOREIGN KEY (`j`) REFERENCES `test`.`t1` (`pk`),
  CONSTRAINT `t11_ibfk_5` FOREIGN KEY (`k`) REFERENCES `test`.`t1` (`pk`),
  CONSTRAINT `t11_ibfk_6` FOREIGN KEY (`l`) REFERENCES `test`.`t1` (`pk`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
SELECT * FROM information_schema.referential_constraints WHERE constraint_schema='mysqltest';
CONSTRAINT_CATALOG	CONSTRAINT_SCHEMA	CONSTRAINT_NAME	UNIQUE_CONSTRAINT_CATALOG	UNIQUE_CONSTRAINT_SCHEMA	UNIQUE_CONSTRAINT_NAME	MATCH_OPTION	UPDATE_RULE	DELETE_RULE	TABLE_NAME	REFERENCED_TABLE_NAME
def	mysqltest	a	def	test	PRIMARY	NONE	NO ACTION	NO ACTION	t11	t1
def	mysqltest	t11_ibfk_1	def	test	PRIMARY	NONE	NO ACTION	NO ACTION	t11	t1
def	mysqltest	t11_ibfk_2	def	test	PRIMARY	NONE	NO ACTION	NO ACTION	t11	t1
def	mysqltest	t11_ibfk_3	def	test	PRIMARY	NONE	NO ACTION	NO ACTION	t11	t1
def	mysqltest	t11_ibfk_4	def	test	PRIMARY	NONE	NO ACTION	NO ACTION	t11	t1
def	mysqltest	t11_ibfk_5	def	test	PRIMARY	NONE	NO ACTION	NO ACTION	t11	t1
def	mysqltest	t11_ibfk_6	def	test	PRIMARY	NONE	NO ACTION	NO ACTION	t11	t1
ALTER TABLE mysqltest.t11 ADD FOREIGN KEY (m) REFERENCES test.t1 (pk),
ADD CONSTRAINT t12_ibfk_8 FOREIGN KEY (n) REFERENCES test.t1 (pk),
RENAME TO t12, ALGORITHM=INPLACE;
SHOW CREATE TABLE t12;
Table	Create Table
t12	CREATE TABLE `t12` (
  `fk1` int(11) DEFAULT NULL,
  `fk2` int(11) DEFAULT NULL,
  `fk3` int(11) DEFAULT NULL,
  `i` int(11) DEFAULT NULL,
  `j` int(11) DEFAULT NULL,
  `k` int(11) DEFAULT NULL,
  `l` int(11) DEFAULT NULL,
  `m` int(11) DEFAULT NULL,
  `n` int(11) DEFAULT NULL,
  KEY `a` (`fk1`),
  KEY `t2_ibfk_1` (`fk2`),
  KEY `fk3` (`fk3`),
  KEY `i` (`i`),
  KEY `t10_ibfk_4` (`j`),
  KEY `k` (`k`),
  KEY `t11_ibfk_6` (`l`),
  KEY `m` (`m`),
  KEY `t12_ibfk_8` (`n`),
  CONSTRAINT `a` FOREIGN KEY (`fk1`) REFERENCES `t1` (`pk`),
  CONSTRAINT `t12_ibfk_1` FOREIGN KEY (`fk2`) REFERENCES `t1` (`pk`),
  CONSTRAINT `t12_ibfk_2` FOREIGN KEY (`fk3`) REFERENCES `t1` (`pk`),
  CONSTRAINT `t12_ibfk_3` FOREIGN KEY (`i`) REFERENCES `t1` (`pk`),
  CONSTRAINT `t12_ibfk_4` FOREIGN KEY (`j`) REFERENCES `t1` (`pk`),
  CONSTRAINT `t12_ibfk_5` FOREIGN KEY (`k`) REFERENCES `t1` (`pk`),
  CONSTRAINT `t12_ibfk_6` FOREIGN KEY (`l`) REFERENCES `t1` (`pk`),
  CONSTRAINT `t12_ibfk_7` FOREIGN KEY (`m`) REFERENCES `t1` (`pk`),
  CONSTRAINT `t12_ibfk_8` FOREIGN KEY (`n`) REFERENCES `t1` (`pk`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
SELECT * FROM information_schema.referential_constraints WHERE constraint_schema='test';
CONSTRAINT_CATALOG	CONSTRAINT_SCHEMA	CONSTRAINT_NAME	UNIQUE_CONSTRAINT_CATALOG	UNIQUE_CONSTRAINT_SCHEMA	UNIQUE_CONSTRAINT_NAME	MATCH_OPTION	UPDATE_RULE	DELETE_RULE	TABLE_NAME	REFERENCED_TABLE_NAME
def	test	a	def	test	PRIMARY	NONE	NO ACTION	NO ACTION	t12	t1
def	test	t12_ibfk_1	def	test	PRIMARY	NONE	NO ACTION	NO ACTION	t12	t1
def	test	t12_ibfk_2	def	test	PRIMARY	NONE	NO ACTION	NO ACTION	t12	t1
def	test	t12_ibfk_3	def	test	PRIMARY	NONE	NO ACTION	NO ACTION	t12	t1
def	test	t12_ibfk_4	def	test	PRIMARY	NONE	NO ACTION	NO ACTION	t12	t1
def	test	t12_ibfk_5	def	test	PRIMARY	NONE	NO ACTION	NO ACTION	t12	t1
def	test	t12_ibfk_6	def	test	PRIMARY	NONE	NO ACTION	NO ACTION	t12	t1
def	test	t12_ibfk_7	def	test	PRIMARY	NONE	NO ACTION	NO ACTION	t12	t1
def	test	t12_ibfk_8	def	test	PRIMARY	NONE	NO ACTION	NO ACTION	t12	t1
SET FOREIGN_KEY_CHECKS=1;
# ALTER TABLE COPY + ADD FOREIGN KEY with RENAME clause.
ALTER TABLE t12 ADD COLUMN o INT, ADD COLUMN p INT,
ADD FOREIGN KEY (o) REFERENCES t1 (pk),
ADD CONSTRAINT t12_ibfk_10 FOREIGN KEY (p) REFERENCES t1 (pk),
RENAME TO t13, ALGORITHM=COPY;
SHOW CREATE TABLE t13;
Table	Create Table
t13	CREATE TABLE `t13` (
  `fk1` int(11) DEFAULT NULL,
  `fk2` int(11) DEFAULT NULL,
  `fk3` int(11) DEFAULT NULL,
  `i` int(11) DEFAULT NULL,
  `j` int(11) DEFAULT NULL,
  `k` int(11) DEFAULT NULL,
  `l` int(11) DEFAULT NULL,
  `m` int(11) DEFAULT NULL,
  `n` int(11) DEFAULT NULL,
  `o` int(11) DEFAULT NULL,
  `p` int(11) DEFAULT NULL,
  KEY `a` (`fk1`),
  KEY `t2_ibfk_1` (`fk2`),
  KEY `fk3` (`fk3`),
  KEY `i` (`i`),
  KEY `t10_ibfk_4` (`j`),
  KEY `k` (`k`),
  KEY `t11_ibfk_6` (`l`),
  KEY `m` (`m`),
  KEY `t12_ibfk_8` (`n`),
  KEY `o` (`o`),
  KEY `t12_ibfk_10` (`p`),
  CONSTRAINT `a` FOREIGN KEY (`fk1`) REFERENCES `t1` (`pk`),
  CONSTRAINT `t13_ibfk_1` FOREIGN KEY (`fk2`) REFERENCES `t1` (`pk`),
  CONSTRAINT `t13_ibfk_10` FOREIGN KEY (`p`) REFERENCES `t1` (`pk`),
  CONSTRAINT `t13_ibfk_2` FOREIGN KEY (`fk3`) REFERENCES `t1` (`pk`),
  CONSTRAINT `t13_ibfk_3` FOREIGN KEY (`i`) REFERENCES `t1` (`pk`),
  CONSTRAINT `t13_ibfk_4` FOREIGN KEY (`j`) REFERENCES `t1` (`pk`),
  CONSTRAINT `t13_ibfk_5` FOREIGN KEY (`k`) REFERENCES `t1` (`pk`),
  CONSTRAINT `t13_ibfk_6` FOREIGN KEY (`l`) REFERENCES `t1` (`pk`),
  CONSTRAINT `t13_ibfk_7` FOREIGN KEY (`m`) REFERENCES `t1` (`pk`),
  CONSTRAINT `t13_ibfk_8` FOREIGN KEY (`n`) REFERENCES `t1` (`pk`),
  CONSTRAINT `t13_ibfk_9` FOREIGN KEY (`o`) REFERENCES `t1` (`pk`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
SELECT * FROM information_schema.referential_constraints WHERE constraint_schema='test';
CONSTRAINT_CATALOG	CONSTRAINT_SCHEMA	CONSTRAINT_NAME	UNIQUE_CONSTRAINT_CATALOG	UNIQUE_CONSTRAINT_SCHEMA	UNIQUE_CONSTRAINT_NAME	MATCH_OPTION	UPDATE_RULE	DELETE_RULE	TABLE_NAME	REFERENCED_TABLE_NAME
def	test	a	def	test	PRIMARY	NONE	NO ACTION	NO ACTION	t13	t1
def	test	t13_ibfk_1	def	test	PRIMARY	NONE	NO ACTION	NO ACTION	t13	t1
def	test	t13_ibfk_10	def	test	PRIMARY	NONE	NO ACTION	NO ACTION	t13	t1
def	test	t13_ibfk_2	def	test	PRIMARY	NONE	NO ACTION	NO ACTION	t13	t1
def	test	t13_ibfk_3	def	test	PRIMARY	NONE	NO ACTION	NO ACTION	t13	t1
def	test	t13_ibfk_4	def	test	PRIMARY	NONE	NO ACTION	NO ACTION	t13	t1
def	test	t13_ibfk_5	def	test	PRIMARY	NONE	NO ACTION	NO ACTION	t13	t1
def	test	t13_ibfk_6	def	test	PRIMARY	NONE	NO ACTION	NO ACTION	t13	t1
def	test	t13_ibfk_7	def	test	PRIMARY	NONE	NO ACTION	NO ACTION	t13	t1
def	test	t13_ibfk_8	def	test	PRIMARY	NONE	NO ACTION	NO ACTION	t13	t1
def	test	t13_ibfk_9	def	test	PRIMARY	NONE	NO ACTION	NO ACTION	t13	t1
ALTER TABLE t13 ADD COLUMN q INT, ADD COLUMN r INT,
ADD FOREIGN KEY (q) REFERENCES test.t1 (pk),
ADD CONSTRAINT t13_ibfk_12 FOREIGN KEY (r) REFERENCES test.t1 (pk),
RENAME TO mysqltest.t13, ALGORITHM=COPY;
SHOW CREATE TABLE mysqltest.t13;
Table	Create Table
t13	CREATE TABLE `t13` (
  `fk1` int(11) DEFAULT NULL,
  `fk2` int(11) DEFAULT NULL,
  `fk3` int(11) DEFAULT NULL,
  `i` int(11) DEFAULT NULL,
  `j` int(11) DEFAULT NULL,
  `k` int(11) DEFAULT NULL,
  `l` int(11) DEFAULT NULL,
  `m` int(11) DEFAULT NULL,
  `n` int(11) DEFAULT NULL,
  `o` int(11) DEFAULT NULL,
  `p` int(11) DEFAULT NULL,
  `q` int(11) DEFAULT NULL,
  `r` int(11) DEFAULT NULL,
  KEY `a` (`fk1`),
  KEY `t2_ibfk_1` (`fk2`),
  KEY `fk3` (`fk3`),
  KEY `i` (`i`),
  KEY `t10_ibfk_4` (`j`),
  KEY `k` (`k`),
  KEY `t11_ibfk_6` (`l`),
  KEY `m` (`m`),
  KEY `t12_ibfk_8` (`n`),
  KEY `o` (`o`),
  KEY `t12_ibfk_10` (`p`),
  KEY `q` (`q`),
  KEY `t13_ibfk_12` (`r`),
  CONSTRAINT `a` FOREIGN KEY (`fk1`) REFERENCES `test`.`t1` (`pk`),
  CONSTRAINT `t13_ibfk_1` FOREIGN KEY (`fk2`) REFERENCES `test`.`t1` (`pk`),
  CONSTRAINT `t13_ibfk_10` FOREIGN KEY (`p`) REFERENCES `test`.`t1` (`pk`),
  CONSTRAINT `t13_ibfk_11` FOREIGN KEY (`q`) REFERENCES `test`.`t1` (`pk`),
  CONSTRAINT `t13_ibfk_12` FOREIGN KEY (`r`) REFERENCES `test`.`t1` (`pk`),
  CONSTRAINT `t13_ibfk_2` FOREIGN KEY (`fk3`) REFERENCES `test`.`t1` (`pk`),
  CONSTRAINT `t13_ibfk_3` FOREIGN KEY (`i`) REFERENCES `test`.`t1` (`pk`),
  CONSTRAINT `t13_ibfk_4` FOREIGN KEY (`j`) REFERENCES `test`.`t1` (`pk`),
  CONSTRAINT `t13_ibfk_5` FOREIGN KEY (`k`) REFERENCES `test`.`t1` (`pk`),
  CONSTRAINT `t13_ibfk_6` FOREIGN KEY (`l`) REFERENCES `test`.`t1` (`pk`),
  CONSTRAINT `t13_ibfk_7` FOREIGN KEY (`m`) REFERENCES `test`.`t1` (`pk`),
  CONSTRAINT `t13_ibfk_8` FOREIGN KEY (`n`) REFERENCES `test`.`t1` (`pk`),
  CONSTRAINT `t13_ibfk_9` FOREIGN KEY (`o`) REFERENCES `test`.`t1` (`pk`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
SELECT * FROM information_schema.referential_constraints WHERE constraint_schema='mysqltest';
CONSTRAINT_CATALOG	CONSTRAINT_SCHEMA	CONSTRAINT_NAME	UNIQUE_CONSTRAINT_CATALOG	UNIQUE_CONSTRAINT_SCHEMA	UNIQUE_CONSTRAINT_NAME	MATCH_OPTION	UPDATE_RULE	DELETE_RULE	TABLE_NAME	REFERENCED_TABLE_NAME
def	mysqltest	a	def	test	PRIMARY	NONE	NO ACTION	NO ACTION	t13	t1
def	mysqltest	t13_ibfk_1	def	test	PRIMARY	NONE	NO ACTION	NO ACTION	t13	t1
def	mysqltest	t13_ibfk_10	def	test	PRIMARY	NONE	NO ACTION	NO ACTION	t13	t1
def	mysqltest	t13_ibfk_11	def	test	PRIMARY	NONE	NO ACTION	NO ACTION	t13	t1
def	mysqltest	t13_ibfk_12	def	test	PRIMARY	NONE	NO ACTION	NO ACTION	t13	t1
def	mysqltest	t13_ibfk_2	def	test	PRIMARY	NONE	NO ACTION	NO ACTION	t13	t1
def	mysqltest	t13_ibfk_3	def	test	PRIMARY	NONE	NO ACTION	NO ACTION	t13	t1
def	mysqltest	t13_ibfk_4	def	test	PRIMARY	NONE	NO ACTION	NO ACTION	t13	t1
def	mysqltest	t13_ibfk_5	def	test	PRIMARY	NONE	NO ACTION	NO ACTION	t13	t1
def	mysqltest	t13_ibfk_6	def	test	PRIMARY	NONE	NO ACTION	NO ACTION	t13	t1
def	mysqltest	t13_ibfk_7	def	test	PRIMARY	NONE	NO ACTION	NO ACTION	t13	t1
def	mysqltest	t13_ibfk_8	def	test	PRIMARY	NONE	NO ACTION	NO ACTION	t13	t1
def	mysqltest	t13_ibfk_9	def	test	PRIMARY	NONE	NO ACTION	NO ACTION	t13	t1
ALTER TABLE mysqltest.t13 ADD COLUMN s INT, ADD COLUMN t INT,
ADD FOREIGN KEY (s) REFERENCES test.t1 (pk),
ADD CONSTRAINT t13_ibfk_14 FOREIGN KEY (t) REFERENCES test.t1 (pk),
RENAME TO t14, ALGORITHM=COPY;
SHOW CREATE TABLE t14;
Table	Create Table
t14	CREATE TABLE `t14` (
  `fk1` int(11) DEFAULT NULL,
  `fk2` int(11) DEFAULT NULL,
  `fk3` int(11) DEFAULT NULL,
  `i` int(11) DEFAULT NULL,
  `j` int(11) DEFAULT NULL,
  `k` int(11) DEFAULT NULL,
  `l` int(11) DEFAULT NULL,
  `m` int(11) DEFAULT NULL,
  `n` int(11) DEFAULT NULL,
  `o` int(11) DEFAULT NULL,
  `p` int(11) DEFAULT NULL,
  `q` int(11) DEFAULT NULL,
  `r` int(11) DEFAULT NULL,
  `s` int(11) DEFAULT NULL,
  `t` int(11) DEFAULT NULL,
  KEY `a` (`fk1`),
  KEY `t2_ibfk_1` (`fk2`),
  KEY `fk3` (`fk3`),
  KEY `i` (`i`),
  KEY `t10_ibfk_4` (`j`),
  KEY `k` (`k`),
  KEY `t11_ibfk_6` (`l`),
  KEY `m` (`m`),
  KEY `t12_ibfk_8` (`n`),
  KEY `o` (`o`),
  KEY `t12_ibfk_10` (`p`),
  KEY `q` (`q`),
  KEY `t13_ibfk_12` (`r`),
  KEY `s` (`s`),
  KEY `t13_ibfk_14` (`t`),
  CONSTRAINT `a` FOREIGN KEY (`fk1`) REFERENCES `t1` (`pk`),
  CONSTRAINT `t14_ibfk_1` FOREIGN KEY (`fk2`) REFERENCES `t1` (`pk`),
  CONSTRAINT `t14_ibfk_10` FOREIGN KEY (`p`) REFERENCES `t1` (`pk`),
  CONSTRAINT `t14_ibfk_11` FOREIGN KEY (`q`) REFERENCES `t1` (`pk`),
  CONSTRAINT `t14_ibfk_12` FOREIGN KEY (`r`) REFERENCES `t1` (`pk`),
  CONSTRAINT `t14_ibfk_13` FOREIGN KEY (`s`) REFERENCES `t1` (`pk`),
  CONSTRAINT `t14_ibfk_14` FOREIGN KEY (`t`) REFERENCES `t1` (`pk`),
  CONSTRAINT `t14_ibfk_2` FOREIGN KEY (`fk3`) REFERENCES `t1` (`pk`),
  CONSTRAINT `t14_ibfk_3` FOREIGN KEY (`i`) REFERENCES `t1` (`pk`),
  CONSTRAINT `t14_ibfk_4` FOREIGN KEY (`j`) REFERENCES `t1` (`pk`),
  CONSTRAINT `t14_ibfk_5` FOREIGN KEY (`k`) REFERENCES `t1` (`pk`),
  CONSTRAINT `t14_ibfk_6` FOREIGN KEY (`l`) REFERENCES `t1` (`pk`),
  CONSTRAINT `t14_ibfk_7` FOREIGN KEY (`m`) REFERENCES `t1` (`pk`),
  CONSTRAINT `t14_ibfk_8` FOREIGN KEY (`n`) REFERENCES `t1` (`pk`),
  CONSTRAINT `t14_ibfk_9` FOREIGN KEY (`o`) REFERENCES `t1` (`pk`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
SELECT * FROM information_schema.referential_constraints WHERE constraint_schema='test';
CONSTRAINT_CATALOG	CONSTRAINT_SCHEMA	CONSTRAINT_NAME	UNIQUE_CONSTRAINT_CATALOG	UNIQUE_CONSTRAINT_SCHEMA	UNIQUE_CONSTRAINT_NAME	MATCH_OPTION	UPDATE_RULE	DELETE_RULE	TABLE_NAME	REFERENCED_TABLE_NAME
def	test	a	def	test	PRIMARY	NONE	NO ACTION	NO ACTION	t14	t1
def	test	t14_ibfk_1	def	test	PRIMARY	NONE	NO ACTION	NO ACTION	t14	t1
def	test	t14_ibfk_10	def	test	PRIMARY	NONE	NO ACTION	NO ACTION	t14	t1
def	test	t14_ibfk_11	def	test	PRIMARY	NONE	NO ACTION	NO ACTION	t14	t1
def	test	t14_ibfk_12	def	test	PRIMARY	NONE	NO ACTION	NO ACTION	t14	t1
def	test	t14_ibfk_13	def	test	PRIMARY	NONE	NO ACTION	NO ACTION	t14	t1
def	test	t14_ibfk_14	def	test	PRIMARY	NONE	NO ACTION	NO ACTION	t14	t1
def	test	t14_ibfk_2	def	test	PRIMARY	NONE	NO ACTION	NO ACTION	t14	t1
def	test	t14_ibfk_3	def	test	PRIMARY	NONE	NO ACTION	NO ACTION	t14	t1
def	test	t14_ibfk_4	def	test	PRIMARY	NONE	NO ACTION	NO ACTION	t14	t1
def	test	t14_ibfk_5	def	test	PRIMARY	NONE	NO ACTION	NO ACTION	t14	t1
def	test	t14_ibfk_6	def	test	PRIMARY	NONE	NO ACTION	NO ACTION	t14	t1
def	test	t14_ibfk_7	def	test	PRIMARY	NONE	NO ACTION	NO ACTION	t14	t1
def	test	t14_ibfk_8	def	test	PRIMARY	NONE	NO ACTION	NO ACTION	t14	t1
def	test	t14_ibfk_9	def	test	PRIMARY	NONE	NO ACTION	NO ACTION	t14	t1
DROP TABLE t14;
#
# Detection of duplicate foreign key names by various forms of ALTER
# TABLE.
#
CREATE TABLE t2 (fk INT, CONSTRAINT c FOREIGN KEY (fk) REFERENCES t1 (pk));
CREATE TABLE t3 (pk INT PRIMARY KEY, fk INT, u INT);
INSERT INTO t3 VALUES (1, 1, 1), (2, 1, 1);
#
# ALTER TABLE INPLACE
#
SET FOREIGN_KEY_CHECKS=0;
# Duplicate FK name should be detected before we start addition
# of unique key and report its violation.
ALTER TABLE t3 ADD CONSTRAINT c FOREIGN KEY (fk) REFERENCES t1 (pk),
ADD UNIQUE KEY (u), ALGORITHM=INPLACE;
ERROR HY000: Duplicate foreign key constraint name 'c'
# Even for generated names.
CREATE TABLE t4 (fk INT, CONSTRAINT t3_ibfk_1 FOREIGN KEY (fk) REFERENCES t1 (pk));
ALTER TABLE t3 ADD FOREIGN KEY (fk) REFERENCES t1 (pk),
ADD UNIQUE KEY (u), ALGORITHM=INPLACE;
ERROR HY000: Duplicate foreign key constraint name 't3_ibfk_1'
# There should not be conflicting foreign keys before main phase
# of ALTER INPLACE even if at the end RENAME clause will remove
# conflict.
ALTER TABLE t3 ADD CONSTRAINT c FOREIGN KEY (fk) REFERENCES t1 (pk),
ADD UNIQUE KEY (u), RENAME TO mysqltest.t3,
ALGORITHM=INPLACE;
ERROR HY000: Duplicate foreign key constraint name 'c'
ALTER TABLE t3 ADD FOREIGN KEY (fk) REFERENCES t1 (pk),
ADD UNIQUE KEY (u), RENAME TO t5,
ALGORITHM=INPLACE;
ERROR HY000: Duplicate foreign key constraint name 't3_ibfk_1'
# Now let us check that we detect potential conflicts resulting
# from application of RENAME clause, before ALTER INPLACE main
# phase as well.
CREATE TABLE mysqltest.t5 (fk INT,
CONSTRAINT d FOREIGN KEY (fk) REFERENCES test.t1 (pk));
CREATE TABLE t6 (fk INT, CONSTRAINT t8_ibfk_1 FOREIGN KEY (fk) REFERENCES test.t1 (pk));
CREATE TABLE mysqltest.t6 (fk INT,
CONSTRAINT t8_ibfk_1 FOREIGN KEY (fk) REFERENCES test.t1 (pk));
DROP TABLE t4;
ALTER TABLE t3 ADD CONSTRAINT d FOREIGN KEY (fk) REFERENCES t1 (pk),
ADD UNIQUE KEY (u), RENAME TO mysqltest.t3,
ALGORITHM=INPLACE;
ERROR HY000: Duplicate foreign key constraint name 'd'
ALTER TABLE t3 ADD FOREIGN KEY (fk) REFERENCES t1 (pk),
ADD UNIQUE KEY (u), RENAME TO t8,
ALGORITHM=INPLACE;
ERROR HY000: Duplicate foreign key constraint name 't8_ibfk_1'
ALTER TABLE t3 ADD FOREIGN KEY (fk) REFERENCES t1 (pk),
ADD UNIQUE KEY (u), RENAME TO mysqltest.t8,
ALGORITHM=INPLACE;
ERROR HY000: Duplicate foreign key constraint name 't8_ibfk_1'
SET FOREIGN_KEY_CHECKS=1;
#
# ALTER TABLE COPY
#
# Duplicate FK name should be detected before we start copying
# of rows and report unique/FK constraint violation.
ALTER TABLE t3 ADD CONSTRAINT c FOREIGN KEY (fk) REFERENCES t1 (pk),
ADD UNIQUE KEY (u), ALGORITHM=COPY;
ERROR HY000: Duplicate foreign key constraint name 'c'
# Even for generated names.
CREATE TABLE t4 (fk INT, CONSTRAINT t3_ibfk_1 FOREIGN KEY (fk) REFERENCES t1 (pk));
ALTER TABLE t3 ADD FOREIGN KEY (fk) REFERENCES t1 (pk),
ADD UNIQUE KEY (u), ALGORITHM=COPY;
ERROR HY000: Duplicate foreign key constraint name 't3_ibfk_1'
# Unlike in INPLACE case, COPY algorithm creates FKs right
# away in schema targeted by RENAME clause. So it doesn't
# matter if constraint with the same name exists in the
# table's original schema.
SET FOREIGN_KEY_CHECKS=0;
ALTER TABLE t3 ADD CONSTRAINT c FOREIGN KEY (fk) REFERENCES t1 (pk),
ADD UNIQUE KEY (u), RENAME TO mysqltest.t3,
ALGORITHM=COPY;
ERROR 23000: Duplicate entry '1' for key 'u'
SET FOREIGN_KEY_CHECKS=1;
# Updating of generated names still happens at the end of ALTER,
# so there should not be conflicting foreign keys for generated
# names for both old and new table name.
ALTER TABLE t3 ADD FOREIGN KEY (fk) REFERENCES t1 (pk),
ADD UNIQUE KEY (u), RENAME TO t5,
ALGORITHM=COPY;
ERROR HY000: Duplicate foreign key constraint name 't3_ibfk_1'
# Check that we detect potential conflicts resulting from
# application of RENAME clause early.
DROP TABLE t4;
ALTER TABLE t3 ADD CONSTRAINT d FOREIGN KEY (fk) REFERENCES test.t1 (pk),
ADD UNIQUE KEY (u), RENAME TO mysqltest.t3,
ALGORITHM=COPY;
ERROR HY000: Duplicate foreign key constraint name 'd'
ALTER TABLE t3 ADD FOREIGN KEY (fk) REFERENCES test.t1 (pk),
ADD UNIQUE KEY (u), RENAME TO t8,
ALGORITHM=COPY;
ERROR HY000: Duplicate foreign key constraint name 't8_ibfk_1'
ALTER TABLE t3 ADD FOREIGN KEY (fk) REFERENCES test.t1 (pk),
ADD UNIQUE KEY (u), RENAME TO mysqltest.t8,
ALGORITHM=COPY;
ERROR HY000: Duplicate foreign key constraint name 't8_ibfk_1'
DROP SCHEMA mysqltest;
DROP TABLES t6, t3, t2, t1;
#
# Initial version of the fix triggered asserts in the below statements.
#
CREATE TABLE t1 (pk INT PRIMARY KEY);
CREATE TABLE T2 (fk INT);
ALTER TABLE T2 ADD FOREIGN KEY (fk) REFERENCES t1 (pk);
RENAME TABLE T2 TO T3;
DROP TABLES T3, t1;
#
# Bug #27821060 "NEWDD FK: DROP TABLES/DATABASE SHOULD CHECK FOR FKS".
#
#
# 1) Attempt to DROP TABLE which serves as parent in FK without dropping
#    child should fail with nice error message.
CREATE TABLE t1 (pk INT PRIMARY KEY);
CREATE TABLE t2 (fk INT, FOREIGN KEY (fk) REFERENCES t1 (pk));
DROP TABLE t1;
ERROR HY000: Cannot drop table 't1' referenced by a foreign key constraint 't2_ibfk_1' on table 't2'.
#
# 2) However, same should be allowed in FOREIGN_KEY_CHECKS=0 mode.
SET FOREIGN_KEY_CHECKS=0;
DROP TABLE t1;
SET FOREIGN_KEY_CHECKS=1;
#
# 3) Dropping of parent table along with child table should be allowed.
#    Even if parent precedes child in table list.
CREATE TABLE t1 (pk INT PRIMARY KEY);
DROP TABLES t1, t2;
#
# 4) Even if FKs form circular dependencies.
CREATE TABLE t1 (pk INT PRIMARY KEY, fk INT);
CREATE TABLE t2 (pk INT PRIMARY KEY, fk INT,
FOREIGN KEY(fk) REFERENCES t1 (pk));
ALTER TABLE t1 ADD FOREIGN KEY (fk) REFERENCES t2 (pk);
DROP TABLES t1, t2;
#
# 5) Attempt to DROP SCHEMA which will remove parent without
#    removing child should fail with nice error message.
CREATE SCHEMA mysqltest;
CREATE TABLE mysqltest.t1 (pk INT PRIMARY KEY);
CREATE TABLE t2 (fk INT, FOREIGN KEY(fk) REFERENCES mysqltest.t1 (pk));
DROP SCHEMA mysqltest;
ERROR HY000: Cannot drop table 't1' referenced by a foreign key constraint 't2_ibfk_1' on table 't2'.
#
# 6) But the same should be allowed in FOREIGN_KEY_CHECKS=0 mode.
SET FOREIGN_KEY_CHECKS=0;
DROP SCHEMA mysqltest;
SET FOREIGN_KEY_CHECKS=1;
DROP TABLE t2;
#
# 7) Also dropping schema which drops both parent and child
#    should be OK.
CREATE SCHEMA mysqltest;
USE mysqltest;
CREATE TABLE t1 (pk INT PRIMARY KEY, fk INT);
CREATE TABLE t2 (pk INT PRIMARY KEY, fk INT,
FOREIGN KEY(fk) REFERENCES t1 (pk));
ALTER TABLE t1 ADD FOREIGN KEY (fk) REFERENCES t2 (pk);
USE test;
DROP SCHEMA mysqltest;
#
# Bug#27506922 "DROPPING OF PARENT KEY FOR FOREIGN KEY IS ALLOWED".
#
#
# 1) CREATE TABLE with FK and no parent key.
#
CREATE TABLE parent(pk INT PRIMARY KEY, a INT);
CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES parent(a));
ERROR HY000: Failed to add the foreign key constraint. Missing index for constraint 'child_ibfk_1' in the referenced table 'parent'
SET FOREIGN_KEY_CHECKS = 0;
CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES parent(a));
ERROR HY000: Failed to add the foreign key constraint. Missing index for constraint 'child_ibfk_1' in the referenced table 'parent'
SET FOREIGN_KEY_CHECKS = 1;
CREATE TABLE self (pk INT PRIMARY KEY, a INT, fk INT,
FOREIGN KEY (fk) REFERENCES self(a));
ERROR HY000: Failed to add the foreign key constraint. Missing index for constraint 'self_ibfk_1' in the referenced table 'self'
SET FOREIGN_KEY_CHECKS = 0;
CREATE TABLE self (pk INT PRIMARY KEY, a INT, fk INT,
FOREIGN KEY (fk) REFERENCES self(a));
ERROR HY000: Failed to add the foreign key constraint. Missing index for constraint 'self_ibfk_1' in the referenced table 'self'
# Missing parent table case.
SET FOREIGN_KEY_CHECKS = 1;
CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES no_such_parent(pk));
ERROR HY000: Failed to open the referenced table 'no_such_parent'
# We allow creation of orphan FKs in FOREIGN_KEY_CHECKS = 0 mode.
SET FOREIGN_KEY_CHECKS = 0;
CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES no_such_parent(pk));
SET FOREIGN_KEY_CHECKS = 1;
DROP TABLE child;
#
# 2) ALTER TABLE which adds FK without parent key.
#
CREATE TABLE child (fk INT, fk2 INT);
ALTER TABLE child ADD FOREIGN KEY (fk) REFERENCES parent(a), ALGORITHM=COPY;
ERROR HY000: Failed to add the foreign key constraint. Missing index for constraint 'child_ibfk_1' in the referenced table 'parent'
ALTER TABLE child ADD FOREIGN KEY (fk) REFERENCES parent(a), ALGORITHM=INPLACE;
ERROR HY000: Failed to add the foreign key constraint. Missing index for constraint 'child_ibfk_1' in the referenced table 'parent'
SET FOREIGN_KEY_CHECKS = 0;
ALTER TABLE child ADD FOREIGN KEY (fk) REFERENCES parent(a), ALGORITHM=COPY;
ERROR HY000: Failed to add the foreign key constraint. Missing index for constraint 'child_ibfk_1' in the referenced table 'parent'
ALTER TABLE child ADD FOREIGN KEY (fk) REFERENCES parent(a), ALGORITHM=INPLACE;
ERROR HY000: Failed to add the foreign key constraint. Missing index for constraint 'child_ibfk_1' in the referenced table 'parent'
SET FOREIGN_KEY_CHECKS = 1;
CREATE TABLE self (pk INT PRIMARY KEY, a INT, fk INT);
ALTER TABLE self ADD FOREIGN KEY (fk) REFERENCES self(a), ALGORITHM=COPY;
ERROR HY000: Failed to add the foreign key constraint. Missing index for constraint 'self_ibfk_1' in the referenced table 'self'
ALTER TABLE self ADD FOREIGN KEY (fk) REFERENCES self(a), ALGORITHM=INPLACE;
ERROR HY000: Failed to add the foreign key constraint. Missing index for constraint 'self_ibfk_1' in the referenced table 'self'
SET FOREIGN_KEY_CHECKS = 0;
ALTER TABLE self ADD FOREIGN KEY (fk) REFERENCES self(a), ALGORITHM=COPY;
ERROR HY000: Failed to add the foreign key constraint. Missing index for constraint 'self_ibfk_1' in the referenced table 'self'
ALTER TABLE self ADD FOREIGN KEY (fk) REFERENCES self(a), ALGORITHM=INPLACE;
ERROR HY000: Failed to add the foreign key constraint. Missing index for constraint 'self_ibfk_1' in the referenced table 'self'
# Missing parent table case.
SET FOREIGN_KEY_CHECKS = 1;
ALTER TABLE child ADD FOREIGN KEY (fk) REFERENCES no_such_parent(pk), ALGORITHM=COPY;
ERROR HY000: Failed to open the referenced table 'no_such_parent'
ALTER TABLE child ADD FOREIGN KEY (fk) REFERENCES no_such_parent(pk), ALGORITHM=INPLACE;
ERROR HY000: Failed to open the referenced table 'no_such_parent'
# We allow creation of orphan FKs in FOREIGN_KEY_CHECKS = 0 mode.
SET FOREIGN_KEY_CHECKS = 0;
ALTER TABLE child ADD FOREIGN KEY (fk) REFERENCES no_such_parent(pk), ALGORITHM=COPY;
ALTER TABLE child ADD FOREIGN KEY (fk2) REFERENCES no_such_parent(pk), ALGORITHM=INPLACE;
SET FOREIGN_KEY_CHECKS = 1;
DROP TABLE child, self, parent;
#
# 3) ALTER TABLE which drops parent key.
#
CREATE TABLE parent (pk INT PRIMARY KEY, u INT NOT NULL, UNIQUE(u));
CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES parent(u));
ALTER TABLE parent DROP KEY u, ALGORITHM=COPY;
ERROR HY000: Cannot drop index 'u': needed in a foreign key constraint
ALTER TABLE parent DROP KEY u, ALGORITHM=INPLACE;
ERROR HY000: Cannot drop index 'u': needed in a foreign key constraint
SET FOREIGN_KEY_CHECKS = 0;
ALTER TABLE parent DROP KEY u, ALGORITHM=COPY;
ERROR HY000: Cannot drop index 'u': needed in a foreign key constraint
ALTER TABLE parent DROP KEY u, ALGORITHM=INPLACE;
ERROR HY000: Cannot drop index 'u': needed in a foreign key constraint
SET FOREIGN_KEY_CHECKS = 1;
CREATE TABLE self (pk INT PRIMARY KEY, u INT NOT NULL, fk INT, UNIQUE(u),
FOREIGN KEY (fk) REFERENCES self(u));
ALTER TABLE self DROP KEY u, ALGORITHM=COPY;
ERROR HY000: Cannot drop index 'u': needed in a foreign key constraint
ALTER TABLE self DROP KEY u, ALGORITHM=INPLACE;
ERROR HY000: Cannot drop index 'u': needed in a foreign key constraint
SET FOREIGN_KEY_CHECKS = 0;
ALTER TABLE self DROP KEY u, ALGORITHM=COPY;
ERROR HY000: Cannot drop index 'u': needed in a foreign key constraint
ALTER TABLE self DROP KEY u, ALGORITHM=INPLACE;
ERROR HY000: Cannot drop index 'u': needed in a foreign key constraint
SET FOREIGN_KEY_CHECKS = 1;
# Check case which requires additional handling during error-reporting.
# Attempt to drop non-unique parent key.
ALTER TABLE parent DROP KEY u, ADD KEY nu(u);
ALTER TABLE parent DROP KEY nu, ALGORITHM=COPY;
ERROR HY000: Cannot drop index 'nu': needed in a foreign key constraint
ALTER TABLE parent DROP KEY nu, ALGORITHM=INPLACE;
ERROR HY000: Cannot drop index 'nu': needed in a foreign key constraint
SET FOREIGN_KEY_CHECKS = 0;
ALTER TABLE parent DROP KEY nu, ALGORITHM=COPY;
ERROR HY000: Cannot drop index 'nu': needed in a foreign key constraint
ALTER TABLE parent DROP KEY nu, ALGORITHM=INPLACE;
ERROR HY000: Cannot drop index 'nu': needed in a foreign key constraint
SET FOREIGN_KEY_CHECKS = 1;
ALTER TABLE self DROP KEY u, ADD KEY nu(u);
ALTER TABLE self DROP KEY nu, ALGORITHM=COPY;
ERROR HY000: Cannot drop index 'nu': needed in a foreign key constraint
ALTER TABLE self DROP KEY nu, ALGORITHM=INPLACE;
ERROR HY000: Cannot drop index 'nu': needed in a foreign key constraint
SET FOREIGN_KEY_CHECKS = 0;
ALTER TABLE self DROP KEY nu, ALGORITHM=COPY;
ERROR HY000: Cannot drop index 'nu': needed in a foreign key constraint
ALTER TABLE self DROP KEY nu, ALGORITHM=INPLACE;
ERROR HY000: Cannot drop index 'nu': needed in a foreign key constraint
SET FOREIGN_KEY_CHECKS = 1;
DROP TABLES self, child, parent;
#
# 4) CREATE, RENAME and ALTER TABLE RENAME which create new
#    parent for previously orphan child table.
#
SET FOREIGN_KEY_CHECKS = 0;
CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES parent(a));
SET FOREIGN_KEY_CHECKS = 1;
CREATE TABLE parent (pk INT PRIMARY KEY, a INT);
ERROR HY000: Cannot add foreign key constraint
SET FOREIGN_KEY_CHECKS = 0;
CREATE TABLE parent (pk INT PRIMARY KEY, a INT);
ERROR HY000: Failed to add the foreign key constraint. Missing index for constraint 'child_ibfk_1' in the referenced table 'parent'
SET FOREIGN_KEY_CHECKS = 1;
CREATE TABLE parent1 (pk INT PRIMARY KEY, a INT);
RENAME TABLE parent1 TO parent;
ERROR HY000: Error on rename of './test/parent1' to './test/parent' (errno: 150 - Foreign key constraint is incorrectly formed)
SET FOREIGN_KEY_CHECKS = 0;
RENAME TABLE parent1 TO parent;
ERROR HY000: Failed to add the foreign key constraint. Missing index for constraint 'child_ibfk_1' in the referenced table 'parent'
SET FOREIGN_KEY_CHECKS = 1;
ALTER TABLE parent1 RENAME TO parent;
ERROR HY000: Failed to add the foreign key constraint. Missing index for constraint 'child_ibfk_1' in the referenced table 'parent'
SET FOREIGN_KEY_CHECKS = 0;
ALTER TABLE parent1 RENAME TO parent;
ERROR HY000: Failed to add the foreign key constraint. Missing index for constraint 'child_ibfk_1' in the referenced table 'parent'
SET FOREIGN_KEY_CHECKS = 1;
ALTER TABLE parent1 ADD COLUMN b INT, RENAME TO parent, ALGORITHM=INPLACE;
ERROR HY000: Failed to add the foreign key constraint. Missing index for constraint 'child_ibfk_1' in the referenced table 'parent'
SET FOREIGN_KEY_CHECKS = 0;
ALTER TABLE parent1 RENAME TO parent;
ERROR HY000: Failed to add the foreign key constraint. Missing index for constraint 'child_ibfk_1' in the referenced table 'parent'
SET FOREIGN_KEY_CHECKS = 1;
ALTER TABLE parent1 ADD COLUMN b INT, RENAME TO parent, ALGORITHM=COPY;
ERROR HY000: Failed to add the foreign key constraint. Missing index for constraint 'child_ibfk_1' in the referenced table 'parent'
SET FOREIGN_KEY_CHECKS = 0;
ALTER TABLE parent1 RENAME TO parent;
ERROR HY000: Failed to add the foreign key constraint. Missing index for constraint 'child_ibfk_1' in the referenced table 'parent'
SET FOREIGN_KEY_CHECKS = 1;
DROP TABLE parent1, child;
#
# 5) Special case. ALTER TABLE which replaces parent key with another
#    one due to new key creation. Old key is generated so it is
#    automagically replaced with a new explicit key or more universal
#    generated key.
#
CREATE TABLE grandparent (pk INT PRIMARY KEY);
CREATE TABLE parent (pkfk INT, FOREIGN KEY (pkfk) REFERENCES grandparent(pk));
SHOW CREATE TABLE parent;
Table	Create Table
parent	CREATE TABLE `parent` (
  `pkfk` int(11) DEFAULT NULL,
  KEY `pkfk` (`pkfk`),
  CONSTRAINT `parent_ibfk_1` FOREIGN KEY (`pkfk`) REFERENCES `grandparent` (`pk`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES parent(pkfk));
SELECT referenced_table_name, unique_constraint_name FROM
information_schema.referential_constraints WHERE table_name = 'child';
REFERENCED_TABLE_NAME	UNIQUE_CONSTRAINT_NAME
parent	NULL
ALTER TABLE parent ADD UNIQUE KEY u (pkfk);
SHOW CREATE TABLE parent;
Table	Create Table
parent	CREATE TABLE `parent` (
  `pkfk` int(11) DEFAULT NULL,
  UNIQUE KEY `u` (`pkfk`),
  CONSTRAINT `parent_ibfk_1` FOREIGN KEY (`pkfk`) REFERENCES `grandparent` (`pk`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
SELECT referenced_table_name, unique_constraint_name FROM
information_schema.referential_constraints WHERE table_name = 'child';
REFERENCED_TABLE_NAME	UNIQUE_CONSTRAINT_NAME
parent	u
DROP TABLE child, parent;
CREATE TABLE self (fk INT, pkfk INT,
FOREIGN KEY (fk) REFERENCES self (pkfk),
FOREIGN KEY (pkfk) REFERENCES grandparent(pk));
SHOW CREATE TABLE self;
Table	Create Table
self	CREATE TABLE `self` (
  `fk` int(11) DEFAULT NULL,
  `pkfk` int(11) DEFAULT NULL,
  KEY `fk` (`fk`),
  KEY `pkfk` (`pkfk`),
  CONSTRAINT `self_ibfk_1` FOREIGN KEY (`fk`) REFERENCES `self` (`pkfk`),
  CONSTRAINT `self_ibfk_2` FOREIGN KEY (`pkfk`) REFERENCES `grandparent` (`pk`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
SELECT referenced_table_name, unique_constraint_name FROM
information_schema.referential_constraints WHERE table_name = 'self'
  ORDER BY referenced_table_name;
REFERENCED_TABLE_NAME	UNIQUE_CONSTRAINT_NAME
grandparent	PRIMARY
self	NULL
ALTER TABLE self ADD UNIQUE KEY u (pkfk);
SHOW CREATE TABLE self;
Table	Create Table
self	CREATE TABLE `self` (
  `fk` int(11) DEFAULT NULL,
  `pkfk` int(11) DEFAULT NULL,
  UNIQUE KEY `u` (`pkfk`),
  KEY `fk` (`fk`),
  CONSTRAINT `self_ibfk_1` FOREIGN KEY (`fk`) REFERENCES `self` (`pkfk`),
  CONSTRAINT `self_ibfk_2` FOREIGN KEY (`pkfk`) REFERENCES `grandparent` (`pk`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
SELECT referenced_table_name, unique_constraint_name FROM
information_schema.referential_constraints WHERE table_name = 'self'
  ORDER BY referenced_table_name;
REFERENCED_TABLE_NAME	UNIQUE_CONSTRAINT_NAME
grandparent	PRIMARY
self	u
DROP TABLE self, grandparent;
CREATE TABLE grandparent1 (pk INT PRIMARY KEY);
CREATE TABLE grandparent2 (pk1 INT , pk2 INT, PRIMARY KEY(pk1, pk2));
CREATE TABLE parent (pkfk1 INT, pkfk2 INT, FOREIGN KEY (pkfk1) REFERENCES grandparent1(pk));
SHOW CREATE TABLE parent;
Table	Create Table
parent	CREATE TABLE `parent` (
  `pkfk1` int(11) DEFAULT NULL,
  `pkfk2` int(11) DEFAULT NULL,
  KEY `pkfk1` (`pkfk1`),
  CONSTRAINT `parent_ibfk_1` FOREIGN KEY (`pkfk1`) REFERENCES `grandparent1` (`pk`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES parent(pkfk1));
SELECT referenced_table_name, unique_constraint_name FROM
information_schema.referential_constraints WHERE table_name = 'child';
REFERENCED_TABLE_NAME	UNIQUE_CONSTRAINT_NAME
parent	NULL
ALTER TABLE parent ADD FOREIGN KEY (pkfk1, pkfk2) REFERENCES grandparent2(pk1, pk2);
SHOW CREATE TABLE parent;
Table	Create Table
parent	CREATE TABLE `parent` (
  `pkfk1` int(11) DEFAULT NULL,
  `pkfk2` int(11) DEFAULT NULL,
  KEY `pkfk1` (`pkfk1`,`pkfk2`),
  CONSTRAINT `parent_ibfk_1` FOREIGN KEY (`pkfk1`) REFERENCES `grandparent1` (`pk`),
  CONSTRAINT `parent_ibfk_2` FOREIGN KEY (`pkfk1`, `pkfk2`) REFERENCES `grandparent2` (`pk1`, `pk2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
SELECT referenced_table_name, unique_constraint_name FROM
information_schema.referential_constraints WHERE table_name = 'child';
REFERENCED_TABLE_NAME	UNIQUE_CONSTRAINT_NAME
parent	NULL
DROP TABLE child, parent;
CREATE TABLE self (fk INT, pkfk1 INT, pkfk2 INT,
FOREIGN KEY (fk) REFERENCES self (pkfk1),
FOREIGN KEY (pkfk1) REFERENCES grandparent1(pk));
SHOW CREATE TABLE self;
Table	Create Table
self	CREATE TABLE `self` (
  `fk` int(11) DEFAULT NULL,
  `pkfk1` int(11) DEFAULT NULL,
  `pkfk2` int(11) DEFAULT NULL,
  KEY `fk` (`fk`),
  KEY `pkfk1` (`pkfk1`),
  CONSTRAINT `self_ibfk_1` FOREIGN KEY (`fk`) REFERENCES `self` (`pkfk1`),
  CONSTRAINT `self_ibfk_2` FOREIGN KEY (`pkfk1`) REFERENCES `grandparent1` (`pk`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
SELECT referenced_table_name, unique_constraint_name FROM
information_schema.referential_constraints WHERE table_name = 'self'
  ORDER BY referenced_table_name;
REFERENCED_TABLE_NAME	UNIQUE_CONSTRAINT_NAME
grandparent1	PRIMARY
self	NULL
ALTER TABLE self ADD FOREIGN KEY (pkfk1, pkfk2) REFERENCES grandparent2(pk1, pk2);
SHOW CREATE TABLE self;
Table	Create Table
self	CREATE TABLE `self` (
  `fk` int(11) DEFAULT NULL,
  `pkfk1` int(11) DEFAULT NULL,
  `pkfk2` int(11) DEFAULT NULL,
  KEY `fk` (`fk`),
  KEY `pkfk1` (`pkfk1`,`pkfk2`),
  CONSTRAINT `self_ibfk_1` FOREIGN KEY (`fk`) REFERENCES `self` (`pkfk1`),
  CONSTRAINT `self_ibfk_2` FOREIGN KEY (`pkfk1`) REFERENCES `grandparent1` (`pk`),
  CONSTRAINT `self_ibfk_3` FOREIGN KEY (`pkfk1`, `pkfk2`) REFERENCES `grandparent2` (`pk1`, `pk2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
SELECT referenced_table_name, unique_constraint_name FROM
information_schema.referential_constraints WHERE table_name = 'self'
  ORDER BY referenced_table_name;
REFERENCED_TABLE_NAME	UNIQUE_CONSTRAINT_NAME
grandparent1	PRIMARY
grandparent2	PRIMARY
self	NULL
DROP TABLE self, grandparent1, grandparent2;
#
# Bug#25722927 "NEWDD FK: ALTER TABLE CHANGE COLUMN TYPE SHOULD CHECK FK CONSTRAINT"
#
#
# 1) Check how missing/dropped referencing and referenced columns
#    are handled.
#
#
# 1.a) Missing referencing column. This problem is detected
#      during generated supported index processing.
CREATE TABLE parent (pk INT PRIMARY KEY, j INT);
CREATE TABLE child (fk INT, FOREIGN KEY (nocol) REFERENCES parent(pk));
ERROR 42000: Key column 'nocol' doesn't exist in table
CREATE TABLE self (pk INT PRIMARY KEY, FOREIGN KEY (nocol) REFERENCES self(pk));
ERROR 42000: Key column 'nocol' doesn't exist in table
CREATE TABLE child (fk INT, j INT);
CREATE TABLE self (pk INT PRIMARY KEY, fk INT);
ALTER TABLE child ADD FOREIGN KEY (nocol) REFERENCES parent(pk);
ERROR 42000: Key column 'nocol' doesn't exist in table
ALTER TABLE self ADD FOREIGN KEY (nocol) REFERENCES self(pk);
ERROR 42000: Key column 'nocol' doesn't exist in table
#
# 1.b) Dropped referencing column.
ALTER TABLE child ADD FOREIGN KEY (fk) REFERENCES parent(pk);
ALTER TABLE child DROP COLUMN fk;
ERROR HY000: Cannot drop column 'fk': needed in a foreign key constraint 'child_ibfk_1'
# Adding column with the same name at the same time should not help.
ALTER TABLE child DROP COLUMN fk, ADD COLUMN fk INT;
ERROR HY000: Cannot drop column 'fk': needed in a foreign key constraint 'child_ibfk_1'
ALTER TABLE self ADD FOREIGN KEY (fk) REFERENCES self(pk);
ALTER TABLE self DROP COLUMN fk;
ERROR HY000: Cannot drop column 'fk': needed in a foreign key constraint 'self_ibfk_1'
ALTER TABLE self DROP COLUMN fk, ADD COLUMN fk INT;
ERROR HY000: Cannot drop column 'fk': needed in a foreign key constraint 'self_ibfk_1'
#
# 1.c) Missing referenced column.
DROP TABLE child;
CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES parent(nocol));
ERROR HY000: Failed to add the foreign key constraint. Missing column 'nocol' for constraint 'child_ibfk_1' in the referenced table 'parent'
DROP TABLE self;
CREATE TABLE self (pk INT PRIMARY KEY, fk INT, FOREIGN KEY (fk) REFERENCES self(nocol));
ERROR HY000: Failed to add the foreign key constraint. Missing column 'nocol' for constraint 'self_ibfk_1' in the referenced table 'self'
CREATE TABLE child (fk INT);
ALTER TABLE child ADD FOREIGN KEY (fk) REFERENCES parent(nocol);
ERROR HY000: Failed to add the foreign key constraint. Missing column 'nocol' for constraint 'child_ibfk_1' in the referenced table 'parent'
CREATE TABLE self (pk INT PRIMARY KEY, fk INT);
ALTER TABLE self ADD FOREIGN KEY (fk) REFERENCES self(nocol);
ERROR HY000: Failed to add the foreign key constraint. Missing column 'nocol' for constraint 'self_ibfk_1' in the referenced table 'self'
#
# 1.d) Dropped referenced column.
ALTER TABLE child ADD FOREIGN KEY (fk) REFERENCES parent(pk);
ALTER TABLE parent DROP COLUMN pk;
ERROR HY000: Cannot drop column 'pk': needed in a foreign key constraint 'child_ibfk_1' of table 'child'
# Adding column with the same name at the same time should not help.
ALTER TABLE parent DROP COLUMN pk, ADD COLUMN pk INT;
ERROR HY000: Cannot drop column 'pk': needed in a foreign key constraint 'child_ibfk_1' of table 'child'
ALTER TABLE self ADD FOREIGN KEY (fk) REFERENCES self(pk);
ALTER TABLE self DROP COLUMN pk;
ERROR HY000: Cannot drop column 'pk': needed in a foreign key constraint 'self_ibfk_1' of table 'self'
ALTER TABLE self DROP COLUMN pk, ADD COLUMN pk INT;
ERROR HY000: Cannot drop column 'pk': needed in a foreign key constraint 'self_ibfk_1' of table 'self'
#
# 1.e) Special case. Attempt to create parent for orphan
#      foreign key which doesn't have matching column.
DROP TABLES child, parent;
SET FOREIGN_KEY_CHECKS=0;
CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES parent(nocol));
CREATE TABLE parent (pk INT PRIMARY KEY);
ERROR HY000: Failed to add the foreign key constraint. Missing column 'nocol' for constraint 'child_ibfk_1' in the referenced table 'parent'
CREATE TABLE parent0 (pk INT PRIMARY KEY);
RENAME TABLE parent0 TO parent;
ERROR HY000: Failed to add the foreign key constraint. Missing column 'nocol' for constraint 'child_ibfk_1' in the referenced table 'parent'
ALTER TABLE parent0 RENAME TO parent;
ERROR HY000: Failed to add the foreign key constraint. Missing column 'nocol' for constraint 'child_ibfk_1' in the referenced table 'parent'
SET FOREIGN_KEY_CHECKS=1;
DROP TABLES child, parent0, self;
#
# 2) Handling of virtual columns in referencing and referenced
#    columns lists.
#
#
# 2.a) Virtual columns in referencing columns list are not
#      supported.
CREATE TABLE parent (pk INT PRIMARY KEY);
CREATE TABLE child (base INT, fk INT GENERATED ALWAYS AS (base+1) VIRTUAL,
FOREIGN KEY (fk) REFERENCES parent(pk));
ERROR HY000: Foreign key 'child_ibfk_1' uses virtual column 'fk' which is not supported.
CREATE TABLE self (pk INT PRIMARY KEY, base INT,
fk INT GENERATED ALWAYS AS (base+1) VIRTUAL,
FOREIGN KEY (fk) REFERENCES self(pk));
ERROR HY000: Foreign key 'self_ibfk_1' uses virtual column 'fk' which is not supported.
CREATE TABLE child (base INT, fk INT GENERATED ALWAYS AS (base+1) VIRTUAL);
ALTER TABLE child ADD FOREIGN KEY (fk) REFERENCES parent(pk);
ERROR HY000: Foreign key 'child_ibfk_1' uses virtual column 'fk' which is not supported.
CREATE TABLE self (pk INT PRIMARY KEY, base INT,
fk INT GENERATED ALWAYS AS (base+1) VIRTUAL);
ALTER TABLE self ADD FOREIGN KEY (fk) REFERENCES self(pk);
ERROR HY000: Foreign key 'self_ibfk_1' uses virtual column 'fk' which is not supported.
#
# 2.b) One should not be able to get virtual column in referencing
#      columns list by ALTERing column.
DROP TABLE child, self;
CREATE TABLE child (base INT, fk INT GENERATED ALWAYS AS (base+1) STORED,
FOREIGN KEY (fk) REFERENCES parent(pk));
ALTER TABLE child MODIFY fk INT GENERATED ALWAYS AS (base+1) VIRTUAL;
ERROR HY000: 'Changing the STORED status' is not supported for generated columns.
CREATE TABLE self (pk INT PRIMARY KEY, base INT,
fk INT GENERATED ALWAYS AS (base+1) STORED,
FOREIGN KEY (fk) REFERENCES self(pk));
ALTER TABLE self MODIFY fk INT GENERATED ALWAYS AS (base+1) VIRTUAL;
ERROR HY000: 'Changing the STORED status' is not supported for generated columns.
#
# 2.c) Virtual columns in referenced columns list are not
#      supported.
DROP TABLE child, parent, self;
CREATE TABLE parent (base INT, pk INT GENERATED ALWAYS AS (base+1) VIRTUAL, UNIQUE KEY(pk));
CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES parent(pk));
ERROR HY000: Foreign key 'child_ibfk_1' uses virtual column 'pk' which is not supported.
CREATE TABLE child (fk INT);
ALTER TABLE child ADD FOREIGN KEY (fk) REFERENCES parent(pk);
ERROR HY000: Foreign key 'child_ibfk_1' uses virtual column 'pk' which is not supported.
CREATE TABLE self (base INT, pk INT GENERATED ALWAYS AS (base+1) VIRTUAL, fk INT,
UNIQUE KEY(pk), FOREIGN KEY (fk) REFERENCES self(pk));
ERROR HY000: Foreign key 'self_ibfk_1' uses virtual column 'pk' which is not supported.
CREATE TABLE self (base INT, pk INT GENERATED ALWAYS AS (base+1) VIRTUAL, fk INT,
UNIQUE KEY(pk));
ALTER TABLE self ADD FOREIGN KEY (fk) REFERENCES self(pk);
ERROR HY000: Foreign key 'self_ibfk_1' uses virtual column 'pk' which is not supported.
#
# 2.d) Again, one should not be able to get referenced virtual
#      column by ALTERing it.
DROP TABLE child, parent, self;
CREATE TABLE parent (base INT, pk INT GENERATED ALWAYS AS (base+1) STORED, UNIQUE KEY(pk));
CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES parent(pk));
ALTER TABLE parent MODIFY pk INT GENERATED ALWAYS AS (base+1) VIRTUAL;
ERROR HY000: 'Changing the STORED status' is not supported for generated columns.
CREATE TABLE self (base INT, pk INT GENERATED ALWAYS AS (base+1) STORED, fk INT,
UNIQUE KEY(pk), FOREIGN KEY (fk) REFERENCES self(pk));
ALTER TABLE self MODIFY pk INT GENERATED ALWAYS AS (base+1) VIRTUAL;
ERROR HY000: 'Changing the STORED status' is not supported for generated columns.
#
# 2.e) Special case. Attempt to create parent for orphan
#      foreign key which has virtual column.
DROP TABLES child, parent, self;
SET FOREIGN_KEY_CHECKS=0;
CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES parent(pk));
CREATE TABLE parent (base INT, pk INT GENERATED ALWAYS AS (base+1) VIRTUAL, UNIQUE KEY(pk));
ERROR HY000: Foreign key 'child_ibfk_1' uses virtual column 'pk' which is not supported.
CREATE TABLE parent0 (base INT, pk INT GENERATED ALWAYS AS (base+1) VIRTUAL, UNIQUE KEY(pk));
RENAME TABLE parent0 TO parent;
ERROR HY000: Foreign key 'child_ibfk_1' uses virtual column 'pk' which is not supported.
ALTER TABLE parent0 RENAME TO parent;
ERROR HY000: Foreign key 'child_ibfk_1' uses virtual column 'pk' which is not supported.
SET FOREIGN_KEY_CHECKS=1;
DROP TABLES child, parent0;
#
# 3) Check how missing/dropped supporting index on referencing table
#    is handled.
#
#
# 3.a) Normally, generated supporting index is automatically added
#      along with foreign key, so it can be missing only in some rare
#      corner case, for example, when generated index is automatically
#      converted to spatial index.
CREATE TABLE parent (pk POINT SRID 0 NOT NULL, KEY(pk));
CREATE TABLE child (fk POINT SRID 0 NOT NULL, FOREIGN KEY(fk) REFERENCES parent(pk));
ERROR HY000: Failed to add the foreign key constraint. Missing index for constraint 'child_ibfk_1' in the foreign table 'child'
CREATE TABLE child (fk POINT SRID 0 NOT NULL);
ALTER TABLE child ADD FOREIGN KEY(fk) REFERENCES parent(pk);
ERROR HY000: Failed to add the foreign key constraint. Missing index for constraint 'child_ibfk_1' in the foreign table 'child'
CREATE TABLE self (pk POINT SRID 0 NOT NULL, fk POINT SRID 0 NOT NULL,
KEY(pk), FOREIGN KEY(fk) REFERENCES self(pk));
ERROR HY000: Failed to add the foreign key constraint. Missing index for constraint 'self_ibfk_1' in the foreign table 'self'
CREATE TABLE self (pk POINT SRID 0 NOT NULL, fk POINT SRID 0 NOT NULL, KEY(pk));
ALTER TABLE self ADD FOREIGN KEY(fk) REFERENCES self(pk);
ERROR HY000: Failed to add the foreign key constraint. Missing index for constraint 'self_ibfk_1' in the foreign table 'self'
#
# 3.b) Attempt to drop supporting index should be prohibited.
DROP TABLES self, child, parent;
CREATE TABLE parent (pk INT PRIMARY KEY);
CREATE TABLE child (fk INT, FOREIGN KEY(fk) REFERENCES parent(pk));
ALTER TABLE child DROP KEY fk;
ERROR HY000: Cannot drop index 'fk': needed in a foreign key constraint
CREATE TABLE self (pk INT PRIMARY KEY, fk INT, FOREIGN KEY(fk) REFERENCES self(pk));
ALTER TABLE self DROP KEY fk;
ERROR HY000: Cannot drop index 'fk': needed in a foreign key constraint
# However, we allow automatic dropping of generated index when new
# explicit supporting index is added.
ALTER TABLE child ADD KEY fk_s(fk);
SHOW CREATE TABLE child;
Table	Create Table
child	CREATE TABLE `child` (
  `fk` int(11) DEFAULT NULL,
  KEY `fk_s` (`fk`),
  CONSTRAINT `child_ibfk_1` FOREIGN KEY (`fk`) REFERENCES `parent` (`pk`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
ALTER TABLE self ADD KEY fk_s(fk);
SHOW CREATE TABLE self;
Table	Create Table
self	CREATE TABLE `self` (
  `pk` int(11) NOT NULL,
  `fk` int(11) DEFAULT NULL,
  PRIMARY KEY (`pk`),
  KEY `fk_s` (`fk`),
  CONSTRAINT `self_ibfk_1` FOREIGN KEY (`fk`) REFERENCES `self` (`pk`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
# Also dropping supporting index and providing replacement is fine.
ALTER TABLE child DROP KEY fk_s, ADD COLUMN j INT, ADD KEY (fk, j);
ALTER TABLE self DROP KEY fk_s, ADD COLUMN j INT, ADD KEY(fk, j);
DROP TABLES self, child, parent;
#
# 4) Check how foreign keys involving partitioned tables are handled.
#
#
# 4.a) Creation of partitioned child table, addition of foreign key to
#      partitioned table and attempt to partition child table in a
#      foreign key should all lead to errors (since InnoDB doesn't
#      support foreign keys involving partitioned tables yet).
CREATE TABLE parent (pk INT PRIMARY KEY);
CREATE TABLE child (pk INT PRIMARY KEY, fk INT, FOREIGN KEY (fk) REFERENCES parent(pk))
PARTITION BY KEY (pk) PARTITIONS 20;
ERROR HY000: Foreign keys are not yet supported in conjunction with partitioning
CREATE TABLE child (pk INT PRIMARY KEY, fk INT) PARTITION BY KEY (pk) PARTITIONS 20;
ALTER TABLE child ADD FOREIGN KEY (fk) REFERENCES parent(pk);
ERROR HY000: Foreign keys are not yet supported in conjunction with partitioning
DROP TABLE child;
CREATE TABLE child (pk INT PRIMARY KEY, fk INT, FOREIGN KEY (fk) REFERENCES parent(pk));
ALTER TABLE child PARTITION BY KEY (pk) PARTITIONS 20;
ERROR HY000: Foreign keys are not yet supported in conjunction with partitioning
#
# 4.b) Attempt to partition parent table in a foreign key,
#      attempt to create table with foreign key referencing
#      partitioned table and addition of foreign key with
#      partitioned parent should all lead to errors as well.
ALTER TABLE parent PARTITION BY KEY (pk) PARTITIONS 20;
ERROR HY000: Foreign keys are not yet supported in conjunction with partitioning
DROP TABLES child, parent;
CREATE TABLE parent (pk INT PRIMARY KEY) PARTITION BY KEY (pk) PARTITIONS 20;
CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES parent(pk));
ERROR HY000: Foreign keys are not yet supported in conjunction with partitioning
CREATE TABLE child (fk INT);
ALTER TABLE child ADD FOREIGN KEY (fk) REFERENCES parent(pk);
ERROR HY000: Foreign keys are not yet supported in conjunction with partitioning
DROP TABLES child, parent;
#
# 4.c) Addition of partitioned parent for previously orphan foreign key
#      should result in error as well.
SET FOREIGN_KEY_CHECKS=0;
CREATE TABLE orphan (fk INT, FOREIGN KEY (fk) REFERENCES parent(pk));
SET FOREIGN_KEY_CHECKS=1;
CREATE TABLE parent (pk INT PRIMARY KEY) PARTITION BY KEY (pk) PARTITIONS 20;
ERROR HY000: Foreign keys are not yet supported in conjunction with partitioning
CREATE TABLE parent0 (pk INT PRIMARY KEY) PARTITION BY KEY (pk) PARTITIONS 20;
RENAME TABLE parent0 TO parent;
ERROR HY000: Foreign keys are not yet supported in conjunction with partitioning
ALTER TABLE parent0 RENAME TO parent;
ERROR HY000: Foreign keys are not yet supported in conjunction with partitioning
ALTER TABLE parent0 RENAME TO parent, ADD COLUMN j INT, ALGORITHM=COPY;
ERROR HY000: Foreign keys are not yet supported in conjunction with partitioning
ALTER TABLE parent0 RENAME TO parent, ADD COLUMN j INT, ALGORITHM=INPLACE;
ERROR HY000: Foreign keys are not yet supported in conjunction with partitioning
DROP TABLES orphan, parent0;
#
# 4.d) ALTER TABLE which ADD/DROP FOREIGN KEY and change table's
#      partitioning status at the same time.
CREATE TABLE parent (pk INT PRIMARY KEY);
CREATE TABLE child (pk INT PRIMARY KEY, fk INT,
CONSTRAINT c FOREIGN KEY (fk) REFERENCES parent(pk));
# Dropping foreign key and adding partitioning is OK.
ALTER TABLE child DROP FOREIGN KEY c PARTITION BY KEY (pk) PARTITIONS 20;
# Adding foreign key and removing partitioning is OK.
ALTER TABLE child ADD FOREIGN KEY (fk) REFERENCES parent(pk) REMOVE PARTITIONING;
DROP TABLES child, parent;
CREATE TABLE self (pk INT PRIMARY KEY, fk INT,
CONSTRAINT c FOREIGN KEY (fk) REFERENCES self(pk));
# Dropping foreign key and adding partitioning is OK.
ALTER TABLE self DROP FOREIGN KEY c PARTITION BY KEY (pk) PARTITIONS 20;
# Adding foreign key and removing partitioning should be OK.
# However, the below statement fails due to bug#28486106 in InnoDB code.
ALTER TABLE self ADD FOREIGN KEY (fk) REFERENCES self(pk) REMOVE PARTITIONING;
ERROR HY000: Cannot add foreign key constraint
DROP TABLES self;
#
# 6) Check that we prohibit foreign keys with SET NULL action with
#    non-nullable referencing columns.
#
#
# 6.1) Attempt to add foreign key with SET NULL action and
#      non-nullable column should lead to error.
CREATE TABLE parent (pk INT PRIMARY KEY);
CREATE TABLE child (fk INT NOT NULL, CONSTRAINT c FOREIGN KEY (fk) REFERENCES parent(pk) ON DELETE SET NULL);
ERROR HY000: Column 'fk' cannot be NOT NULL: needed in a foreign key constraint 'c' SET NULL
CREATE TABLE child (fk INT NOT NULL, CONSTRAINT c FOREIGN KEY (fk) REFERENCES parent(pk) ON UPDATE SET NULL);
ERROR HY000: Column 'fk' cannot be NOT NULL: needed in a foreign key constraint 'c' SET NULL
CREATE TABLE child (fk INT NOT NULL);
ALTER TABLE child ADD FOREIGN KEY (fk) REFERENCES parent(pk) ON DELETE SET NULL;
ERROR HY000: Column 'fk' cannot be NOT NULL: needed in a foreign key constraint 'child_ibfk_1' SET NULL
ALTER TABLE child ADD FOREIGN KEY (fk) REFERENCES parent(pk) ON UPDATE SET NULL;
ERROR HY000: Column 'fk' cannot be NOT NULL: needed in a foreign key constraint 'child_ibfk_1' SET NULL
DROP TABLE child;
# Case of when column is implicitly made non-nullable due to PRIMARY
# KEY should be handled in the same way.
CREATE TABLE child (fk INT, PRIMARY KEY(fk), CONSTRAINT c FOREIGN KEY (fk) REFERENCES parent(pk) ON DELETE SET NULL);
ERROR HY000: Column 'fk' cannot be NOT NULL: needed in a foreign key constraint 'c' SET NULL
CREATE TABLE child (fk INT, PRIMARY KEY(fk), CONSTRAINT c FOREIGN KEY (fk) REFERENCES parent(pk) ON UPDATE SET NULL);
ERROR HY000: Column 'fk' cannot be NOT NULL: needed in a foreign key constraint 'c' SET NULL
CREATE TABLE child (fk INT);
ALTER TABLE child ADD PRIMARY KEY (fk), ADD FOREIGN KEY (fk) REFERENCES parent(pk) ON DELETE SET NULL;
ERROR HY000: Column 'fk' cannot be NOT NULL: needed in a foreign key constraint 'child_ibfk_1' SET NULL
ALTER TABLE child ADD PRIMARY KEY (fk), ADD FOREIGN KEY (fk) REFERENCES parent(pk) ON UPDATE SET NULL;
ERROR HY000: Column 'fk' cannot be NOT NULL: needed in a foreign key constraint 'child_ibfk_1' SET NULL
DROP TABLE child;
#
# 6.2) Attempt to make referencing column non-nullable in existing
#      foreign key with SET NULL action should lead to error as well.
CREATE TABLE child_one (fk INT, CONSTRAINT c FOREIGN KEY (fk) REFERENCES parent(pk) ON DELETE SET NULL);
CREATE TABLE child_two (fk INT, FOREIGN KEY (fk) REFERENCES parent(pk) ON DELETE SET NULL);
ALTER TABLE child_one MODIFY COLUMN fk INT NOT NULL;
ERROR HY000: Column 'fk' cannot be NOT NULL: needed in a foreign key constraint 'c' SET NULL
ALTER TABLE child_two CHANGE COLUMN fk fk1 INT NOT NULL;
ERROR HY000: Column 'fk1' cannot be NOT NULL: needed in a foreign key constraint 'child_two_ibfk_1' SET NULL
# Case of when column is implicitly made non-nullable due addition
# of PRIMARY KEY should be handled in the same way.
ALTER TABLE child_one ADD PRIMARY KEY(fk);
ERROR HY000: Column 'fk' cannot be NOT NULL: needed in a foreign key constraint 'c' SET NULL
ALTER TABLE child_two ADD PRIMARY KEY(fk);
ERROR HY000: Column 'fk' cannot be NOT NULL: needed in a foreign key constraint 'child_two_ibfk_1' SET NULL
DROP TABLES child_one, child_two, parent;
#
# 7) Test that we check that referencing and referenced column types are
#    compatible. Such check should be performed for newly created foreign
#    keys and when we change types of columns in existing foreign keys.
#
#
# 7.a) Attempt to create new foreign key between columns of incompatible
#      types should lead to error. This should happen even in
#      FOREIGN_KEY_CHECKS=0 mode.
CREATE TABLE parent (pk INT PRIMARY KEY);
CREATE TABLE child (fk CHAR(10), FOREIGN KEY (fk) REFERENCES parent(pk));
ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_ibfk_1' are incompatible.
SET FOREIGN_KEY_CHECKS=0;
CREATE TABLE child (fk CHAR(10), FOREIGN KEY (fk) REFERENCES parent(pk));
ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_ibfk_1' are incompatible.
SET FOREIGN_KEY_CHECKS=1;
CREATE TABLE child (fk CHAR(10));
ALTER TABLE child ADD FOREIGN KEY (fk) REFERENCES parent(pk);
ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_ibfk_1' are incompatible.
SET FOREIGN_KEY_CHECKS=0;
ALTER TABLE child ADD FOREIGN KEY (fk) REFERENCES parent(pk);
ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_ibfk_1' are incompatible.
SET FOREIGN_KEY_CHECKS=1;
CREATE TABLE self (pk INT PRIMARY KEY, fk CHAR(10), FOREIGN KEY (fk) REFERENCES self(pk));
ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'self_ibfk_1' are incompatible.
SET FOREIGN_KEY_CHECKS=0;
CREATE TABLE self (pk INT PRIMARY KEY, fk CHAR(10), FOREIGN KEY (fk) REFERENCES self(pk));
ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'self_ibfk_1' are incompatible.
SET FOREIGN_KEY_CHECKS=1;
CREATE TABLE self (pk INT PRIMARY KEY, fk CHAR(10));
ALTER TABLE self ADD FOREIGN KEY (fk) REFERENCES self(pk);
ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'self_ibfk_1' are incompatible.
SET FOREIGN_KEY_CHECKS=0;
ALTER TABLE self ADD FOREIGN KEY (fk) REFERENCES self(pk);
ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'self_ibfk_1' are incompatible.
SET FOREIGN_KEY_CHECKS=1;
DROP TABLES self, child;
#
# 7.b) Attempt to change referencing or referenced column in existing
#      foreign key to incompatible type should lead to error. This
#      should also happen in FOREIGN_KEY_CHECKS=0 mode in general case
#      (there is exception for charset changes which we test below).
CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES parent(pk));
ALTER TABLE child MODIFY fk CHAR(10);
ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_ibfk_1' are incompatible.
ALTER TABLE parent MODIFY pk CHAR(10);
ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_ibfk_1' are incompatible.
SET FOREIGN_KEY_CHECKS=0;
ALTER TABLE child MODIFY fk CHAR(10);
ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_ibfk_1' are incompatible.
ALTER TABLE parent MODIFY pk CHAR(10);
ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_ibfk_1' are incompatible.
SET FOREIGN_KEY_CHECKS=1;
CREATE TABLE self (pk INT PRIMARY KEY, fk INT, FOREIGN KEY (fk) REFERENCES self(pk));
ALTER TABLE self MODIFY fk CHAR(10);
ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'self_ibfk_1' are incompatible.
ALTER TABLE self MODIFY pk CHAR(10);
ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'self_ibfk_1' are incompatible.
SET FOREIGN_KEY_CHECKS=0;
ALTER TABLE self MODIFY fk CHAR(10);
ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'self_ibfk_1' are incompatible.
ALTER TABLE self MODIFY pk CHAR(10);
ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'self_ibfk_1' are incompatible.
SET FOREIGN_KEY_CHECKS=1;
# Modifying types of both referencing and referenced columns to
# to compatible types is OK. However FOREIGN_KEY_CHECKS=0 is needed
# to avoid error about possible FK violation due to data conversion.
SET FOREIGN_KEY_CHECKS=0;
ALTER TABLE self MODIFY pk CHAR(10), MODIFY fk CHAR(10);
SET FOREIGN_KEY_CHECKS=1;
DROP TABLES child, parent, self;
#
# 7.c) Test compatibility checks for multi-column foreign keys.
CREATE TABLE parent (pk1 INT, pk2 INT, PRIMARY KEY (pk1, pk2));
CREATE TABLE child (fk1 INT, fk2 CHAR(10), FOREIGN KEY (fk1, fk2) REFERENCES parent(pk1, pk2));
ERROR HY000: Referencing column 'fk2' and referenced column 'pk2' in foreign key constraint 'child_ibfk_1' are incompatible.
CREATE TABLE child (fk1 INT, fk2 CHAR(10));
ALTER TABLE child ADD FOREIGN KEY (fk1, fk2) REFERENCES parent(pk1, pk2);
ERROR HY000: Referencing column 'fk2' and referenced column 'pk2' in foreign key constraint 'child_ibfk_1' are incompatible.
DROP TABLE child;
CREATE TABLE child (fk1 INT, fk2 INT, FOREIGN KEY (fk1, fk2) REFERENCES parent(pk1, pk2));
ALTER TABLE child MODIFY fk2 CHAR(10);
ERROR HY000: Referencing column 'fk2' and referenced column 'pk2' in foreign key constraint 'child_ibfk_1' are incompatible.
ALTER TABLE parent MODIFY pk2 CHAR(10);
ERROR HY000: Referencing column 'fk2' and referenced column 'pk2' in foreign key constraint 'child_ibfk_1' are incompatible.
DROP TABLE child, parent;
CREATE TABLE self (pk1 INT, pk2 INT, fk1 INT, fk2 CHAR(10), PRIMARY KEY (pk1, pk2),
FOREIGN KEY (fk1, fk2) REFERENCES self(pk1, pk2));
ERROR HY000: Referencing column 'fk2' and referenced column 'pk2' in foreign key constraint 'self_ibfk_1' are incompatible.
CREATE TABLE self (pk1 INT, pk2 INT, fk1 INT, fk2 CHAR(10), PRIMARY KEY (pk1, pk2));
ALTER TABLE self ADD FOREIGN KEY (fk1, fk2) REFERENCES self(pk1, pk2);
ERROR HY000: Referencing column 'fk2' and referenced column 'pk2' in foreign key constraint 'self_ibfk_1' are incompatible.
DROP TABLE self;
CREATE TABLE self (pk1 INT, pk2 INT, fk1 INT, fk2 INT, PRIMARY KEY (pk1, pk2),
FOREIGN KEY (fk1, fk2) REFERENCES self(pk1, pk2));
ALTER TABLE self MODIFY fk2 CHAR(10);
ERROR HY000: Referencing column 'fk2' and referenced column 'pk2' in foreign key constraint 'self_ibfk_1' are incompatible.
ALTER TABLE self MODIFY pk2 CHAR(10);
ERROR HY000: Referencing column 'fk2' and referenced column 'pk2' in foreign key constraint 'self_ibfk_1' are incompatible.
DROP TABLE self;
#
# 7.e) Test how compatibility rules work for various types.
#      Different engines have different rules, so the below
#      part of test is specific to InnoDB.
#
# We don't check compatibility for BLOB based types as they only
# support prefix indexes which are not usable for foreign keys.
# Also we don't provide coverage for legacy types.
#
# 7.e.I) For integer types both type size and signedness should match.
CREATE TABLE parent (pk INT PRIMARY KEY);
# Foreign keys over integer types are supported.
CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES parent(pk));
DROP TABLE child;
CREATE TABLE child (fk TINYINT, FOREIGN KEY (fk) REFERENCES parent(pk));
ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_ibfk_1' are incompatible.
CREATE TABLE child (fk BIGINT, FOREIGN KEY (fk) REFERENCES parent(pk));
ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_ibfk_1' are incompatible.
CREATE TABLE child (fk INT UNSIGNED, FOREIGN KEY (fk) REFERENCES parent(pk));
ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_ibfk_1' are incompatible.
ALTER TABLE parent MODIFY pk INT UNSIGNED;
CREATE TABLE child (fk INT UNSIGNED, FOREIGN KEY (fk) REFERENCES parent(pk));
ALTER TABLE child MODIFY fk INT;
ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_ibfk_1' are incompatible.
DROP TABLE child, parent;
# Integer types are not compatible with most of other types
# (we check types with same storage requirements as INT below).
CREATE TABLE parent (pk INT PRIMARY KEY);
CREATE TABLE child (fk BINARY(4), FOREIGN KEY (fk) REFERENCES parent(pk));
ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_ibfk_1' are incompatible.
CREATE TABLE child (fk VARBINARY(3), FOREIGN KEY (fk) REFERENCES parent(pk));
ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_ibfk_1' are incompatible.
CREATE TABLE child (fk FLOAT, FOREIGN KEY (fk) REFERENCES parent(pk));
ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_ibfk_1' are incompatible.
CREATE TABLE child (fk DECIMAL(8,0), FOREIGN KEY (fk) REFERENCES parent(pk));
ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_ibfk_1' are incompatible.
CREATE TABLE child (fk TIMESTAMP, FOREIGN KEY (fk) REFERENCES parent(pk));
ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_ibfk_1' are incompatible.
CREATE TABLE child (fk BIT(32), FOREIGN KEY (fk) REFERENCES parent(pk));
ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_ibfk_1' are incompatible.
# Oddly enough, some integer types are compatible with some temporal
# types, enums and sets. However, this is probably a bug and not a
# feature, so we don't test it here.
DROP TABLE parent;
#
# 7.e.II) For floating point types only the exact type matters.
CREATE TABLE parent (pk DOUBLE PRIMARY KEY);
# Though using such types in foreign key is EXTREMELY bad idea they
# are supported.
CREATE TABLE child (fk DOUBLE, FOREIGN KEY (fk) REFERENCES parent(pk));
DROP TABLE child;
CREATE TABLE child (fk FLOAT, FOREIGN KEY (fk) REFERENCES parent(pk));
ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_ibfk_1' are incompatible.
# Signedness doesn't matter.
CREATE TABLE child (fk DOUBLE UNSIGNED, FOREIGN KEY (fk) REFERENCES parent(pk));
Warnings:
Warning	1681	UNSIGNED for decimal and floating point data types is deprecated and support for it will be removed in a future release.
DROP TABLES child, parent;
# Floating point types are not compatible with other types.
# (we check types with same storage requirements as FLOAT below).
CREATE TABLE parent (pk FLOAT PRIMARY KEY);
CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES parent(pk));
ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_ibfk_1' are incompatible.
CREATE TABLE child (fk BINARY(4), FOREIGN KEY (fk) REFERENCES parent(pk));
ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_ibfk_1' are incompatible.
CREATE TABLE child (fk VARBINARY(3), FOREIGN KEY (fk) REFERENCES parent(pk));
ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_ibfk_1' are incompatible.
CREATE TABLE child (fk DECIMAL(8,0), FOREIGN KEY (fk) REFERENCES parent(pk));
ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_ibfk_1' are incompatible.
CREATE TABLE child (fk TIMESTAMP, FOREIGN KEY (fk) REFERENCES parent(pk));
ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_ibfk_1' are incompatible.
CREATE TABLE child (fk TIME(2), FOREIGN KEY (fk) REFERENCES parent(pk));
ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_ibfk_1' are incompatible.
CREATE TABLE child (fk BIT(32), FOREIGN KEY (fk) REFERENCES parent(pk));
ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_ibfk_1' are incompatible.
DROP TABLE parent;
#
# 7.e.III) Compatibility rules for DECIMAL type are broken.
#
#          InnoDB considers this type to be binary string type.
#          So it doesn't take into account precision and scale.
#          And it is hard to imagine that comparing binary strings
#          representing DECIMAL(10,9) and DECIMAL(10,1) can work
#          correctly. Making this type compatible with other binary
#          string types, some temporals and BIT type is probably a
#          bad idea too.
CREATE TABLE parent (pk DECIMAL(6,2) PRIMARY KEY);
# Foreign keys over DECIMAL columns are supported.
CREATE TABLE child (fk DECIMAL(6,2), FOREIGN KEY (fk) REFERENCES parent(pk));
DROP TABLE child;
# Signedness doesn't matter.
CREATE TABLE child (fk DECIMAL(6,2) UNSIGNED, FOREIGN KEY (fk) REFERENCES parent(pk));
Warnings:
Warning	1681	UNSIGNED for decimal and floating point data types is deprecated and support for it will be removed in a future release.
DROP TABLE child;
# DECIMAL type is not compatible with many other types.
# (we check types with same storage requirements as DECIMAL(6,2) below).
CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES parent(pk));
ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_ibfk_1' are incompatible.
CREATE TABLE child (fk CHAR(4) CHARACTER SET latin1, FOREIGN KEY (fk) REFERENCES parent(pk));
ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_ibfk_1' are incompatible.
CREATE TABLE child (fk VARCHAR(3) CHARACTER SET latin1, FOREIGN KEY (fk) REFERENCES parent(pk));
ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_ibfk_1' are incompatible.
CREATE TABLE child (fk FLOAT, FOREIGN KEY (fk) REFERENCES parent(pk));
ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_ibfk_1' are incompatible.
DROP TABLE parent;
#
# 7.e.IV) All string types are compatible with each other provided
#         that charset matches (there are exceptions to the latter
#         rule, see below).
CREATE TABLE parent (pk CHAR(10) PRIMARY KEY);
# Difference in size doesn't matter.
CREATE TABLE child (fk CHAR(100), FOREIGN KEY (fk) REFERENCES parent(pk));
# Difference in type doesn't matter
DROP TABLE child;
CREATE TABLE child (fk VARCHAR(100), FOREIGN KEY (fk) REFERENCES parent(pk));
DROP TABLES child, parent;
# Even VARCHARs which use different number of bytes to store length
# are compatible.
CREATE TABLE parent (pk VARCHAR(10) PRIMARY KEY);
CREATE TABLE child (fk VARCHAR(100), FOREIGN KEY (fk) REFERENCES parent(pk));
DROP TABLE child, parent;
# However both columns must use the same collation.
CREATE TABLE parent (pk VARCHAR(10) CHARACTER SET utf8mb4 PRIMARY KEY);
CREATE TABLE child (fk VARCHAR(100) CHARACTER SET latin1, FOREIGN KEY (fk) REFERENCES parent(pk));
ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_ibfk_1' are incompatible.
CREATE TABLE child (fk VARCHAR(100) COLLATE utf8mb4_bin, FOREIGN KEY (fk) REFERENCES parent(pk));
ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_ibfk_1' are incompatible.
# Binary strings are not compatible with non-binary strings.
CREATE TABLE child (fk VARBINARY(40), FOREIGN KEY (fk) REFERENCES parent(pk));
ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_ibfk_1' are incompatible.
DROP TABLE parent;
# But all binary string types are compatible.
CREATE TABLE parent (pk VARBINARY(10) PRIMARY KEY);
CREATE TABLE child (fk BINARY(100), FOREIGN KEY (fk) REFERENCES parent(pk));
DROP TABLES child, parent;
# Non-binary string types are incompatible with non-string types.
CREATE TABLE parent (pk CHAR(4) CHARACTER SET latin1 PRIMARY KEY);
CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES parent(pk));
ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_ibfk_1' are incompatible.
CREATE TABLE child (fk FLOAT, FOREIGN KEY (fk) REFERENCES parent(pk));
ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_ibfk_1' are incompatible.
CREATE TABLE child (fk DECIMAL(8,0), FOREIGN KEY (fk) REFERENCES parent(pk));
ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_ibfk_1' are incompatible.
CREATE TABLE child (fk TIMESTAMP, FOREIGN KEY (fk) REFERENCES parent(pk));
ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_ibfk_1' are incompatible.
CREATE TABLE child (fk TIME(2), FOREIGN KEY (fk) REFERENCES parent(pk));
ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_ibfk_1' are incompatible.
CREATE TABLE child (fk BIT(32), FOREIGN KEY (fk) REFERENCES parent(pk));
ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_ibfk_1' are incompatible.
ALTER TABLE parent MODIFY pk CHAR(1) CHARACTER SET latin1;
CREATE TABLE child (fk YEAR, FOREIGN KEY (fk) REFERENCES parent(pk));
ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_ibfk_1' are incompatible.
CREATE TABLE child (fk ENUM('a') CHARACTER SET latin1, FOREIGN KEY (fk) REFERENCES parent(pk));
ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_ibfk_1' are incompatible.
CREATE TABLE child (fk SET('a') CHARACTER SET latin1, FOREIGN KEY (fk) REFERENCES parent(pk));
ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_ibfk_1' are incompatible.
DROP TABLE parent;
# Binary string types are incompatible with many non-string types.
CREATE TABLE parent (pk BINARY(4) PRIMARY KEY);
CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES parent(pk));
ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_ibfk_1' are incompatible.
CREATE TABLE child (fk FLOAT, FOREIGN KEY (fk) REFERENCES parent(pk));
ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_ibfk_1' are incompatible.
ALTER TABLE parent MODIFY pk BINARY(1);
CREATE TABLE child (fk YEAR, FOREIGN KEY (fk) REFERENCES parent(pk));
ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_ibfk_1' are incompatible.
CREATE TABLE child (fk ENUM('a') CHARACTER SET binary, FOREIGN KEY (fk) REFERENCES parent(pk));
ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_ibfk_1' are incompatible.
CREATE TABLE child (fk SET('a') CHARACTER SET binary, FOREIGN KEY (fk) REFERENCES parent(pk));
ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_ibfk_1' are incompatible.
# Since we think that binary string compatibility with DECIMAL and some temporal types
# is probably a bug, we don't cover it here.
DROP TABLE parent;
#
# 7.e.V) Compatibility rules for some of temporal types are broken.
#
#        InnoDB considers TIME, TIMESTAMP and DATETIME types to be
#        binary strings. As result they are compatible with each other
#        (no matter what scale is used), binary string, DECIMAL and BIT
#        types, which makes little sense.
#        YEAR and DATE types are considered to be integer types which
#        makes them compatible with some other integer types, enums and
#        sets, which is probably bad idea too.
#
#        YEAR and DATE are only compatible with itself (and integer types,
#        enums and sets with the same storage size which is probably a bug);
CREATE TABLE parent(pk DATE PRIMARY KEY);
CREATE TABLE child (fk DATE, FOREIGN KEY (fk) REFERENCES parent(pk));
DROP TABLE child;
CREATE TABLE child (fk YEAR, FOREIGN KEY (fk) REFERENCES parent(pk));
ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_ibfk_1' are incompatible.
CREATE TABLE child (fk BINARY(3), FOREIGN KEY (fk) REFERENCES parent(pk));
ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_ibfk_1' are incompatible.
CREATE TABLE child (fk VARBINARY(2), FOREIGN KEY (fk) REFERENCES parent(pk));
ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_ibfk_1' are incompatible.
CREATE TABLE child (fk FLOAT, FOREIGN KEY (fk) REFERENCES parent(pk));
ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_ibfk_1' are incompatible.
CREATE TABLE child (fk DECIMAL(6,0), FOREIGN KEY (fk) REFERENCES parent(pk));
ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_ibfk_1' are incompatible.
CREATE TABLE child (fk TIME(0), FOREIGN KEY (fk) REFERENCES parent(pk));
ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_ibfk_1' are incompatible.
CREATE TABLE child (fk TIMESTAMP, FOREIGN KEY (fk) REFERENCES parent(pk));
ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_ibfk_1' are incompatible.
CREATE TABLE child (fk BIT(24), FOREIGN KEY (fk) REFERENCES parent(pk));
ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_ibfk_1' are incompatible.
DROP TABLE parent;
#        TIME, TIMESTAMP and DATETIME types are compatible only
#        with other types which InnoDB considers binary strings.
#        Their scale doesn't matter (which is probably a bug).
CREATE TABLE parent (pk TIMESTAMP PRIMARY KEY);
CREATE TABLE child (fk TIMESTAMP, FOREIGN KEY (fk) REFERENCES parent(pk));
DROP TABLE child;
CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES parent(pk));
ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_ibfk_1' are incompatible.
CREATE TABLE child (fk CHAR(4) CHARACTER SET latin1, FOREIGN KEY (fk) REFERENCES parent(pk));
ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_ibfk_1' are incompatible.
CREATE TABLE child (fk VARCHAR(3) CHARACTER SET latin1, FOREIGN KEY (fk) REFERENCES parent(pk));
ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_ibfk_1' are incompatible.
CREATE TABLE child (fk FLOAT, FOREIGN KEY (fk) REFERENCES parent(pk));
ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_ibfk_1' are incompatible.
CREATE TABLE child (fk DATE, FOREIGN KEY (fk) REFERENCES parent(pk));
ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_ibfk_1' are incompatible.
CREATE TABLE child (fk ENUM('a'), FOREIGN KEY (fk) REFERENCES parent(pk));
ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_ibfk_1' are incompatible.
CREATE TABLE child (fk SET('a'), FOREIGN KEY (fk) REFERENCES parent(pk));
ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_ibfk_1' are incompatible.
DROP TABLE parent;
#
# 7.e.VI) Columns of ENUM type are compatible if storage size is
#         the same.
#
# They are also compatible with some integer types, but it is not
# clear if it is a bug or feature.
#
CREATE TABLE parent(pk ENUM('a') PRIMARY KEY);
# Foreign key over ENUMs are supported, element names and count do
# not matter provided that storage size is the same.
CREATE TABLE child (fk ENUM('b','c'), FOREIGN KEY (fk) REFERENCES parent(pk));
DROP TABLE child;
# Storage size should match.
CREATE TABLE child (fk ENUM('a1','a2','a3','a4','a5','a6','a7','a8','a9','a10','a11','a12','a13','a14','a15','a16','a17','a18','a19','a20','a21','a22','a23','a24','a25','a26','a27','a28','a29','a30','a31','a32','a33','a34','a35','a36','a37','a38','a39','a40','a41','a42','a43','a44','a45','a46','a47','a48','a49','a50','a51','a52','a53','a54','a55','a56','a57','a58','a59','a60','a61','a62','a63','a64','a65','a66','a67','a68','a69','a70','a71','a72','a73','a74','a75','a76','a77','a78','a79','a80','a81','a82','a83','a84','a85','a86','a87','a88','a89','a90','a91','a92','a93','a94','a95','a96','a97','a98','a99','a100','a101','a102','a103','a104','a105','a106','a107','a108','a109','a110','a111','a112','a113','a114','a115','a116','a117','a118','a119','a120','a121','a122','a123','a124','a125','a126','a127','a128','a129','a130','a131','a132','a133','a134','a135','a136','a137','a138','a139','a140','a141','a142','a143','a144','a145','a146','a147','a148','a149','a150','a151','a152','a153','a154','a155','a156','a157','a158','a159','a160','a161','a162','a163','a164','a165','a166','a167','a168','a169','a170','a171','a172','a173','a174','a175','a176','a177','a178','a179','a180','a181','a182','a183','a184','a185','a186','a187','a188','a189','a190','a191','a192','a193','a194','a195','a196','a197','a198','a199','a200','a201','a202','a203','a204','a205','a206','a207','a208','a209','a210','a211','a212','a213','a214','a215','a216','a217','a218','a219','a220','a221','a222','a223','a224','a225','a226','a227','a228','a229','a230','a231','a232','a233','a234','a235','a236','a237','a238','a239','a240','a241','a242','a243','a244','a245','a246','a247','a248','a249','a250','a251','a252','a253','a254','a255','a256'), FOREIGN KEY (fk) REFERENCES parent(pk));
ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_ibfk_1' are incompatible.
CREATE TABLE child (fk DATE, FOREIGN KEY (fk) REFERENCES parent(pk));
ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_ibfk_1' are incompatible.
CREATE TABLE child (fk BINARY(1), FOREIGN KEY (fk) REFERENCES parent(pk));
ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_ibfk_1' are incompatible.
CREATE TABLE child (fk VARBINARY(1), FOREIGN KEY (fk) REFERENCES parent(pk));
ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_ibfk_1' are incompatible.
CREATE TABLE child (fk FLOAT, FOREIGN KEY (fk) REFERENCES parent(pk));
ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_ibfk_1' are incompatible.
CREATE TABLE child (fk DECIMAL(2,0), FOREIGN KEY (fk) REFERENCES parent(pk));
ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_ibfk_1' are incompatible.
CREATE TABLE child (fk TIME, FOREIGN KEY (fk) REFERENCES parent(pk));
ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_ibfk_1' are incompatible.
CREATE TABLE child (fk TIMESTAMP, FOREIGN KEY (fk) REFERENCES parent(pk));
ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_ibfk_1' are incompatible.
CREATE TABLE child (fk BIT(8), FOREIGN KEY (fk) REFERENCES parent(pk));
ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_ibfk_1' are incompatible.
DROP TABLE parent;
#
# 7.e.VII) Columns of SET type are compatible if storage size is
#          the same.
#
# They are also compatible with some integer types, but it is not
# clear if it is a bug or feature.
#
CREATE TABLE parent(pk SET('a') PRIMARY KEY);
# Foreign key over SETs are supported, element names and count do
# not matter provided that storage size is the same.
CREATE TABLE child (fk SET('b','c'), FOREIGN KEY (fk) REFERENCES parent(pk));
DROP TABLE child;
# Storage size should match.
CREATE TABLE child (fk SET('a1','a2','a3','a4','a5','a6','a7','a8','a9'), FOREIGN KEY (fk) REFERENCES parent(pk));
ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_ibfk_1' are incompatible.
CREATE TABLE child (fk DATE, FOREIGN KEY (fk) REFERENCES parent(pk));
ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_ibfk_1' are incompatible.
CREATE TABLE child (fk BINARY(1), FOREIGN KEY (fk) REFERENCES parent(pk));
ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_ibfk_1' are incompatible.
CREATE TABLE child (fk VARBINARY(1), FOREIGN KEY (fk) REFERENCES parent(pk));
ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_ibfk_1' are incompatible.
CREATE TABLE child (fk FLOAT, FOREIGN KEY (fk) REFERENCES parent(pk));
ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_ibfk_1' are incompatible.
CREATE TABLE child (fk DECIMAL(2,0), FOREIGN KEY (fk) REFERENCES parent(pk));
ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_ibfk_1' are incompatible.
CREATE TABLE child (fk TIME, FOREIGN KEY (fk) REFERENCES parent(pk));
ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_ibfk_1' are incompatible.
CREATE TABLE child (fk TIMESTAMP, FOREIGN KEY (fk) REFERENCES parent(pk));
ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_ibfk_1' are incompatible.
CREATE TABLE child (fk BIT(8), FOREIGN KEY (fk) REFERENCES parent(pk));
ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_ibfk_1' are incompatible.
DROP TABLE parent;
#
# 7.e.VIII) Columns of BIT type are compatible with each other
#           independently of size.
#
# They are considered binary strings so compatible with other
# binary string types (which might make sense). They are also
# compatible with some temporals and DECIMAL type, which makes
# little sense.
CREATE TABLE parent(pk BIT(32) PRIMARY KEY);
# Column size doesn't matter.
CREATE TABLE child (fk BIT(10), FOREIGN KEY (fk) REFERENCES parent(pk));
DROP TABLE child;
CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES parent(pk));
ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_ibfk_1' are incompatible.
CREATE TABLE child (fk DATE, FOREIGN KEY (fk) REFERENCES parent(pk));
ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_ibfk_1' are incompatible.
CREATE TABLE child (fk CHAR(4) CHARACTER SET latin1, FOREIGN KEY (fk) REFERENCES parent(pk));
ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_ibfk_1' are incompatible.
CREATE TABLE child (fk VARCHAR(3) CHARACTER SET latin1, FOREIGN KEY (fk) REFERENCES parent(pk));
ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_ibfk_1' are incompatible.
CREATE TABLE child (fk FLOAT, FOREIGN KEY (fk) REFERENCES parent(pk));
ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_ibfk_1' are incompatible.
CREATE TABLE child (fk ENUM('a'), FOREIGN KEY (fk) REFERENCES parent(pk));
ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_ibfk_1' are incompatible.
CREATE TABLE child (fk SET('a'), FOREIGN KEY (fk) REFERENCES parent(pk));
ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_ibfk_1' are incompatible.
DROP TABLE parent;
#
# 7.e.IX) In FOREIGN_KEY_CHECKS=0 mode InnoDB allows to change charsets
#         of string columns in existing foreign keys. Allowing such
#         temporary discrepancies is necessary as in general case there
#         is no way to change charset of both child and parent columns
#         simultaneously. Such discrepancies are still prohibited
#         in newly created foreign keys.
CREATE TABLE parent (pk VARCHAR(10) CHARACTER SET latin1 PRIMARY KEY);
CREATE TABLE child (fk VARCHAR(20) CHARACTER SET latin1,
FOREIGN KEY (fk) REFERENCES parent(pk));
SET FOREIGN_KEY_CHECKS=0;
ALTER TABLE parent MODIFY pk VARCHAR(10) CHARACTER SET utf8mb4;
ALTER TABLE child MODIFY fk VARCHAR(20) CHARACTER SET utf8mb4;
ALTER TABLE child MODIFY fk VARCHAR(20) CHARACTER SET latin1;
ALTER TABLE parent MODIFY pk VARCHAR(10) CHARACTER SET latin1;
DROP TABLE child;
# Adding new foreign key with discrepancies is not allowed
# even in FOREIGN_KEY_CHECKS=0 mode.
CREATE TABLE child (fk VARCHAR(20) CHARACTER SET utf8mb4,
FOREIGN KEY (fk) REFERENCES parent(pk));
ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_ibfk_1' are incompatible.
CREATE TABLE child (fk VARCHAR(20) CHARACTER SET utf8mb4);
ALTER TABLE child ADD FOREIGN KEY (fk) REFERENCES parent(pk);
ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_ibfk_1' are incompatible.
DROP TABLE child, parent;
# One cannot create such discrepancy when adding parent to
# orphan foreign key.
CREATE TABLE child (fk VARCHAR(20) CHARACTER SET latin1,
FOREIGN KEY (fk) REFERENCES parent(pk));
CREATE TABLE parent (pk VARCHAR(10) CHARACTER SET utf8mb4 PRIMARY KEY);
ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_ibfk_1' are incompatible.
CREATE TABLE parent0 (pk VARCHAR(10) CHARACTER SET utf8mb4 PRIMARY KEY);
RENAME TABLE parent0 TO parent;
ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_ibfk_1' are incompatible.
DROP TABLES child, parent0;
# This exception doesn't apply to binary strings though.
CREATE TABLE parent (pk VARCHAR(10) CHARACTER SET binary PRIMARY KEY);
CREATE TABLE child (fk VARCHAR(20) CHARACTER SET binary,
FOREIGN KEY (fk) REFERENCES parent(pk));
ALTER TABLE parent MODIFY pk VARCHAR(10) CHARACTER SET utf8mb4;
ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_ibfk_1' are incompatible.
ALTER TABLE child MODIFY fk VARCHAR(20) CHARACTER SET utf8mb4;
ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_ibfk_1' are incompatible.
SET FOREIGN_KEY_CHECKS=1;
DROP TABLES child, parent;
#
# WL#8910: Ensure foreign key error does not reveal information about
#          parent table for which user has no access privileges.
#
CREATE DATABASE wl8910db;
USE wl8910db;
#Set up tables.
CREATE TABLE t1(fld1 INT PRIMARY KEY, fld2 INT) ENGINE=INNODB;
CREATE TABLE t2(fld1 INT PRIMARY KEY, fld2 INT, CONSTRAINT fk2
FOREIGN KEY (fld1) REFERENCES t1 (fld1)) ENGINE=InnoDB;
CREATE TABLE t3(fld1 INT PRIMARY KEY, fld2 INT) ENGINE=InnoDB;
# Set up stored routines
CREATE PROCEDURE p1() SQL SECURITY INVOKER INSERT INTO t2 (fld1, fld2) VALUES (1, 2);
CREATE PROCEDURE p2() SQL SECURITY DEFINER INSERT INTO t2 (fld1, fld2) VALUES (1, 2);
CREATE FUNCTION f1() RETURNS INT SQL SECURITY INVOKER
BEGIN
INSERT INTO t2 (fld1, fld2) VALUES (1, 2);
RETURN 0;
END|
CREATE FUNCTION f2() RETURNS INT SQL SECURITY DEFINER
BEGIN
INSERT INTO t2 (fld1, fld2) VALUES (1, 2);
RETURN 0;
END|
# Set up views.
CREATE SQL SECURITY INVOKER VIEW v1 AS SELECT * FROM t2;
CREATE SQL SECURITY DEFINER VIEW v2 AS SELECT * FROM t2;
# Set up users and permissions.
CREATE USER user1@localhost;
CREATE USER user2@localhost;
CREATE USER user3@localhost;
GRANT INSERT (fld1, fld2) ON t2 TO user1@localhost;
GRANT INSERT ON v1 TO user2@localhost;
GRANT INSERT ON v2 TO user2@localhost;
GRANT SYSTEM_USER ON *.* TO user2@localhost;
GRANT SET_USER_ID ON *.* TO user2@localhost;
GRANT EXECUTE ON PROCEDURE p1 TO user2@localhost;
GRANT EXECUTE ON PROCEDURE p2 TO user2@localhost;
GRANT EXECUTE ON FUNCTION f1 TO user2@localhost;
GRANT EXECUTE ON FUNCTION f2 TO user2@localhost;
connect  con1, localhost, user1,,wl8910db;
# Without patch, reveals parent table's information.
INSERT INTO t2 (fld1, fld2) VALUES (1, 2);
ERROR 23000: Cannot add or update a child row: a foreign key constraint fails
# Warning displayed does not reveal parent table information.
INSERT IGNORE INTO t2 (fld1, fld2) VALUES (1, 2);
Warnings:
Warning	1216	Cannot add or update a child row: a foreign key constraint fails
SHOW WARNINGS;
Level	Code	Message
Warning	1216	Cannot add or update a child row: a foreign key constraint fails
connection default;
GRANT SELECT ON t1 TO user1@localhost;
connection con1;
INSERT INTO t2 (fld1, fld2) VALUES (1, 2);
ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`wl8910db`.`t2`, CONSTRAINT `fk2` FOREIGN KEY (`fld1`) REFERENCES `t1` (`fld1`))
connection default;
ALTER TABLE t2 ADD CONSTRAINT fk3 FOREIGN KEY (fld2) REFERENCES t3(fld1);
connection con1;
# Without patch, reveals parent table's information.
INSERT INTO t2 (fld1, fld2) VALUES (1, 2);
ERROR 23000: Cannot add or update a child row: a foreign key constraint fails
# Warning displayed does not reveal parent table information.
INSERT IGNORE INTO t2 (fld1, fld2) VALUES (1, 2);
Warnings:
Warning	1216	Cannot add or update a child row: a foreign key constraint fails
SHOW WARNINGS;
Level	Code	Message
Warning	1216	Cannot add or update a child row: a foreign key constraint fails
connection default;
GRANT SELECT ON t3 TO user1@localhost;
connection con1;
INSERT INTO t2 (fld1, fld2) VALUES (1, 2);
ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`wl8910db`.`t2`, CONSTRAINT `fk2` FOREIGN KEY (`fld1`) REFERENCES `t1` (`fld1`))
connection default;
GRANT INSERT (fld1, fld2) ON t2 TO user2@localhost;
GRANT CREATE ROUTINE ON wl8910db.* TO user2@localhost;
GRANT CREATE VIEW ON wl8910db.* TO user2@localhost;
# Tests where DML reports FK constraint failure within Stored Routines.
connect  con2, localhost, user2,,wl8910db;
# The SQL security for p1 is invoker where invoker lacks permission
# to parent table, hence parent table information is not displayed.
CALL p1();
ERROR 23000: Cannot add or update a child row: a foreign key constraint fails
# The SQL security p2 is definer, where the definer has access privilege
# to the parent table, hence parent table information is displayed.
CALL p2();
ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`wl8910db`.`t2`, CONSTRAINT `fk2` FOREIGN KEY (`fld1`) REFERENCES `t1` (`fld1`))
# The SQL security for f1 is invoker where invoker lacks permission
# to parent table, hence parent table information is not displayed.
SELECT f1();
ERROR 23000: Cannot add or update a child row: a foreign key constraint fails
# The SQL security f2 is definer, where the definer has access privilege
# to the parent table, hence parent table information is displayed.
SELECT f2();
ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`wl8910db`.`t2`, CONSTRAINT `fk2` FOREIGN KEY (`fld1`) REFERENCES `t1` (`fld1`))
# Test for DMLs on VIEWS.
# The invoker does not have access to the parent table, hence the parent
# table information is not displayed.
INSERT INTO v1 VALUES (1, 2);
ERROR 23000: Cannot add or update a child row: a foreign key constraint fails
# DML on view executed within the definer context where the invoker does
# not have access to the parent table, hence the parent table information
# is not displayed.
INSERT INTO v2 VALUES (1, 2);
ERROR 23000: Cannot add or update a child row: a foreign key constraint fails
connection default;
GRANT SELECT ON t1 TO user2@localhost;
GRANT SELECT ON t3 TO user2@localhost;
connection con2;
# DML on view executed within the definer context where the invoker
# has access to the parent table, hence the parent table information
# is displayed.
INSERT INTO v2 VALUES (1, 2);
ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`wl8910db`.`t2`, CONSTRAINT `fk2` FOREIGN KEY (`fld1`) REFERENCES `t1` (`fld1`))
# Tests with SET_USER_ID
# Set up stored routines and views by user with SET_USER_ID
# privilege.
CREATE DEFINER=root@localhost PROCEDURE p3() SQL SECURITY DEFINER
INSERT INTO t2 (fld1, fld2) VALUES (1, 2);
CREATE DEFINER=root@localhost FUNCTION f3() RETURNS
INT SQL SECURITY DEFINER
BEGIN
INSERT INTO t2 (fld1, fld2) VALUES (1, 2);
RETURN 0;
END|
CREATE DEFINER=root@localhost SQL SECURITY DEFINER VIEW v3 AS
SELECT * FROM t2;
# Grant privileges for user3.
connection default;
GRANT EXECUTE ON PROCEDURE p3 TO user3@localhost;
GRANT EXECUTE ON FUNCTION f3 TO user3@localhost;
GRANT INSERT ON v3 TO user3@localhost;
GRANT SELECT ON t1 TO user3@localhost;
GRANT SELECT ON t3 TO user3@localhost;
connect  con3, localhost, user3,,wl8910db;
# Since the execution happens in the definer 'root' user
# context, the parent table informaton is exposed.
CALL p3();
ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`wl8910db`.`t2`, CONSTRAINT `fk2` FOREIGN KEY (`fld1`) REFERENCES `t1` (`fld1`))
SELECT f3();
ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`wl8910db`.`t2`, CONSTRAINT `fk2` FOREIGN KEY (`fld1`) REFERENCES `t1` (`fld1`))
INSERT INTO v3 VALUES(4, 5);
ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`wl8910db`.`t2`, CONSTRAINT `fk2` FOREIGN KEY (`fld1`) REFERENCES `t1` (`fld1`))
# Cleanup
connection default;
disconnect con1;
disconnect con2;
disconnect con3;
DROP VIEW v1, v2, v3;
DROP TABLE t2, t3, t1;
DROP USER user1@localhost;
DROP USER user2@localhost;
DROP USER user3@localhost;
DROP PROCEDURE p1;
DROP PROCEDURE p2;
DROP PROCEDURE p3;
DROP FUNCTION f1;
DROP FUNCTION f2;
DROP FUNCTION f3;
DROP DATABASE wl8910db;
USE test;
#
# Bug #28122781  FOREIGN KEY REFERENCE OPTION RESTRICT IGNORED AFTER MYSQLDUMP AND RELOAD.
#
CREATE TABLE t1 (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
);
CREATE TABLE t2 (
t1_id INT NOT NULL,
CONSTRAINT t2_fk FOREIGN KEY (t1_id)
REFERENCES t1(id) ON UPDATE RESTRICT
);
CREATE TABLE t3 (
t1_id INT NOT NULL,
CONSTRAINT t3_fk FOREIGN KEY (t1_id)
REFERENCES t1(id) ON DELETE RESTRICT
);
CREATE TABLE t4 (
t1_id INT NOT NULL,
CONSTRAINT t4_fk FOREIGN KEY (t1_id)
REFERENCES t1(id) ON DELETE RESTRICT ON UPDATE RESTRICT
);
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `t1_id` int(11) NOT NULL,
  KEY `t2_fk` (`t1_id`),
  CONSTRAINT `t2_fk` FOREIGN KEY (`t1_id`) REFERENCES `t1` (`id`) ON UPDATE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
SHOW CREATE TABLE t3;
Table	Create Table
t3	CREATE TABLE `t3` (
  `t1_id` int(11) NOT NULL,
  KEY `t3_fk` (`t1_id`),
  CONSTRAINT `t3_fk` FOREIGN KEY (`t1_id`) REFERENCES `t1` (`id`) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
SHOW CREATE TABLE t4;
Table	Create Table
t4	CREATE TABLE `t4` (
  `t1_id` int(11) NOT NULL,
  KEY `t4_fk` (`t1_id`),
  CONSTRAINT `t4_fk` FOREIGN KEY (`t1_id`) REFERENCES `t1` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
SELECT *  FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS WHERE
TABLE_NAME IN ('t1', 't2', 't3', 't4');
CONSTRAINT_CATALOG	CONSTRAINT_SCHEMA	CONSTRAINT_NAME	UNIQUE_CONSTRAINT_CATALOG	UNIQUE_CONSTRAINT_SCHEMA	UNIQUE_CONSTRAINT_NAME	MATCH_OPTION	UPDATE_RULE	DELETE_RULE	TABLE_NAME	REFERENCED_TABLE_NAME
def	test	t2_fk	def	test	PRIMARY	NONE	RESTRICT	NO ACTION	t2	t1
def	test	t3_fk	def	test	PRIMARY	NONE	NO ACTION	RESTRICT	t3	t1
def	test	t4_fk	def	test	PRIMARY	NONE	RESTRICT	RESTRICT	t4	t1
# dump tables t1, t2, t3, t3
DROP TABLE t1, t2, t3, t4;
# reload dump
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `t1_id` int(11) NOT NULL,
  KEY `t2_fk` (`t1_id`),
  CONSTRAINT `t2_fk` FOREIGN KEY (`t1_id`) REFERENCES `t1` (`id`) ON UPDATE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
SHOW CREATE TABLE t3;
Table	Create Table
t3	CREATE TABLE `t3` (
  `t1_id` int(11) NOT NULL,
  KEY `t3_fk` (`t1_id`),
  CONSTRAINT `t3_fk` FOREIGN KEY (`t1_id`) REFERENCES `t1` (`id`) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
SHOW CREATE TABLE t4;
Table	Create Table
t4	CREATE TABLE `t4` (
  `t1_id` int(11) NOT NULL,
  KEY `t4_fk` (`t1_id`),
  CONSTRAINT `t4_fk` FOREIGN KEY (`t1_id`) REFERENCES `t1` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
SELECT *  FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS WHERE
TABLE_NAME IN ('t1', 't2', 't3', 't4');
CONSTRAINT_CATALOG	CONSTRAINT_SCHEMA	CONSTRAINT_NAME	UNIQUE_CONSTRAINT_CATALOG	UNIQUE_CONSTRAINT_SCHEMA	UNIQUE_CONSTRAINT_NAME	MATCH_OPTION	UPDATE_RULE	DELETE_RULE	TABLE_NAME	REFERENCED_TABLE_NAME
def	test	t2_fk	def	test	PRIMARY	NONE	RESTRICT	NO ACTION	t2	t1
def	test	t3_fk	def	test	PRIMARY	NONE	NO ACTION	RESTRICT	t3	t1
def	test	t4_fk	def	test	PRIMARY	NONE	RESTRICT	RESTRICT	t4	t1
DROP TABLE t1, t2, t3, t4;
#
# Bug #27353767 "FOREIGN KEY IS ALWAYS IN LOWER CASE".
#
# Check that the way in which referencing and referenced column names in
# foreign key definition are stored and shown in SHOW CREATE TABLE output
# and I_S tables is with 5.7 and earlier versions.
#
# We always use version of name of referencing column which comes from
# the definition of referencing table and not version from FOREIGN KEY
# clause.
# If referenced table exists than we use version of referenced column
# name which comes from its definition, and not version from FOREIGN KEY
# clause.
CREATE TABLE parent (Pk VARCHAR(10) PRIMARY KEY);
CREATE TABLE child (Fk VARCHAR(10), FOREIGN KEY (fK) REFERENCES parent(pK));
SHOW CREATE TABLE child;
Table	Create Table
child	CREATE TABLE `child` (
  `Fk` varchar(10) DEFAULT NULL,
  KEY `Fk` (`Fk`),
  CONSTRAINT `child_ibfk_1` FOREIGN KEY (`Fk`) REFERENCES `parent` (`Pk`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
SELECT column_name, referenced_column_name FROM information_schema.key_column_usage
WHERE referenced_table_schema='test' AND referenced_table_name='parent';
column_name	referenced_column_name
Fk	Pk
DROP TABLE child;
CREATE TABLE child (Fk VARCHAR(10));
ALTER TABLE child ADD FOREIGN KEY (fK) REFERENCES parent(pK);
SHOW CREATE TABLE child;
Table	Create Table
child	CREATE TABLE `child` (
  `Fk` varchar(10) DEFAULT NULL,
  KEY `Fk` (`Fk`),
  CONSTRAINT `child_ibfk_1` FOREIGN KEY (`Fk`) REFERENCES `parent` (`Pk`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
SELECT column_name, referenced_column_name FROM information_schema.key_column_usage
WHERE referenced_table_schema='test' AND referenced_table_name='parent';
column_name	referenced_column_name
Fk	Pk
# Ditto for self-referencing tables.
CREATE TABLE self (Pk VARCHAR(10) PRIMARY KEY, Fk VARCHAR(10),
FOREIGN KEY (fK) REFERENCES self(pK));
SHOW CREATE TABLE self;
Table	Create Table
self	CREATE TABLE `self` (
  `Pk` varchar(10) NOT NULL,
  `Fk` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`Pk`),
  KEY `Fk` (`Fk`),
  CONSTRAINT `self_ibfk_1` FOREIGN KEY (`Fk`) REFERENCES `self` (`Pk`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
SELECT column_name, referenced_column_name FROM information_schema.key_column_usage
WHERE referenced_table_schema='test' AND referenced_table_name='self';
column_name	referenced_column_name
Fk	Pk
DROP TABLE self;
CREATE TABLE self (Pk VARCHAR(10) PRIMARY KEY, Fk VARCHAR(10));
ALTER TABLE self ADD FOREIGN KEY (fK) REFERENCES self(pK);
SHOW CREATE TABLE self;
Table	Create Table
self	CREATE TABLE `self` (
  `Pk` varchar(10) NOT NULL,
  `Fk` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`Pk`),
  KEY `Fk` (`Fk`),
  CONSTRAINT `self_ibfk_1` FOREIGN KEY (`Fk`) REFERENCES `self` (`Pk`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
SELECT column_name, referenced_column_name FROM information_schema.key_column_usage
WHERE referenced_table_schema='test' AND referenced_table_name='self';
column_name	referenced_column_name
Fk	Pk
#
# If column name is changed (even to equivalent one) then the name shown
# in foreign key definition is updated too.
ALTER TABLE parent CHANGE COLUMN Pk PK VARCHAR(20);
SHOW CREATE TABLE child;
Table	Create Table
child	CREATE TABLE `child` (
  `Fk` varchar(10) DEFAULT NULL,
  KEY `Fk` (`Fk`),
  CONSTRAINT `child_ibfk_1` FOREIGN KEY (`Fk`) REFERENCES `parent` (`PK`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
SELECT column_name, referenced_column_name FROM information_schema.key_column_usage
WHERE referenced_table_schema='test' AND referenced_table_name='parent';
column_name	referenced_column_name
Fk	PK
ALTER TABLE child CHANGE COLUMN Fk FK VARCHAR(20);
SHOW CREATE TABLE child;
Table	Create Table
child	CREATE TABLE `child` (
  `FK` varchar(20) DEFAULT NULL,
  KEY `Fk` (`FK`),
  CONSTRAINT `child_ibfk_1` FOREIGN KEY (`FK`) REFERENCES `parent` (`PK`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
SELECT column_name, referenced_column_name FROM information_schema.key_column_usage
WHERE referenced_table_schema='test' AND referenced_table_name='parent';
column_name	referenced_column_name
FK	PK
# Ditto for self-referencing tables.
ALTER TABLE self CHANGE COLUMN Pk PK VARCHAR(20);
SHOW CREATE TABLE self;
Table	Create Table
self	CREATE TABLE `self` (
  `PK` varchar(20) NOT NULL,
  `Fk` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`PK`),
  KEY `Fk` (`Fk`),
  CONSTRAINT `self_ibfk_1` FOREIGN KEY (`Fk`) REFERENCES `self` (`PK`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
SELECT column_name, referenced_column_name FROM information_schema.key_column_usage
WHERE referenced_table_schema='test' AND referenced_table_name='self';
column_name	referenced_column_name
Fk	PK
ALTER TABLE self CHANGE COLUMN Fk FK VARCHAR(20);
SHOW CREATE TABLE self;
Table	Create Table
self	CREATE TABLE `self` (
  `PK` varchar(20) NOT NULL,
  `FK` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`PK`),
  KEY `Fk` (`FK`),
  CONSTRAINT `self_ibfk_1` FOREIGN KEY (`FK`) REFERENCES `self` (`PK`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
SELECT column_name, referenced_column_name FROM information_schema.key_column_usage
WHERE referenced_table_schema='test' AND referenced_table_name='self';
column_name	referenced_column_name
FK	PK
DROP TABLE self;
#
# Referenced column names are preserved when referenced table is dropped
# (which is possible in FOREIGN_KEY_CHECKS = 0 mode).
SET FOREIGN_KEY_CHECKS=0;
DROP TABLE parent;
SHOW CREATE TABLE child;
Table	Create Table
child	CREATE TABLE `child` (
  `FK` varchar(20) DEFAULT NULL,
  KEY `Fk` (`FK`),
  CONSTRAINT `child_ibfk_1` FOREIGN KEY (`FK`) REFERENCES `parent` (`PK`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
SELECT column_name, referenced_column_name FROM information_schema.key_column_usage
WHERE referenced_table_schema='test' AND referenced_table_name='parent';
column_name	referenced_column_name
FK	PK
#
# Addition of parent doesn't change the referenced column names.
CREATE TABLE parent (pk VARCHAR(10) PRIMARY KEY);
SHOW CREATE TABLE child;
Table	Create Table
child	CREATE TABLE `child` (
  `FK` varchar(20) DEFAULT NULL,
  KEY `Fk` (`FK`),
  CONSTRAINT `child_ibfk_1` FOREIGN KEY (`FK`) REFERENCES `parent` (`PK`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
SELECT column_name, referenced_column_name FROM information_schema.key_column_usage
WHERE referenced_table_schema='test' AND referenced_table_name='parent';
column_name	referenced_column_name
FK	PK
DROP TABLES child, parent;
#
# We use version of referenced column name coming from FOREIGN KEY clause
# when orphan foreign key is created (as referenced table doesn't exist).
#
# Oddly, 5.7 uses version of referencing column name coming from FOREIGN
# KEY clause when orphan FK is added via ALTER TABLE (but not CREATE
# TABLE). This means that in 5.7 there is discrepancy between CREATE and
# ALTER TABLE behavior in this case.
# In 8.0 we are consistent and stick to CREATE TABLE behavior in such
# cases.
CREATE TABLE child (Fk VARCHAR(10), FOREIGN KEY (fK) REFERENCES parent(pK));
SHOW CREATE TABLE child;
Table	Create Table
child	CREATE TABLE `child` (
  `Fk` varchar(10) DEFAULT NULL,
  KEY `Fk` (`Fk`),
  CONSTRAINT `child_ibfk_1` FOREIGN KEY (`Fk`) REFERENCES `parent` (`pK`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
SELECT column_name, referenced_column_name FROM information_schema.key_column_usage
WHERE referenced_table_schema='test' AND referenced_table_name='parent';
column_name	referenced_column_name
Fk	pK
DROP TABLE child;
CREATE TABLE child (Fk VARCHAR(10));
ALTER TABLE child ADD FOREIGN KEY (fK) REFERENCES parent(pK);
SHOW CREATE TABLE child;
Table	Create Table
child	CREATE TABLE `child` (
  `Fk` varchar(10) DEFAULT NULL,
  KEY `Fk` (`Fk`),
  CONSTRAINT `child_ibfk_1` FOREIGN KEY (`Fk`) REFERENCES `parent` (`pK`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
SELECT column_name, referenced_column_name FROM information_schema.key_column_usage
WHERE referenced_table_schema='test' AND referenced_table_name='parent';
column_name	referenced_column_name
Fk	pK
#
# Addition of parent doesn't change the referenced column names.
CREATE TABLE parent (pk VARCHAR(10) PRIMARY KEY);
SHOW CREATE TABLE child;
Table	Create Table
child	CREATE TABLE `child` (
  `Fk` varchar(10) DEFAULT NULL,
  KEY `Fk` (`Fk`),
  CONSTRAINT `child_ibfk_1` FOREIGN KEY (`Fk`) REFERENCES `parent` (`pK`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
SELECT column_name, referenced_column_name FROM information_schema.key_column_usage
WHERE referenced_table_schema='test' AND referenced_table_name='parent';
column_name	referenced_column_name
Fk	pK
SET FOREIGN_KEY_CHECKS=1;
DROP TABLES child, parent;
#
# Test for bug#29173134 "FOREIGN KEY CONSTRAINT NAMES TAKING INDEX NAME".
#
#
# Original test case.
CREATE TABLE t1 (id INT PRIMARY KEY);
CREATE TABLE t2 (t1id INT, FOREIGN KEY fk_index (t1id) REFERENCES t1 (id));
CREATE TABLE t3 (t1id INT, FOREIGN KEY fk_index (t1id) REFERENCES t1 (id));
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `t1id` int(11) DEFAULT NULL,
  KEY `fk_index` (`t1id`),
  CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`t1id`) REFERENCES `t1` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
SHOW CREATE TABLE t3;
Table	Create Table
t3	CREATE TABLE `t3` (
  `t1id` int(11) DEFAULT NULL,
  KEY `fk_index` (`t1id`),
  CONSTRAINT `t3_ibfk_1` FOREIGN KEY (`t1id`) REFERENCES `t1` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
SELECT CONSTRAINT_NAME, TABLE_NAME
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
WHERE TABLE_NAME IN ('t2', 't3') ORDER BY CONSTRAINT_NAME;
CONSTRAINT_NAME	TABLE_NAME
t2_ibfk_1	t2
t3_ibfk_1	t3
#
# Let us check what foreign key names are used in different situations.
CREATE TABLE t4 (fk1 INT, fk2 INT, fk3 INT, fk4 INT,
FOREIGN KEY (fk1) REFERENCES t1 (id),
CONSTRAINT c FOREIGN KEY (fk2) REFERENCES t1 (id),
FOREIGN KEY d (fk3) REFERENCES t1 (id),
CONSTRAINT e FOREIGN KEY f (fk4) REFERENCES t1 (id));
SHOW CREATE TABLE t4;
Table	Create Table
t4	CREATE TABLE `t4` (
  `fk1` int(11) DEFAULT NULL,
  `fk2` int(11) DEFAULT NULL,
  `fk3` int(11) DEFAULT NULL,
  `fk4` int(11) DEFAULT NULL,
  KEY `fk1` (`fk1`),
  KEY `c` (`fk2`),
  KEY `d` (`fk3`),
  KEY `e` (`fk4`),
  CONSTRAINT `c` FOREIGN KEY (`fk2`) REFERENCES `t1` (`id`),
  CONSTRAINT `e` FOREIGN KEY (`fk4`) REFERENCES `t1` (`id`),
  CONSTRAINT `t4_ibfk_1` FOREIGN KEY (`fk1`) REFERENCES `t1` (`id`),
  CONSTRAINT `t4_ibfk_2` FOREIGN KEY (`fk3`) REFERENCES `t1` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
SELECT CONSTRAINT_NAME FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
WHERE TABLE_NAME = 't4' ORDER BY CONSTRAINT_NAME;
CONSTRAINT_NAME
c
e
t4_ibfk_1
t4_ibfk_2
# Clean-up.
DROP TABLES t1, t2, t3, t4;
#
# Bug #11756183 "FOREIGN KEYS MAY DISAPPEAR AFTER ALTER TABLE RENAME".
# Bug #18713399 "FK CHILD TABLE CANNOT BE CREATED: PROBLEMS AFTER TABLE RENAME"
#
CREATE TABLE parent (pk INT PRIMARY KEY);
INSERT INTO parent VALUES (1);
CREATE TABLE child (fk INT, b INT, FOREIGN KEY (fk) REFERENCES parent (pk));
INSERT INTO child VALUES (1, 1);
SHOW CREATE TABLE child;
Table	Create Table
child	CREATE TABLE `child` (
  `fk` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  KEY `fk` (`fk`),
  CONSTRAINT `child_ibfk_1` FOREIGN KEY (`fk`) REFERENCES `parent` (`pk`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
# We use ALGORITHM=COPY to ensure that the below ALTER is executed
# using COPY algorithm even if changing column datatype becomes
# in-place operation.
ALTER TABLE child MODIFY COLUMN b BIGINT, RENAME TO child_renamed, ALGORITHM=COPY;
# The foreign key should be still in SHOW CREATE TABLE output.
SHOW CREATE TABLE child_renamed;
Table	Create Table
child_renamed	CREATE TABLE `child_renamed` (
  `fk` int(11) DEFAULT NULL,
  `b` bigint(20) DEFAULT NULL,
  KEY `fk` (`fk`),
  CONSTRAINT `child_renamed_ibfk_1` FOREIGN KEY (`fk`) REFERENCES `parent` (`pk`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
# Removal of parent row should not be allowed.
DELETE FROM parent WHERE pk = 1;
ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`child_renamed`, CONSTRAINT `child_renamed_ibfk_1` FOREIGN KEY (`fk`) REFERENCES `parent` (`pk`))
DROP TABLE child_renamed;
# Part of test covering bug#18713399. The below CREATE TABLE statement
# should not fail due to duplicate foreign key name.
CREATE TABLE child (fk INT, b INT, FOREIGN KEY (fk) REFERENCES parent (pk));
DROP TABLES child, parent;
#
# Bug #18199504 "AUTO-NAMING OF FOREIGN KEYS SEEMS BROKEN WHEN
#                MULTI-OPERATION ALTER IS USED".
#
CREATE TABLE parent (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, c CHAR(32));
CREATE TABLE uncle (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, c CHAR(32));
CREATE TABLE child (parent_id INT, c CHAR(32), FOREIGN KEY (parent_id) REFERENCES parent (id));
# Turn off foreign key checking so we can add foreign key using
# inplace algorithm.
SET FOREIGN_KEY_CHECKS=0;
# The problem occured when a foreign key was added by ALTER TABLE
# executed using inplace algorithm which still did table rebuild
# internally. So we drop column to make operation non-instant for
# sure.
ALTER TABLE child ADD COLUMN uncle_id INT, DROP COLUMN c, ADD CONSTRAINT FOREIGN KEY (uncle_id) REFERENCES uncle (id), ALGORITHM=INPLACE;
SET FOREIGN_KEY_CHECKS=1;
SHOW CREATE TABLE child;
Table	Create Table
child	CREATE TABLE `child` (
  `parent_id` int(11) DEFAULT NULL,
  `uncle_id` int(11) DEFAULT NULL,
  KEY `parent_id` (`parent_id`),
  KEY `uncle_id` (`uncle_id`),
  CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`),
  CONSTRAINT `child_ibfk_2` FOREIGN KEY (`uncle_id`) REFERENCES `uncle` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
DROP TABLES child, parent, uncle;
#
# Bug #28480149 "UPGRADE FAIL: FAILED TO ADD THE FOREIGN KEY
#                CONSTRAINT. MISSING INDEX FOR CONSTR".
#
# When InnoDB SE checks if a key can serve as a parent key for a foreign
# key it takes into account columns from hidden part of the key. This
# hidden part consists of primary key columns which are implicitly
# added by InnoDB to non-primary keys.
#
# See dd_upgrade_test test for coverage of upgrade process for such
# foreign keys.
#
CREATE TABLE parent (pk INT PRIMARY KEY, a INT, b INT, KEY(a), UNIQUE(b));
# This works both for non-unique and unique keys.
CREATE TABLE child1 (fk1 INT, fk2 INT, FOREIGN KEY (fk1, fk2) REFERENCES parent (a, pk));
CREATE TABLE child2 (fk1 INT, fk2 INT, FOREIGN KEY (fk1, fk2) REFERENCES parent (b, pk));
DROP TABLES child1, child2;
# This also works when foreign keys are added by ALTER TABLE.
CREATE TABLE child1 (fk1 INT, fk2 INT);
ALTER TABLE child1 ADD FOREIGN KEY (fk1, fk2) REFERENCES parent (a, pk);
CREATE TABLE child2 (fk1 INT, fk2 INT);
ALTER TABLE child2 ADD FOREIGN KEY (fk1, fk2) REFERENCES parent (b, pk);
DROP TABLES child1, child2, parent;
# And for complex multi-column cases too.
CREATE TABLE parent (a INT, b INT, c INT, PRIMARY KEY (a,b), KEY(c, a));
CREATE TABLE child (fk1 INT, fk2 INT, fk3 INT, FOREIGN KEY (fk1, fk2, fk3) REFERENCES parent (c, a, b));
DROP TABLES child, parent;
# Moreover, this works for keys which are promoted to primary.
CREATE TABLE parent (u INT NOT NULL, a INT, b INT, UNIQUE(u), KEY(a), UNIQUE(b));
CREATE TABLE child1 (fk1 INT, fk2 INT, FOREIGN KEY (fk1, fk2) REFERENCES parent (a, u));
CREATE TABLE child2 (fk1 INT, fk2 INT, FOREIGN KEY (fk1, fk2) REFERENCES parent (b, u));
DROP TABLES child1, child2, parent;
# Also works correctly when parent table is altered.
CREATE TABLE parent (pk INT PRIMARY KEY, a INT, KEY k1(a, pk), UNIQUE k2(a));
CREATE TABLE child (fk1 INT, fk2 INT, FOREIGN KEY (fk1, fk2) REFERENCES parent (a, pk));
ALTER TABLE parent DROP KEY k1;
DROP TABLES child, parent;
# And when we add parent table to previously orphan foreign key.
SET FOREIGN_KEY_CHECKS=0;
CREATE TABLE child (fk1 INT, fk2 INT, CONSTRAINT c FOREIGN KEY (fk1, fk2) REFERENCES parent (a, pk));
SET FOREIGN_KEY_CHECKS=1;
CREATE TABLE parent (pk INT PRIMARY KEY, a INT, UNIQUE ua(a));
SELECT constraint_name, unique_constraint_name FROM information_schema.referential_constraints
WHERE constraint_schema='test' AND constraint_name='c';
CONSTRAINT_NAME	UNIQUE_CONSTRAINT_NAME
c	ua
DROP TABLES child, parent;
# However, columns which are implicitly added by InnoDB as hidden
# elements to the primary key are not considered when searching for
# parent key (because primary key contains hidden system DB_TRX_ID,
# DB_ROLL_PTR columns before hidden normal columns).
CREATE TABLE parent (pk INT PRIMARY KEY, a INT);
CREATE TABLE child (fk1 INT, fk2 INT, FOREIGN KEY (fk1, fk2) REFERENCES parent (pk, a));
ERROR HY000: Failed to add the foreign key constraint. Missing index for constraint 'child_ibfk_1' in the referenced table 'parent'
DROP TABLE parent;
# Hidden parts which are column prefixes (because primary key has prefix
# parts [sic!]) are not allowed as parent key parts.
CREATE TABLE parent (a CHAR(10), b int, KEY(b), PRIMARY KEY (a(5)));
CREATE TABLE child (fk1 int, fk2 CHAR(10), FOREIGN KEY (fk1, fk2) REFERENCES parent (b, a));
ERROR HY000: Failed to add the foreign key constraint. Missing index for constraint 'child_ibfk_1' in the referenced table 'parent'
DROP TABLE parent;
# Moreover, even hidden parts for full columns are not allowed if
# primary key contains prefix parts. This was supported in 5.7.
CREATE TABLE parent (a INT, b CHAR(10), c int, KEY(c), PRIMARY KEY (a, b(5)));
CREATE TABLE child (fk1 int, fk2 int, FOREIGN KEY (fk1, fk2) REFERENCES parent (c, a));
ERROR HY000: Failed to add the foreign key constraint. Missing index for constraint 'child_ibfk_1' in the referenced table 'parent'
DROP TABLE parent;
#
# Now similar tests for self-referencing foreign keys.
#
CREATE TABLE self1 (pk INT PRIMARY KEY, a INT, fk1 INT, fk2 INT,
KEY(a), FOREIGN KEY (fk1, fk2) REFERENCES self1 (a, pk));
CREATE TABLE self2 (pk INT PRIMARY KEY, b INT, fk1 INT, fk2 INT,
UNIQUE(b), FOREIGN KEY (fk1, fk2) REFERENCES self2 (b, pk));
DROP TABLES self1, self2;
CREATE TABLE self1 (pk INT PRIMARY KEY, a INT, fk1 INT, fk2 INT, KEY(a));
ALTER TABLE self1 ADD FOREIGN KEY (fk1, fk2) REFERENCES self1 (a, pk);
CREATE TABLE self2 (pk INT PRIMARY KEY, b INT, fk1 INT, fk2 INT, UNIQUE(b));
ALTER TABLE self2 ADD FOREIGN KEY (fk1, fk2) REFERENCES self2 (b, pk);
DROP TABLES self1, self2;
# Test for complex multi-column case.
CREATE TABLE self (a INT, b INT, c INT, fk1 INT, fk2 INT, fk3 INT,
PRIMARY KEY (a,b), KEY(c, a),
FOREIGN KEY (fk1, fk2, fk3) REFERENCES self (c, a, b));
DROP TABLE self;
# Test for removing parent key.
CREATE TABLE self (pk INT PRIMARY KEY, a INT, fk1 INT, fk2 INT,
KEY k1(a, pk), UNIQUE k2(a),
FOREIGN KEY (fk1, fk2) REFERENCES self (a, pk));
ALTER TABLE self DROP KEY k1;
DROP TABLE self;
# But again this is not supposed to work for hidden columns added to
# primary key.
CREATE TABLE self (pk INT PRIMARY KEY, a INT, fk1 INT, fk2 INT,
FOREIGN KEY (fk1, fk2) REFERENCES self (pk, a));
ERROR HY000: Failed to add the foreign key constraint. Missing index for constraint 'self_ibfk_1' in the referenced table 'self'
# Hidden parts which are column prefixes should not work.
CREATE TABLE self (a CHAR(10), b int, fk1 int, fk2 CHAR(10), KEY(b),
PRIMARY KEY (a(5)), FOREIGN KEY (fk1, fk2) REFERENCES self (b, a));
ERROR HY000: Failed to add the foreign key constraint. Missing index for constraint 'self_ibfk_1' in the referenced table 'self'
# Hidden parts for full columns are not allowed either if
# primary key contains prefix parts. This was supported in 5.7.
CREATE TABLE self (a INT, b CHAR(10), c int, fk1 int, fk2 int, KEY(c),
PRIMARY KEY (a, b(5)), FOREIGN KEY (fk1, fk2) REFERENCES self (c, a));
ERROR HY000: Failed to add the foreign key constraint. Missing index for constraint 'self_ibfk_1' in the referenced table 'self'
#
# Bug#21308781 "DROP FOREIGN KEY LEAD TO INCONSISTENT TABLE STRUCTURE
#               ON MASTER AND SLAVE".
#
CREATE TABLE t1(pk INT PRIMARY KEY);
CREATE TABLE t2(pk INT PRIMARY KEY);
CREATE TABLE t3(fk1 INT, fk2 INT, KEY k1(fk1),
CONSTRAINT a FOREIGN KEY (fk1) REFERENCES t1(pk),
CONSTRAINT b FOREIGN KEY (fk2) REFERENCES t2(pk));
ALTER TABLE t3 DROP KEY k1, DROP FOREIGN KEY b, ALGORITHM=COPY;
ERROR HY000: Cannot drop index 'k1': needed in a foreign key constraint
# Failed ALTER TABLE should have left table intact!
SHOW CREATE TABLE t3;
Table	Create Table
t3	CREATE TABLE `t3` (
  `fk1` int(11) DEFAULT NULL,
  `fk2` int(11) DEFAULT NULL,
  KEY `k1` (`fk1`),
  KEY `b` (`fk2`),
  CONSTRAINT `a` FOREIGN KEY (`fk1`) REFERENCES `t1` (`pk`),
  CONSTRAINT `b` FOREIGN KEY (`fk2`) REFERENCES `t2` (`pk`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
DROP TABLES t3, t2, t1;