mirror of
https://github.com/MariaDB/server.git
synced 2025-10-31 19:06:14 +01:00
677 lines
26 KiB
Text
677 lines
26 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
|
|
test/fk_1 test/child test/parent 1 6
|
|
SELECT * FROM information_schema.INNODB_SYS_FOREIGN_COLS;
|
|
ID FOR_COL_NAME REF_COL_NAME POS
|
|
test/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 'test/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
|
|
test/fk_1 test/child test/parent 1 6
|
|
test/fk_10 test/child test/parent 2 5
|
|
test/fk_2 test/child test/parent 2 5
|
|
test/fk_3 test/child test/parent 2 5
|
|
SELECT * FROM information_schema.INNODB_SYS_FOREIGN_COLS;
|
|
ID FOR_COL_NAME REF_COL_NAME POS
|
|
test/fk_1 a2 b 0
|
|
test/fk_10 a1 a 0
|
|
test/fk_10 a2 b 1
|
|
test/fk_2 a1 a 0
|
|
test/fk_2 a2 b 1
|
|
test/fk_3 a1 a 0
|
|
test/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
|
|
test/fk_1 test/child test/parent 1 6
|
|
test/fk_10 test/child test/parent 2 5
|
|
test/fk_2 test/child test/parent 2 5
|
|
test/fk_3 test/child test/parent 2 5
|
|
test/fk_4 test/child test/parent 2 5
|
|
SELECT * FROM information_schema.INNODB_SYS_FOREIGN_COLS;
|
|
ID FOR_COL_NAME REF_COL_NAME POS
|
|
test/fk_1 a2 b 0
|
|
test/fk_10 a1 a 0
|
|
test/fk_10 a2 b 1
|
|
test/fk_2 a1 a 0
|
|
test/fk_2 a2 b 1
|
|
test/fk_3 a1 a 0
|
|
test/fk_3 a2 b 1
|
|
test/fk_4 a1 a 0
|
|
test/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
|
|
test/fk_1 test/child test/parent 1 6
|
|
test/fk_10 test/child test/parent 2 5
|
|
test/fk_2 test/child test/parent 2 5
|
|
test/fk_3 test/child test/parent 2 5
|
|
test/fk_4 test/child test/parent 2 5
|
|
SELECT * FROM information_schema.INNODB_SYS_FOREIGN_COLS;
|
|
ID FOR_COL_NAME REF_COL_NAME POS
|
|
test/fk_1 a2 b 0
|
|
test/fk_10 a1 a 0
|
|
test/fk_10 a2 b 1
|
|
test/fk_2 a1 a 0
|
|
test/fk_2 a2 b 1
|
|
test/fk_3 a1 a 0
|
|
test/fk_3 a2 b 1
|
|
test/fk_4 a1 a 0
|
|
test/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 'test/fk_42'
|
|
SET DEBUG_DBUG = @saved_debug_dbug;
|
|
SELECT * FROM information_schema.INNODB_SYS_FOREIGN;
|
|
ID FOR_NAME REF_NAME N_COLS TYPE
|
|
test/fk_1 test/child test/parent 1 6
|
|
test/fk_10 test/child test/parent 2 5
|
|
test/fk_2 test/child test/parent 2 5
|
|
test/fk_3 test/child test/parent 2 5
|
|
test/fk_4 test/child test/parent 2 5
|
|
SELECT * FROM information_schema.INNODB_SYS_FOREIGN_COLS;
|
|
ID FOR_COL_NAME REF_COL_NAME POS
|
|
test/fk_1 a2 b 0
|
|
test/fk_10 a1 a 0
|
|
test/fk_10 a2 b 1
|
|
test/fk_2 a1 a 0
|
|
test/fk_2 a2 b 1
|
|
test/fk_3 a1 a 0
|
|
test/fk_3 a2 b 1
|
|
test/fk_4 a1 a 0
|
|
test/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/fk_43' to system tables
|
|
SET DEBUG_DBUG = @saved_debug_dbug;
|
|
SHOW ERRORS;
|
|
Level Code Message
|
|
Error 1823 Failed to add the foreign key constraint 'test/fk_43' 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
|
|
test/fk_1 test/child test/parent 1 6
|
|
test/fk_10 test/child test/parent 2 5
|
|
test/fk_2 test/child test/parent 2 5
|
|
test/fk_3 test/child test/parent 2 5
|
|
test/fk_4 test/child test/parent 2 5
|
|
test/fk_5 test/child test/parent 1 6
|
|
test/fk_6 test/child test/parent 2 5
|
|
SELECT * FROM information_schema.INNODB_SYS_FOREIGN_COLS;
|
|
ID FOR_COL_NAME REF_COL_NAME POS
|
|
test/fk_1 a2 b 0
|
|
test/fk_10 a1 a 0
|
|
test/fk_10 a2 b 1
|
|
test/fk_2 a1 a 0
|
|
test/fk_2 a2 b 1
|
|
test/fk_3 a1 a 0
|
|
test/fk_3 a2 b 1
|
|
test/fk_4 a1 a 0
|
|
test/fk_4 a2 b 1
|
|
test/fk_5 a2 b 0
|
|
test/fk_6 a1 a 0
|
|
test/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
|
|
test/fk_4 test/child test/parent 1 5
|
|
SELECT * FROM information_schema.INNODB_SYS_FOREIGN_COLS;
|
|
ID FOR_COL_NAME REF_COL_NAME POS
|
|
test/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/fk_1' 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
|
|
test/fk_1 test/child test/parent 1 6
|
|
SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN_COLS;
|
|
ID FOR_COL_NAME REF_COL_NAME POS
|
|
test/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
|
|
test/fk_1 test/child test/parent 1 6
|
|
SELECT * from information_schema.INNODB_SYS_FOREIGN_COLS;
|
|
ID FOR_COL_NAME REF_COL_NAME POS
|
|
test/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
|
|
test/fk_1 test/child test/parent 1 6
|
|
SELECT * from information_schema.INNODB_SYS_FOREIGN_COLS;
|
|
ID FOR_COL_NAME REF_COL_NAME POS
|
|
test/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 'test/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
|
|
test/fk_a test/child test/parent 1 6
|
|
test/fk_b test/child test/parent 1 0
|
|
SELECT * from information_schema.INNODB_SYS_FOREIGN_COLS;
|
|
ID FOR_COL_NAME REF_COL_NAME POS
|
|
test/fk_a a2_new b 0
|
|
test/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
|
|
test/fk_a test/child test/parent 1 6
|
|
test/fk_b test/child test/parent 1 0
|
|
SELECT * from information_schema.INNODB_SYS_FOREIGN_COLS;
|
|
ID FOR_COL_NAME REF_COL_NAME POS
|
|
test/fk_a a2_new b 0
|
|
test/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
|
|
test/fk_a test/child test/parent 1 6
|
|
test/fk_b test/child test/parent 1 0
|
|
test/fk_new_1 test/child test/parent 1 0
|
|
test/fk_new_2 test/child test/parent 1 0
|
|
test/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
|
|
test/fk_a a2_new b 0
|
|
test/fk_b a1_new a 0
|
|
test/fk_new_1 a1_new b 0
|
|
test/fk_new_2 a2_new a 0
|
|
test/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
|
|
test/fk_new_1 test/child test/parent 1 0
|
|
test/fk_new_2 test/child test/parent 1 0
|
|
test/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
|
|
test/fk_new_1 a1 b 0
|
|
test/fk_new_2 a2 a 0
|
|
test/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;
|
|
ERROR HY000: Failed to add the foreign key constraint 'test/e' to system tables
|
|
SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN;
|
|
ID FOR_NAME REF_NAME N_COLS TYPE
|
|
test/e test/t2 test/t3 1 0
|
|
test/fw test/t2 test/t3 1 0
|
|
SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN_COLS;
|
|
ID FOR_COL_NAME REF_COL_NAME POS
|
|
test/e d a 0
|
|
test/fw c a 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 `t2_ibfk_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 `t2_ibfk_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 `t1_ibfk_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 `t1_ibfk_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 `t1_ibfk_1` FOREIGN KEY (`f1`) REFERENCES `t1` (`f1`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
|
|
DROP TABLE t1;
|