mirror of
https://github.com/MariaDB/server.git
synced 2025-07-22 03:08:18 +02:00
cffbb17480
Before MySQL 4.0.18, user-specified constraint names were ignored. Starting with MySQL 4.0.18, the specified constraint name was prepended with the schema name and '/'. Now we are transforming into a format where the constraint name is prepended with the dict_table_t::name and the impossible UTF-8 sequence 0xff. Generated constraint names will be ASCII decimal numbers. On upgrade, old FOREIGN KEY constraint names will be displayed without any schema name prefix. They will be updated to the new format on DDL operations. dict_foreign_t::sql_id(): Return the SQL constraint name without any schemaname/tablename\377 or schemaname/ prefix. row_rename_table_for_mysql(), dict_table_rename_in_cache(): Simplify the logic: Just rename constraints to the new format. dict_table_get_foreign_id(): Replaces dict_table_get_highest_foreign_id(). innobase_get_foreign_key_info(): Let my_error() refer to erroneous anonymous constraints as "(null)". row_delete_constraint(): Try to drop all 3 constraint name variants. Reviewed by: Thirunarayanan Balathandayuthapani Tested by: Matthias Leich
678 lines
25 KiB
Text
678 lines
25 KiB
Text
CREATE TABLE parent (a INT PRIMARY KEY, b INT NOT NULL) ENGINE = InnoDB;
|
|
INSERT INTO parent VALUES(1,2),(2,3);
|
|
CREATE INDEX tb ON parent(b);
|
|
INSERT INTO parent VALUES(10,20),(20,30);
|
|
CREATE TABLE child (a1 INT PRIMARY KEY, a2 INT) ENGINE = InnoDB;
|
|
CREATE INDEX tb ON child(a2);
|
|
INSERT INTO child VALUES(10,20);
|
|
ALTER TABLE child ADD FOREIGN KEY(a2) REFERENCES parent(b),
|
|
ALGORITHM = INPLACE;
|
|
ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: Adding foreign keys needs foreign_key_checks=OFF. Try ALGORITHM=COPY
|
|
SET foreign_key_checks = 0;
|
|
ALTER TABLE child ADD CONSTRAINT fk_1 FOREIGN KEY (a2)
|
|
REFERENCES parent(b) ON DELETE SET NULL ON UPDATE CASCADE,
|
|
ALGORITHM = INPLACE;
|
|
SELECT * FROM information_schema.INNODB_SYS_FOREIGN;
|
|
ID FOR_NAME REF_NAME N_COLS TYPE
|
|
fk_1 test/child test/parent 1 6
|
|
SELECT * FROM information_schema.INNODB_SYS_FOREIGN_COLS;
|
|
ID FOR_COL_NAME REF_COL_NAME POS
|
|
fk_1 a2 b 0
|
|
ALTER TABLE child ADD CONSTRAINT fk_1 FOREIGN KEY (a2)
|
|
REFERENCES parent(b) ON DELETE SET NULL ON UPDATE CASCADE,
|
|
ALGORITHM = INPLACE;
|
|
ERROR HY000: Duplicate FOREIGN KEY constraint name 'fk_1'
|
|
SET foreign_key_checks = 1;
|
|
INSERT INTO child VALUES(1,2),(2,3);
|
|
INSERT INTO child VALUES(4,4);
|
|
ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`child`, CONSTRAINT `fk_1` FOREIGN KEY (`a2`) REFERENCES `parent` (`b`) ON DELETE SET NULL ON UPDATE CASCADE)
|
|
SELECT * FROM parent;
|
|
a b
|
|
1 2
|
|
2 3
|
|
10 20
|
|
20 30
|
|
SET foreign_key_checks = 0;
|
|
ALTER TABLE child ADD CONSTRAINT fk_20 FOREIGN KEY (a1, a2)
|
|
REFERENCES parent(a, b) ON DELETE CASCADE ON UPDATE CASCADE,
|
|
ALGORITHM = INPLACE;
|
|
ERROR HY000: Failed to add the foreign key constraint. Missing index for constraint 'fk_20' in the referenced table 'parent'
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
Error 1822 Failed to add the foreign key constraint. Missing index for constraint 'fk_20' in the referenced table 'parent'
|
|
SHOW ERRORS;
|
|
Level Code Message
|
|
Error 1822 Failed to add the foreign key constraint. Missing index for constraint 'fk_20' in the referenced table 'parent'
|
|
CREATE INDEX idx1 on parent(a, b);
|
|
ALTER TABLE child ADD CONSTRAINT fk_10 FOREIGN KEY (a1, a2)
|
|
REFERENCES parent(a, b) ON DELETE CASCADE ON UPDATE CASCADE,
|
|
ALGORITHM = INPLACE;
|
|
ALTER TABLE child ADD CONSTRAINT fk_2 FOREIGN KEY (a1, a2)
|
|
REFERENCES parent(a, b) ON DELETE CASCADE ON UPDATE CASCADE, ADD INDEX idx1(a1,a2),
|
|
ALGORITHM = INPLACE;
|
|
ALTER TABLE child ADD CONSTRAINT fk_3 FOREIGN KEY (a1, a2)
|
|
REFERENCES parent(a, b) ON DELETE CASCADE ON UPDATE CASCADE;
|
|
SELECT * FROM information_schema.INNODB_SYS_FOREIGN;
|
|
ID FOR_NAME REF_NAME N_COLS TYPE
|
|
fk_1 test/child test/parent 1 6
|
|
fk_10 test/child test/parent 2 5
|
|
fk_2 test/child test/parent 2 5
|
|
fk_3 test/child test/parent 2 5
|
|
SELECT * FROM information_schema.INNODB_SYS_FOREIGN_COLS;
|
|
ID FOR_COL_NAME REF_COL_NAME POS
|
|
fk_1 a2 b 0
|
|
fk_10 a1 a 0
|
|
fk_10 a2 b 1
|
|
fk_2 a1 a 0
|
|
fk_2 a2 b 1
|
|
fk_3 a1 a 0
|
|
fk_3 a2 b 1
|
|
SET foreign_key_checks = 1;
|
|
INSERT INTO child VALUES(5,4);
|
|
ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`child`, CONSTRAINT `fk_1` FOREIGN KEY (`a2`) REFERENCES `parent` (`b`) ON DELETE SET NULL ON UPDATE CASCADE)
|
|
SHOW CREATE TABLE child;
|
|
Table Create Table
|
|
child CREATE TABLE `child` (
|
|
`a1` int(11) NOT NULL,
|
|
`a2` int(11) DEFAULT NULL,
|
|
PRIMARY KEY (`a1`),
|
|
KEY `tb` (`a2`),
|
|
KEY `idx1` (`a1`,`a2`),
|
|
CONSTRAINT `fk_1` FOREIGN KEY (`a2`) REFERENCES `parent` (`b`) ON DELETE SET NULL ON UPDATE CASCADE,
|
|
CONSTRAINT `fk_10` FOREIGN KEY (`a1`, `a2`) REFERENCES `parent` (`a`, `b`) ON DELETE CASCADE ON UPDATE CASCADE,
|
|
CONSTRAINT `fk_2` FOREIGN KEY (`a1`, `a2`) REFERENCES `parent` (`a`, `b`) ON DELETE CASCADE ON UPDATE CASCADE,
|
|
CONSTRAINT `fk_3` FOREIGN KEY (`a1`, `a2`) REFERENCES `parent` (`a`, `b`) ON DELETE CASCADE ON UPDATE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
|
|
DELETE FROM parent where a = 1;
|
|
SELECT * FROM child;
|
|
a1 a2
|
|
1 NULL
|
|
2 3
|
|
10 20
|
|
SET foreign_key_checks = 0;
|
|
SET @saved_debug_dbug = @@SESSION.debug_dbug;
|
|
SET DEBUG_DBUG = '+d,innodb_test_open_ref_fail';
|
|
ALTER TABLE child ADD CONSTRAINT fk_4 FOREIGN KEY (a1, a2)
|
|
REFERENCES parent(a, b) ON DELETE CASCADE ON UPDATE CASCADE,
|
|
ALGORITHM = INPLACE;
|
|
SET DEBUG_DBUG = @saved_debug_dbug;
|
|
SELECT * FROM information_schema.INNODB_SYS_FOREIGN;
|
|
ID FOR_NAME REF_NAME N_COLS TYPE
|
|
fk_1 test/child test/parent 1 6
|
|
fk_10 test/child test/parent 2 5
|
|
fk_2 test/child test/parent 2 5
|
|
fk_3 test/child test/parent 2 5
|
|
fk_4 test/child test/parent 2 5
|
|
SELECT * FROM information_schema.INNODB_SYS_FOREIGN_COLS;
|
|
ID FOR_COL_NAME REF_COL_NAME POS
|
|
fk_1 a2 b 0
|
|
fk_10 a1 a 0
|
|
fk_10 a2 b 1
|
|
fk_2 a1 a 0
|
|
fk_2 a2 b 1
|
|
fk_3 a1 a 0
|
|
fk_3 a2 b 1
|
|
fk_4 a1 a 0
|
|
fk_4 a2 b 1
|
|
SELECT t2.name, t1.name FROM information_schema.innodb_sys_columns t1, information_schema.innodb_sys_tables t2 WHERE t1.table_id = t2.table_id AND t2.name LIKE "%child" ORDER BY t1.name;
|
|
name name
|
|
test/child a1
|
|
test/child a2
|
|
SELECT NAME FROM information_schema.INNODB_SYS_TABLES;
|
|
NAME
|
|
SYS_FOREIGN
|
|
SYS_FOREIGN_COLS
|
|
SYS_VIRTUAL
|
|
mysql/innodb_index_stats
|
|
mysql/innodb_table_stats
|
|
mysql/transaction_registry
|
|
test/child
|
|
test/parent
|
|
INSERT INTO child VALUES(5,4);
|
|
SET foreign_key_checks = 1;
|
|
INSERT INTO child VALUES(6,5);
|
|
ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`child`, CONSTRAINT `fk_1` FOREIGN KEY (`a2`) REFERENCES `parent` (`b`) ON DELETE SET NULL ON UPDATE CASCADE)
|
|
SET foreign_key_checks = 0;
|
|
CREATE TABLE `#parent` (a INT PRIMARY KEY, b INT NOT NULL) ENGINE = InnoDB;
|
|
CREATE INDEX tb ON `#parent`(a, b);
|
|
CREATE TABLE `#child` (a1 INT PRIMARY KEY, a2 INT) ENGINE = InnoDB;
|
|
CREATE INDEX tb ON `#child`(a1, a2);
|
|
SET DEBUG_DBUG = '+d,innodb_test_no_foreign_idx';
|
|
ALTER TABLE `#child` ADD CONSTRAINT fk_40 FOREIGN KEY (a1, a2)
|
|
REFERENCES `#parent`(a, b) ON DELETE CASCADE ON UPDATE CASCADE,
|
|
ALGORITHM = INPLACE;
|
|
ERROR HY000: Failed to add the foreign key constraint. Missing index for constraint 'fk_40' in the foreign table '#child'
|
|
SET DEBUG_DBUG = @saved_debug_dbug;
|
|
SHOW ERRORS;
|
|
Level Code Message
|
|
Error 1821 Failed to add the foreign key constraint. Missing index for constraint 'fk_40' in the foreign table '#child'
|
|
SELECT * FROM information_schema.INNODB_SYS_FOREIGN;
|
|
ID FOR_NAME REF_NAME N_COLS TYPE
|
|
fk_1 test/child test/parent 1 6
|
|
fk_10 test/child test/parent 2 5
|
|
fk_2 test/child test/parent 2 5
|
|
fk_3 test/child test/parent 2 5
|
|
fk_4 test/child test/parent 2 5
|
|
SELECT * FROM information_schema.INNODB_SYS_FOREIGN_COLS;
|
|
ID FOR_COL_NAME REF_COL_NAME POS
|
|
fk_1 a2 b 0
|
|
fk_10 a1 a 0
|
|
fk_10 a2 b 1
|
|
fk_2 a1 a 0
|
|
fk_2 a2 b 1
|
|
fk_3 a1 a 0
|
|
fk_3 a2 b 1
|
|
fk_4 a1 a 0
|
|
fk_4 a2 b 1
|
|
SET DEBUG_DBUG = '+d,innodb_test_no_reference_idx';
|
|
ALTER TABLE child ADD CONSTRAINT fk_42 FOREIGN KEY (a1, a2)
|
|
REFERENCES parent(a, b) ON DELETE CASCADE ON UPDATE CASCADE,
|
|
ALGORITHM = INPLACE;
|
|
ERROR HY000: Failed to add the foreign key constraint. Missing index for constraint 'fk_42' in the referenced table 'parent'
|
|
SET DEBUG_DBUG = @saved_debug_dbug;
|
|
SHOW ERRORS;
|
|
Level Code Message
|
|
Error 1822 Failed to add the foreign key constraint. Missing index for constraint 'fk_42' in the referenced table 'parent'
|
|
SET DEBUG_DBUG = '+d,innodb_test_wrong_fk_option';
|
|
ALTER TABLE child ADD CONSTRAINT fk_42 FOREIGN KEY (a1, a2)
|
|
REFERENCES parent(a, b) ON DELETE CASCADE ON UPDATE CASCADE,
|
|
ALGORITHM = INPLACE;
|
|
ERROR HY000: Failed to add the foreign key constraint on table 'child'. Incorrect options in FOREIGN KEY constraint 'fk_42'
|
|
SET DEBUG_DBUG = @saved_debug_dbug;
|
|
SELECT * FROM information_schema.INNODB_SYS_FOREIGN;
|
|
ID FOR_NAME REF_NAME N_COLS TYPE
|
|
fk_1 test/child test/parent 1 6
|
|
fk_10 test/child test/parent 2 5
|
|
fk_2 test/child test/parent 2 5
|
|
fk_3 test/child test/parent 2 5
|
|
fk_4 test/child test/parent 2 5
|
|
SELECT * FROM information_schema.INNODB_SYS_FOREIGN_COLS;
|
|
ID FOR_COL_NAME REF_COL_NAME POS
|
|
fk_1 a2 b 0
|
|
fk_10 a1 a 0
|
|
fk_10 a2 b 1
|
|
fk_2 a1 a 0
|
|
fk_2 a2 b 1
|
|
fk_3 a1 a 0
|
|
fk_3 a2 b 1
|
|
fk_4 a1 a 0
|
|
fk_4 a2 b 1
|
|
SET DEBUG_DBUG = '+d,innodb_test_cannot_add_fk_system';
|
|
ALTER TABLE `#child` ADD CONSTRAINT fk_43 FOREIGN KEY (a1, a2)
|
|
REFERENCES `#parent`(a, b) ON DELETE CASCADE ON UPDATE CASCADE,
|
|
ALGORITHM = INPLACE;
|
|
ERROR HY000: Failed to add the foreign key constraint 'test/@0023child' to system tables
|
|
SET DEBUG_DBUG = @saved_debug_dbug;
|
|
SHOW ERRORS;
|
|
Level Code Message
|
|
Error 1823 Failed to add the foreign key constraint 'test/@0023child' to system tables
|
|
DROP TABLE `#child`;
|
|
DROP TABLE `#parent`;
|
|
SET foreign_key_checks = 0;
|
|
ALTER TABLE child ADD CONSTRAINT fk_5 FOREIGN KEY (a2) REFERENCES parent(b)
|
|
ON DELETE SET NULL ON UPDATE CASCADE,
|
|
ADD CONSTRAINT fk_6 FOREIGN KEY (a1, a2)
|
|
REFERENCES parent(a, b) ON DELETE CASCADE ON UPDATE CASCADE,
|
|
ALGORITHM = INPLACE;
|
|
SELECT * FROM information_schema.INNODB_SYS_FOREIGN;
|
|
ID FOR_NAME REF_NAME N_COLS TYPE
|
|
fk_1 test/child test/parent 1 6
|
|
fk_10 test/child test/parent 2 5
|
|
fk_2 test/child test/parent 2 5
|
|
fk_3 test/child test/parent 2 5
|
|
fk_4 test/child test/parent 2 5
|
|
fk_5 test/child test/parent 1 6
|
|
fk_6 test/child test/parent 2 5
|
|
SELECT * FROM information_schema.INNODB_SYS_FOREIGN_COLS;
|
|
ID FOR_COL_NAME REF_COL_NAME POS
|
|
fk_1 a2 b 0
|
|
fk_10 a1 a 0
|
|
fk_10 a2 b 1
|
|
fk_2 a1 a 0
|
|
fk_2 a2 b 1
|
|
fk_3 a1 a 0
|
|
fk_3 a2 b 1
|
|
fk_4 a1 a 0
|
|
fk_4 a2 b 1
|
|
fk_5 a2 b 0
|
|
fk_6 a1 a 0
|
|
fk_6 a2 b 1
|
|
DROP TABLE child;
|
|
DROP TABLE parent;
|
|
CREATE TABLE parent (a INT PRIMARY KEY, b INT NOT NULL) ENGINE = InnoDB;
|
|
INSERT INTO parent VALUES(1,2),(2,3);
|
|
CREATE INDEX tb ON parent(b);
|
|
INSERT INTO parent VALUES(10,20),(20,30);
|
|
CREATE TABLE child (a1 INT PRIMARY KEY, a2 INT) ENGINE = InnoDB;
|
|
CREATE INDEX tb ON child(a2);
|
|
INSERT INTO child VALUES(10,20);
|
|
SET foreign_key_checks = 0;
|
|
ALTER TABLE child DROP INDEX tb, ADD CONSTRAINT fk_4 FOREIGN KEY (a2)
|
|
REFERENCES parent(b) ON DELETE CASCADE ON UPDATE CASCADE,
|
|
ALGORITHM = INPLACE;
|
|
SHOW CREATE TABLE child;
|
|
Table Create Table
|
|
child CREATE TABLE `child` (
|
|
`a1` int(11) NOT NULL,
|
|
`a2` int(11) DEFAULT NULL,
|
|
PRIMARY KEY (`a1`),
|
|
KEY `fk_4` (`a2`),
|
|
CONSTRAINT `fk_4` FOREIGN KEY (`a2`) REFERENCES `parent` (`b`) ON DELETE CASCADE ON UPDATE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
|
|
SELECT * FROM information_schema.INNODB_SYS_FOREIGN;
|
|
ID FOR_NAME REF_NAME N_COLS TYPE
|
|
fk_4 test/child test/parent 1 5
|
|
SELECT * FROM information_schema.INNODB_SYS_FOREIGN_COLS;
|
|
ID FOR_COL_NAME REF_COL_NAME POS
|
|
fk_4 a2 b 0
|
|
SET foreign_key_checks = 1;
|
|
DROP TABLE child;
|
|
DROP TABLE parent;
|
|
CREATE TABLE parent (a INT PRIMARY KEY, b INT NOT NULL) ENGINE = InnoDB;
|
|
INSERT INTO parent VALUES(1,2),(2,3);
|
|
CREATE INDEX tb ON parent(b);
|
|
INSERT INTO parent VALUES(10,20),(20,30);
|
|
CREATE TABLE child (a1 INT PRIMARY KEY, a2 INT) ENGINE = InnoDB;
|
|
CREATE INDEX tb ON child(a2);
|
|
SET foreign_key_checks = 0;
|
|
ALTER TABLE child CHANGE a2 a3 INT,
|
|
ADD CONSTRAINT fk_1 FOREIGN KEY (a2) REFERENCES parent(b)
|
|
ON DELETE SET NULL ON UPDATE CASCADE,
|
|
ALGORITHM = INPLACE;
|
|
ERROR 42000: Key column 'a2' doesn't exist in table
|
|
ALTER TABLE child CHANGE a2 a3 INT,
|
|
ADD CONSTRAINT fk_1 FOREIGN KEY (a3) REFERENCES parent(b)
|
|
ON DELETE SET NULL ON UPDATE CASCADE,
|
|
ALGORITHM = INPLACE;
|
|
DROP TABLE child;
|
|
DROP TABLE parent;
|
|
CREATE TABLE parent (a INT PRIMARY KEY, b INT NOT NULL) ENGINE = InnoDB;
|
|
INSERT INTO parent VALUES(1,2),(2,3);
|
|
CREATE INDEX tb ON parent(b);
|
|
INSERT INTO parent VALUES(10,20),(20,30);
|
|
CREATE TABLE child (a1 INT NOT NULL, a2 INT) ENGINE = InnoDB;
|
|
CREATE INDEX tb ON child(a2);
|
|
SET foreign_key_checks = 0;
|
|
SET DEBUG_DBUG = '+d,innodb_test_cannot_add_fk_system';
|
|
ALTER TABLE child ADD PRIMARY KEY idx (a3), CHANGE a1 a3 INT,
|
|
ADD CONSTRAINT fk_1 FOREIGN KEY (a2) REFERENCES parent(b)
|
|
ON DELETE SET NULL ON UPDATE CASCADE,
|
|
ALGORITHM = INPLACE;
|
|
ERROR HY000: Failed to add the foreign key constraint 'test/child' to system tables
|
|
SET DEBUG_DBUG = @saved_debug_dbug;
|
|
SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN;
|
|
ID FOR_NAME REF_NAME N_COLS TYPE
|
|
SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN_COLS;
|
|
ID FOR_COL_NAME REF_COL_NAME POS
|
|
SELECT t2.name, t1.name FROM information_schema.innodb_sys_columns t1, information_schema.innodb_sys_tables t2 WHERE t1.table_id = t2.table_id AND t2.name LIKE "%child" ORDER BY t1.name;
|
|
name name
|
|
test/child a1
|
|
test/child a2
|
|
SELECT NAME FROM information_schema.INNODB_SYS_TABLES;
|
|
NAME
|
|
SYS_FOREIGN
|
|
SYS_FOREIGN_COLS
|
|
SYS_VIRTUAL
|
|
mysql/innodb_index_stats
|
|
mysql/innodb_table_stats
|
|
mysql/transaction_registry
|
|
test/child
|
|
test/parent
|
|
ALTER TABLE child ADD PRIMARY KEY idx (a3), CHANGE a1 a3 INT,
|
|
ADD CONSTRAINT fk_1 FOREIGN KEY (a2) REFERENCES parent(b)
|
|
ON DELETE SET NULL ON UPDATE CASCADE,
|
|
ALGORITHM = INPLACE;
|
|
Warnings:
|
|
Warning 1280 Name 'idx' ignored for PRIMARY key.
|
|
SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN;
|
|
ID FOR_NAME REF_NAME N_COLS TYPE
|
|
fk_1 test/child test/parent 1 6
|
|
SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN_COLS;
|
|
ID FOR_COL_NAME REF_COL_NAME POS
|
|
fk_1 a2 b 0
|
|
SELECT t2.name, t1.name FROM information_schema.innodb_sys_columns t1, information_schema.innodb_sys_tables t2 WHERE t1.table_id = t2.table_id AND t2.name LIKE "%child" ORDER BY t1.name;
|
|
name name
|
|
test/child a2
|
|
test/child a3
|
|
SELECT NAME FROM information_schema.INNODB_SYS_TABLES;
|
|
NAME
|
|
SYS_FOREIGN
|
|
SYS_FOREIGN_COLS
|
|
SYS_VIRTUAL
|
|
mysql/innodb_index_stats
|
|
mysql/innodb_table_stats
|
|
mysql/transaction_registry
|
|
test/child
|
|
test/parent
|
|
SHOW CREATE TABLE child;
|
|
Table Create Table
|
|
child CREATE TABLE `child` (
|
|
`a3` int(11) NOT NULL,
|
|
`a2` int(11) DEFAULT NULL,
|
|
PRIMARY KEY (`a3`),
|
|
KEY `tb` (`a2`),
|
|
CONSTRAINT `fk_1` FOREIGN KEY (`a2`) REFERENCES `parent` (`b`) ON DELETE SET NULL ON UPDATE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
|
|
DROP TABLE child;
|
|
CREATE TABLE child (a1 INT NOT NULL, a2 INT) ENGINE = InnoDB;
|
|
ALTER TABLE child ADD PRIMARY KEY idx (a1),
|
|
ADD CONSTRAINT fk_1 FOREIGN KEY (a2) REFERENCES parent(b)
|
|
ON DELETE SET NULL ON UPDATE CASCADE,
|
|
ALGORITHM = INPLACE;
|
|
Warnings:
|
|
Warning 1280 Name 'idx' ignored for PRIMARY key.
|
|
SELECT * from information_schema.INNODB_SYS_FOREIGN;
|
|
ID FOR_NAME REF_NAME N_COLS TYPE
|
|
fk_1 test/child test/parent 1 6
|
|
SELECT * from information_schema.INNODB_SYS_FOREIGN_COLS;
|
|
ID FOR_COL_NAME REF_COL_NAME POS
|
|
fk_1 a2 b 0
|
|
SELECT t2.name, t1.name FROM information_schema.innodb_sys_columns t1, information_schema.innodb_sys_tables t2 WHERE t1.table_id = t2.table_id AND t2.name LIKE "%child" ORDER BY t1.name;
|
|
name name
|
|
test/child a1
|
|
test/child a2
|
|
SELECT NAME FROM information_schema.INNODB_SYS_TABLES;
|
|
NAME
|
|
SYS_FOREIGN
|
|
SYS_FOREIGN_COLS
|
|
SYS_VIRTUAL
|
|
mysql/innodb_index_stats
|
|
mysql/innodb_table_stats
|
|
mysql/transaction_registry
|
|
test/child
|
|
test/parent
|
|
SHOW CREATE TABLE child;
|
|
Table Create Table
|
|
child CREATE TABLE `child` (
|
|
`a1` int(11) NOT NULL,
|
|
`a2` int(11) DEFAULT NULL,
|
|
PRIMARY KEY (`a1`),
|
|
KEY `fk_1` (`a2`),
|
|
CONSTRAINT `fk_1` FOREIGN KEY (`a2`) REFERENCES `parent` (`b`) ON DELETE SET NULL ON UPDATE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
|
|
DROP TABLE child;
|
|
CREATE TABLE child (a1 INT NOT NULL, a2 INT) ENGINE = InnoDB;
|
|
ALTER TABLE child CHANGE a1 a3 INT,
|
|
ADD CONSTRAINT fk_1 FOREIGN KEY (a3) REFERENCES parent(b)
|
|
ON DELETE SET NULL ON UPDATE CASCADE,
|
|
ALGORITHM = INPLACE;
|
|
SELECT * from information_schema.INNODB_SYS_FOREIGN;
|
|
ID FOR_NAME REF_NAME N_COLS TYPE
|
|
fk_1 test/child test/parent 1 6
|
|
SELECT * from information_schema.INNODB_SYS_FOREIGN_COLS;
|
|
ID FOR_COL_NAME REF_COL_NAME POS
|
|
fk_1 a3 b 0
|
|
SELECT t2.name, t1.name FROM information_schema.innodb_sys_columns t1, information_schema.innodb_sys_tables t2 WHERE t1.table_id = t2.table_id AND t2.name LIKE "%child" ORDER BY t1.name;
|
|
name name
|
|
test/child a2
|
|
test/child a3
|
|
SELECT NAME FROM information_schema.INNODB_SYS_TABLES;
|
|
NAME
|
|
SYS_FOREIGN
|
|
SYS_FOREIGN_COLS
|
|
SYS_VIRTUAL
|
|
mysql/innodb_index_stats
|
|
mysql/innodb_table_stats
|
|
mysql/transaction_registry
|
|
test/child
|
|
test/parent
|
|
SHOW CREATE TABLE child;
|
|
Table Create Table
|
|
child CREATE TABLE `child` (
|
|
`a3` int(11) DEFAULT NULL,
|
|
`a2` int(11) DEFAULT NULL,
|
|
KEY `fk_1` (`a3`),
|
|
CONSTRAINT `fk_1` FOREIGN KEY (`a3`) REFERENCES `parent` (`b`) ON DELETE SET NULL ON UPDATE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
|
|
DROP TABLE child;
|
|
CREATE TABLE child (a1 INT NOT NULL, a2 INT) ENGINE = InnoDB;
|
|
ALTER TABLE child ADD PRIMARY KEY idx (a3), CHANGE a1 a3 INT,
|
|
ADD CONSTRAINT fk_1 FOREIGN KEY (a3) REFERENCES parent(b)
|
|
ON DELETE SET NULL ON UPDATE CASCADE,
|
|
ALGORITHM = INPLACE;
|
|
ERROR HY000: Failed to add the foreign key constraint on table 'child'. Incorrect options in FOREIGN KEY constraint 'fk_1'
|
|
DROP TABLE parent;
|
|
DROP TABLE child;
|
|
CREATE TABLE parent (a INT PRIMARY KEY, b INT NOT NULL, c INT) ENGINE = InnoDB;
|
|
INSERT INTO parent VALUES(1,2,3),(2,3,4);
|
|
CREATE INDEX tb ON parent(b);
|
|
CREATE TABLE child (a1 INT NOT NULL, a2 INT, a3 INT) ENGINE = InnoDB;
|
|
CREATE INDEX tb ON child(a2);
|
|
ALTER TABLE child
|
|
ADD CONSTRAINT fk_a FOREIGN KEY (a2) REFERENCES parent(b)
|
|
ON DELETE SET NULL ON UPDATE CASCADE,
|
|
ALGORITHM = INPLACE;
|
|
ALTER TABLE child
|
|
ADD CONSTRAINT fk_b FOREIGN KEY (a1) REFERENCES parent(a),
|
|
ALGORITHM = INPLACE;
|
|
ALTER TABLE child CHANGE a2 a2_new INT, CHANGE a1 a1_new INT;
|
|
SHOW CREATE TABLE child;
|
|
Table Create Table
|
|
child CREATE TABLE `child` (
|
|
`a1_new` int(11) DEFAULT NULL,
|
|
`a2_new` int(11) DEFAULT NULL,
|
|
`a3` int(11) DEFAULT NULL,
|
|
KEY `tb` (`a2_new`),
|
|
KEY `fk_b` (`a1_new`),
|
|
CONSTRAINT `fk_a` FOREIGN KEY (`a2_new`) REFERENCES `parent` (`b`) ON DELETE SET NULL ON UPDATE CASCADE,
|
|
CONSTRAINT `fk_b` FOREIGN KEY (`a1_new`) REFERENCES `parent` (`a`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
|
|
SELECT * from information_schema.INNODB_SYS_FOREIGN;
|
|
ID FOR_NAME REF_NAME N_COLS TYPE
|
|
fk_a test/child test/parent 1 6
|
|
fk_b test/child test/parent 1 0
|
|
SELECT * from information_schema.INNODB_SYS_FOREIGN_COLS;
|
|
ID FOR_COL_NAME REF_COL_NAME POS
|
|
fk_a a2_new b 0
|
|
fk_b a1_new a 0
|
|
ALTER TABLE child
|
|
ADD CONSTRAINT fk_new_1 FOREIGN KEY (a1_new) REFERENCES parent(b),
|
|
ADD CONSTRAINT fk_new_2 FOREIGN KEY (a2_new) REFERENCES parent(a),
|
|
ADD CONSTRAINT fk_new_3 FOREIGN KEY (a3) REFERENCES parent(c),
|
|
ALGORITHM = INPLACE;
|
|
ERROR HY000: Failed to add the foreign key constraint. Missing index for constraint 'fk_new_3' in the referenced table 'parent'
|
|
SHOW CREATE TABLE child;
|
|
Table Create Table
|
|
child CREATE TABLE `child` (
|
|
`a1_new` int(11) DEFAULT NULL,
|
|
`a2_new` int(11) DEFAULT NULL,
|
|
`a3` int(11) DEFAULT NULL,
|
|
KEY `tb` (`a2_new`),
|
|
KEY `fk_b` (`a1_new`),
|
|
CONSTRAINT `fk_a` FOREIGN KEY (`a2_new`) REFERENCES `parent` (`b`) ON DELETE SET NULL ON UPDATE CASCADE,
|
|
CONSTRAINT `fk_b` FOREIGN KEY (`a1_new`) REFERENCES `parent` (`a`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
|
|
SELECT * from information_schema.INNODB_SYS_FOREIGN;
|
|
ID FOR_NAME REF_NAME N_COLS TYPE
|
|
fk_a test/child test/parent 1 6
|
|
fk_b test/child test/parent 1 0
|
|
SELECT * from information_schema.INNODB_SYS_FOREIGN_COLS;
|
|
ID FOR_COL_NAME REF_COL_NAME POS
|
|
fk_a a2_new b 0
|
|
fk_b a1_new a 0
|
|
ALTER TABLE child
|
|
ADD CONSTRAINT fk_new_1 FOREIGN KEY (a1_new) REFERENCES parent(b),
|
|
ADD CONSTRAINT fk_new_2 FOREIGN KEY (a2_new) REFERENCES parent(a),
|
|
ADD CONSTRAINT fk_new_3 FOREIGN KEY (a3) REFERENCES parent(a),
|
|
ALGORITHM = INPLACE;
|
|
SHOW CREATE TABLE child;
|
|
Table Create Table
|
|
child CREATE TABLE `child` (
|
|
`a1_new` int(11) DEFAULT NULL,
|
|
`a2_new` int(11) DEFAULT NULL,
|
|
`a3` int(11) DEFAULT NULL,
|
|
KEY `tb` (`a2_new`),
|
|
KEY `fk_new_1` (`a1_new`),
|
|
KEY `fk_new_3` (`a3`),
|
|
CONSTRAINT `fk_a` FOREIGN KEY (`a2_new`) REFERENCES `parent` (`b`) ON DELETE SET NULL ON UPDATE CASCADE,
|
|
CONSTRAINT `fk_b` FOREIGN KEY (`a1_new`) REFERENCES `parent` (`a`),
|
|
CONSTRAINT `fk_new_1` FOREIGN KEY (`a1_new`) REFERENCES `parent` (`b`),
|
|
CONSTRAINT `fk_new_2` FOREIGN KEY (`a2_new`) REFERENCES `parent` (`a`),
|
|
CONSTRAINT `fk_new_3` FOREIGN KEY (`a3`) REFERENCES `parent` (`a`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
|
|
SELECT * from information_schema.INNODB_SYS_FOREIGN;
|
|
ID FOR_NAME REF_NAME N_COLS TYPE
|
|
fk_a test/child test/parent 1 6
|
|
fk_b test/child test/parent 1 0
|
|
fk_new_1 test/child test/parent 1 0
|
|
fk_new_2 test/child test/parent 1 0
|
|
fk_new_3 test/child test/parent 1 0
|
|
SELECT * from information_schema.INNODB_SYS_FOREIGN_COLS;
|
|
ID FOR_COL_NAME REF_COL_NAME POS
|
|
fk_a a2_new b 0
|
|
fk_b a1_new a 0
|
|
fk_new_1 a1_new b 0
|
|
fk_new_2 a2_new a 0
|
|
fk_new_3 a3 a 0
|
|
DROP TABLE child;
|
|
CREATE TABLE child (a1 INT NOT NULL, a2 INT, a3 INT) ENGINE = InnoDB;
|
|
CREATE INDEX tb ON child(a2);
|
|
ALTER TABLE child ADD PRIMARY KEY idx (a1),
|
|
ADD CONSTRAINT fk_new_1 FOREIGN KEY (a1) REFERENCES parent(b),
|
|
ADD CONSTRAINT fk_new_2 FOREIGN KEY (a2) REFERENCES parent(a),
|
|
ADD CONSTRAINT fk_new_3 FOREIGN KEY (a3) REFERENCES parent(c),
|
|
ALGORITHM = INPLACE;
|
|
ERROR HY000: Failed to add the foreign key constraint. Missing index for constraint 'fk_new_3' in the referenced table 'parent'
|
|
SHOW CREATE TABLE child;
|
|
Table Create Table
|
|
child CREATE TABLE `child` (
|
|
`a1` int(11) NOT NULL,
|
|
`a2` int(11) DEFAULT NULL,
|
|
`a3` int(11) DEFAULT NULL,
|
|
KEY `tb` (`a2`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
|
|
SELECT * from information_schema.INNODB_SYS_FOREIGN;
|
|
ID FOR_NAME REF_NAME N_COLS TYPE
|
|
SELECT * from information_schema.INNODB_SYS_FOREIGN_COLS;
|
|
ID FOR_COL_NAME REF_COL_NAME POS
|
|
ALTER TABLE child ADD PRIMARY KEY idx (a1),
|
|
ADD CONSTRAINT fk_new_1 FOREIGN KEY (a1) REFERENCES parent(b),
|
|
ADD CONSTRAINT fk_new_2 FOREIGN KEY (a2) REFERENCES parent(a),
|
|
ADD CONSTRAINT fk_new_3 FOREIGN KEY (a3) REFERENCES parent(a),
|
|
ALGORITHM = INPLACE;
|
|
Warnings:
|
|
Warning 1280 Name 'idx' ignored for PRIMARY key.
|
|
SHOW CREATE TABLE child;
|
|
Table Create Table
|
|
child CREATE TABLE `child` (
|
|
`a1` int(11) NOT NULL,
|
|
`a2` int(11) DEFAULT NULL,
|
|
`a3` int(11) DEFAULT NULL,
|
|
PRIMARY KEY (`a1`),
|
|
KEY `tb` (`a2`),
|
|
KEY `fk_new_3` (`a3`),
|
|
CONSTRAINT `fk_new_1` FOREIGN KEY (`a1`) REFERENCES `parent` (`b`),
|
|
CONSTRAINT `fk_new_2` FOREIGN KEY (`a2`) REFERENCES `parent` (`a`),
|
|
CONSTRAINT `fk_new_3` FOREIGN KEY (`a3`) REFERENCES `parent` (`a`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
|
|
SELECT * from information_schema.INNODB_SYS_FOREIGN;
|
|
ID FOR_NAME REF_NAME N_COLS TYPE
|
|
fk_new_1 test/child test/parent 1 0
|
|
fk_new_2 test/child test/parent 1 0
|
|
fk_new_3 test/child test/parent 1 0
|
|
SELECT * from information_schema.INNODB_SYS_FOREIGN_COLS;
|
|
ID FOR_COL_NAME REF_COL_NAME POS
|
|
fk_new_1 a1 b 0
|
|
fk_new_2 a2 a 0
|
|
fk_new_3 a3 a 0
|
|
SET foreign_key_checks = 1;
|
|
DROP TABLE child;
|
|
DROP TABLE parent;
|
|
CREATE TABLE Parent (a INT PRIMARY KEY, b INT NOT NULL) ENGINE = InnoDB;
|
|
INSERT INTO Parent VALUES(1,2),(2,3);
|
|
CREATE INDEX tb ON Parent(b);
|
|
INSERT INTO Parent VALUES(10,20),(20,30);
|
|
CREATE TABLE Child (a1 INT PRIMARY KEY, a2 INT) ENGINE = InnoDB;
|
|
CREATE INDEX tb ON Child(a2);
|
|
INSERT INTO Child VALUES(10,20);
|
|
SET foreign_key_checks = 0;
|
|
ALTER TABLE Child ADD CONSTRAINT fk_1 FOREIGN KEY (a2)
|
|
REFERENCES Parent(b) ON DELETE SET NULL ON UPDATE CASCADE,
|
|
ALGORITHM = INPLACE;
|
|
DROP TABLE Child;
|
|
DROP TABLE Parent;
|
|
CREATE TABLE `t2`(a int,c int,d int) ENGINE=INNODB;
|
|
CREATE TABLE `t3`(a int,c int,d int) ENGINE=INNODB;
|
|
CREATE INDEX idx ON t3(a);
|
|
ALTER TABLE `t2` ADD CONSTRAINT `fw` FOREIGN KEY (`c`) REFERENCES t3 (a);
|
|
ALTER TABLE `t2` ADD CONSTRAINT `e` foreign key (`d`) REFERENCES t3(a);
|
|
ALTER TABLE `t3` ADD CONSTRAINT `e` foreign key (`c`) REFERENCES `t2`(`c`) ON UPDATE SET NULL;
|
|
SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN;
|
|
ID FOR_NAME REF_NAME N_COLS TYPE
|
|
e test/t2 test/t3 1 0
|
|
fw test/t2 test/t3 1 0
|
|
e test/t3 test/t2 1 8
|
|
SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN_COLS;
|
|
ID FOR_COL_NAME REF_COL_NAME POS
|
|
e d a 0
|
|
fw c a 0
|
|
e c c 0
|
|
DROP TABLE t2;
|
|
DROP TABLE t3;
|
|
# Bug #17449901 TABLE DISAPPEARS WHEN ALTERING
|
|
# WITH FOREIGN KEY CHECKS OFF
|
|
create table t1(f1 int,primary key(f1))engine=innodb;
|
|
create table t2(f2 int,f3 int,key t(f2,f3),foreign key(f2) references t1(f1))engine=innodb;
|
|
SET foreign_key_checks=0;
|
|
drop index t on t2;
|
|
drop table t2;
|
|
drop table t1;
|
|
create table t1(f1 int ,primary key(f1))engine=innodb;
|
|
create table t2(f2 int,f3 int, key t(f2),foreign key(f2) references t1(f1))engine=innodb;
|
|
SET foreign_key_checks = 0;
|
|
alter table t2 drop key t,algorithm=inplace;
|
|
show create table t2;
|
|
Table Create Table
|
|
t2 CREATE TABLE `t2` (
|
|
`f2` int(11) DEFAULT NULL,
|
|
`f3` int(11) DEFAULT NULL,
|
|
CONSTRAINT `1` FOREIGN KEY (`f2`) REFERENCES `t1` (`f1`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
|
|
drop table t2;
|
|
drop table t1;
|
|
create table t1(f1 int ,primary key(f1))engine=innodb;
|
|
create table t2(f2 int,f3 int, key t(f2),key t1(f2,f3),
|
|
foreign key(f2) references t1(f1))engine=innodb;
|
|
SET foreign_key_checks = 0;
|
|
alter table t2 drop key t,algorithm=inplace;
|
|
show create table t2;
|
|
Table Create Table
|
|
t2 CREATE TABLE `t2` (
|
|
`f2` int(11) DEFAULT NULL,
|
|
`f3` int(11) DEFAULT NULL,
|
|
KEY `t1` (`f2`,`f3`),
|
|
CONSTRAINT `1` FOREIGN KEY (`f2`) REFERENCES `t1` (`f1`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
|
|
drop table t2;
|
|
drop table t1;
|
|
#
|
|
# MDEV-29092 FOREIGN_KEY_CHECKS does not prevent non-copy
|
|
# alter from creating invalid FK structures
|
|
#
|
|
CREATE TABLE t1(f1 INT, KEY(f1),
|
|
FOREIGN KEY(f1) references t1(f1))ENGINE=InnoDB;
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`f1` int(11) DEFAULT NULL,
|
|
KEY `f1` (`f1`),
|
|
CONSTRAINT `1` FOREIGN KEY (`f1`) REFERENCES `t1` (`f1`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
|
|
DROP TABLE t1;
|
|
CREATE TABLE t1(f1 INT, KEY(f1),
|
|
FOREIGN KEY(f1) REFERENCES t1(f1))ENGINE=InnoDB;
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`f1` int(11) DEFAULT NULL,
|
|
KEY `f1` (`f1`),
|
|
CONSTRAINT `1` FOREIGN KEY (`f1`) REFERENCES `t1` (`f1`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
|
|
ALTER TABLE t1 DROP KEY f1;
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`f1` int(11) DEFAULT NULL,
|
|
CONSTRAINT `1` FOREIGN KEY (`f1`) REFERENCES `t1` (`f1`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
|
|
DROP TABLE t1;
|