Description:
When a foreign key is added on child table, MySQL would check many things:
1. parent table is not partitioned,
2. referenced columns exist and are non-virtual,
3. types of referencing and referenced columns are compatible.
However, the length of referencing and referenced columns should be compatible, too.
How to repeat:
-- The parent table's referencing column is `t_id` varchar(64)
-- The child table's referencing column is `t_id` varchar(10)
CREATE TABLE parent (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`t_id` varchar(64) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `uniq_idx_t` (`t_id`)
) ENGINE=InnoDB;
CREATE TABLE child (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`t_id` varchar(10) NOT NULL,
PRIMARY KEY (`id`),
FOREIGN KEY `u_idx_t` (`t_id`) REFERENCES parent(`t_id`)
) ENGINE=InnoDB;
mysql> SELECT * FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS\G
*************************** 1. row ***************************
CONSTRAINT_CATALOG: def
CONSTRAINT_SCHEMA: test
CONSTRAINT_NAME: child_ibfk_1
UNIQUE_CONSTRAINT_CATALOG: def
UNIQUE_CONSTRAINT_SCHEMA: test
UNIQUE_CONSTRAINT_NAME: uniq_idx_t
MATCH_OPTION: NONE
UPDATE_RULE: NO ACTION
DELETE_RULE: NO ACTION
TABLE_NAME: child
REFERENCED_TABLE_NAME: parent
1 row in set (0.01 sec)
mysql> INSERT INTO parent (t_id) VALUES ('t12345678901234567890');
Query OK, 1 row affected (0.02 sec)
mysql> INSERT INTO child (t_id) VALUES ('t123456789');
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`child`, CONSTRAINT `child_ibfk_1` FOREIGN KEY (`t_id`) REFERENCES `parent` (`t_id`))
mysql> INSERT INTO child (t_id) VALUES ('t12345678901234567890');
ERROR 1406 (22001): Data too long for column 't_id' at row 1
If the length of referencing and referenced columns are not compatible, we can not insert a row into child table.
In fact, the child table should not be create normally.
Suggested fix:
In function prepare_foreign_key(), MySQL will prepare FOREIGN_KEY struct with info about a foreign key.
If find parent key, we should check whether the length of every key column is compatible after ER_FOREIGN_KEY_ON_PARTITIONED, ER_FK_NO_COLUMN_PARENT, ER_FK_CANNOT_USE_VIRTUAL_COLUMN and ER_FK_INCOMPATIBLE_COLUMNS.
Description: When a foreign key is added on child table, MySQL would check many things: 1. parent table is not partitioned, 2. referenced columns exist and are non-virtual, 3. types of referencing and referenced columns are compatible. However, the length of referencing and referenced columns should be compatible, too. How to repeat: -- The parent table's referencing column is `t_id` varchar(64) -- The child table's referencing column is `t_id` varchar(10) CREATE TABLE parent ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `t_id` varchar(64) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `uniq_idx_t` (`t_id`) ) ENGINE=InnoDB; CREATE TABLE child ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `t_id` varchar(10) NOT NULL, PRIMARY KEY (`id`), FOREIGN KEY `u_idx_t` (`t_id`) REFERENCES parent(`t_id`) ) ENGINE=InnoDB; mysql> SELECT * FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS\G *************************** 1. row *************************** CONSTRAINT_CATALOG: def CONSTRAINT_SCHEMA: test CONSTRAINT_NAME: child_ibfk_1 UNIQUE_CONSTRAINT_CATALOG: def UNIQUE_CONSTRAINT_SCHEMA: test UNIQUE_CONSTRAINT_NAME: uniq_idx_t MATCH_OPTION: NONE UPDATE_RULE: NO ACTION DELETE_RULE: NO ACTION TABLE_NAME: child REFERENCED_TABLE_NAME: parent 1 row in set (0.01 sec) mysql> INSERT INTO parent (t_id) VALUES ('t12345678901234567890'); Query OK, 1 row affected (0.02 sec) mysql> INSERT INTO child (t_id) VALUES ('t123456789'); ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`child`, CONSTRAINT `child_ibfk_1` FOREIGN KEY (`t_id`) REFERENCES `parent` (`t_id`)) mysql> INSERT INTO child (t_id) VALUES ('t12345678901234567890'); ERROR 1406 (22001): Data too long for column 't_id' at row 1 If the length of referencing and referenced columns are not compatible, we can not insert a row into child table. In fact, the child table should not be create normally. Suggested fix: In function prepare_foreign_key(), MySQL will prepare FOREIGN_KEY struct with info about a foreign key. If find parent key, we should check whether the length of every key column is compatible after ER_FOREIGN_KEY_ON_PARTITIONED, ER_FK_NO_COLUMN_PARENT, ER_FK_CANNOT_USE_VIRTUAL_COLUMN and ER_FK_INCOMPATIBLE_COLUMNS.