mirror of
https://github.com/MariaDB/server.git
synced 2025-12-18 02:05:45 +01:00
a518e1dd42
1. Access foreign keys via TABLE_SHARE::foreign_keys and
TABLE_SHARE::referenced_keys;
foreign_keys and referenced_keys are lists in TABLE_SHARE.
2. Remove handler FK interface:
- get_foreign_key_list()
- get_parent_foreign_key_list()
- referenced_by_foreign_key()
3. Invalidate referenced shares on:
- RENAME TABLE
- DROP TABLE
- RENAME COLUMN
- ADD FOREIGN KEY
When foreign table is created or altered by the above operations
all referenced shares are closed. This blocks the operation while
any referenced shares are used (when at least one its TABLE
instance is locked).
4. Update referenced shares on:
- CREATE TABLE
On CREATE TABLE add items to referenced_keys of referenced
shares. States of referenced shares are restored in case of errors.
5. Invalidate foreign shares on:
- RENAME TABLE
- RENAME COLUMN
The above-mentioned blocking takes effect.
6. Check foreign/referenced shares consistency on:
- CHECK TABLE
7. Temporary change until MDEV-21051:
InnoDB fill foreign key info at handler open().
FOREIGN_KEY_INFO is refactored to FK_info holding Lex_cstring.
On first TABLE open FK_info is loaded from storage engine into
TABLE_SHARE. All referenced shares (if any exists) are closed. This
leads to blocking of first time foreign table open while referenced
tables are used.
MDEV-21311 Converge Foreign_key and supplemental generated Key together
mysql_prepare_create_table() does data validation and such utilities
as automatic name generation. But it does that only for indexes and
ignores Foreign_key objects. Now as Foreign_key data needs to be
stored in FRM files as well this processing must be done for it like
for any other Key objects.
Replace Key::FOREIGN_KEY type with Key::foreign flag of type
Key::MULTIPLE and Key::generated set to true. Construct one object
with Key::foreign == true instead of two objects of type
Key::FOREIGN_KEY and Key::MULTIPLE.
MDEV-21051 datadict refactorings
- Move read_extra2() to datadict.cc
- Refactored extra2_fields to Extra2_info
- build_frm_image() readability
MDEV-21051 build_table_shadow_filename() refactoring
mysql_prepare_alter_table() leaks fixes
MDEV-21051 amend system tables locking restriction
Table mysql.help_relation has foreign key to mysql.help_keyword. On
bootstrap when help_relation is opened, it preopens help_keyword for
READ and fails in lock_tables_check().
If system table is opened for write then fk references are opened for
write.
Related to: Bug#25422, WL#3984
Tests: main.lock
MDEV-21051 Store and read foreign key info into/from FRM files
1. Introduce Foreign_key_io class which creates/parses binary stream
containing foreign key structures. Referenced tables store there only
hints about foreign tables (their db and name), they restore full info
from the corresponding tables.
Foreign_key_io is stored under new EXTRA2_FOREIGN_KEY_INFO field in
extra2 section of FRM file.
2. Modify mysql_prepare_create_table() to generate names for foreign
keys. Until InnoDB storage of foreign keys is removed, FK names must
be unique across the database: the FK name must be based on table
name.
3. Keep stored data in sync on DDL changes. Referenced tables update
their foreign hints after following operations on foreign tables:
- RENAME TABLE
- DROP TABLE
- CREATE TABLE
- ADD FOREIGN KEY
- DROP FOREIGN KEY
Foreign tables update their foreign info after following operations on
referenced tables:
- RENAME TABLE
- RENAME COLUMN
4. To achieve 3. there must be ability to rewrite extra2 section of
FRM file without full reparse. FRM binary is built from primary
structures like HA_CREATE_INFO and cannot be built from TABLE_SHARE.
Use shadow write and rename like fast_alter_partition_table()
does. Shadow FRM is new FRM file that replaces the old one.
CREATE TABLE workflow:
1. Foreign_key is constructed in parser, placed into
alter_info->key_list;
2. mysql_prepare_create_table() translates them to FK_info, assigns
foreign_id if needed;
3. build_frm_image() writes two FK_info lists into FRM's extra2
section, for referenced keys it stores only table names (hints);
4. init_from_binary_frm_image() parses extra2 section and fills
foreign_keys and referenced_keys of TABLE_SHARE.
It restores referenced_keys by reading hint list of table names,
opening corresponding shares and restoring FK_info from their
foreign_keys. Hints resolution is done only when initializing
non-temporary shares. Usually temporary share has different
(temporary) name and it is impossible to resolve foreign keys by
that name (as we identify them by both foreign and referenced
table names). Another not unimportant reason is performance: this
saves spare share acquisitions.
ALTER TABLE workflow:
1. Foreign_key is constructed in parser, placed into
alter_info->key_list;
2. mysql_prepare_alter_table() prepares action lists and share list
of foreigns/references;
3. mysql_prepare_alter_table() locks list of foreigns/references by
MDL_INTENTION_EXCLUSIVE, acquires shares;
4. prepare_create_table() converts key_list into FK_list, assigns
foreign_id;
5. shadow FRM of altered table is created;
6. data is copied;
7. altered table is locked by MDL_EXCLUSIVE;
8. fk_handle_alter() processes action lists, creates FK backups,
modifies shares, writes shadow FRMs;
9. altered table is closed;
10. shadow FRMs are installed;
11. altered table is renamed, FRM backup deleted;
12. (TBD in MDEV-21053) shadow FRMs installation log closed, backups
deleted;
On FK backup system:
In case of failed DDL operation all shares that was modified must be
restored into original state. This is done by FK_ddl_backup (CREATE,
DROP), FK_rename_backup (RENAME), FK_alter_backup (ALTER).
On STL usage:
STL is used for utility not performance-critical algorithms, core
structures hold native List. A wrapper was made to convert STL
exception into bool error status or NULL value.
MDEV-20865 fk_check_consistency() in CHECK TABLE
Self-refs fix
Test table_flags fix: "debug" deviation is now gone.
FIXMEs: +16 -1
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: Missing index for constraint 'child.fk_20' in the referenced table 'parent'
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
Error 1822 Missing index for constraint 'child.fk_20' in the referenced table 'parent'
|
|
SHOW ERRORS;
|
|
Level Code Message
|
|
Error 1822 Missing index for constraint 'child.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: Missing index for constraint 'fk_40' in the foreign table '#child'
|
|
SET DEBUG_DBUG = @saved_debug_dbug;
|
|
SHOW ERRORS;
|
|
Level Code Message
|
|
Error 1821 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: Missing index for constraint 'child.fk_42' in the referenced table 'parent'
|
|
SET DEBUG_DBUG = @saved_debug_dbug;
|
|
SHOW ERRORS;
|
|
Level Code Message
|
|
Error 1822 Missing index for constraint 'child.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: Missing index for constraint 'child.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: Missing index for constraint 'child.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;
|