Description:
We are unable to add new foreign key references to a renamed column. We will always get:
100812 16:51:20 Error in foreign key constraint of table test1/#sql-f0c_130:
FOREIGN KEY (customer_id)
REFERENCES test1.customer(c_id):
Cannot resolve column name close to:
)
How to repeat:
1) Create the tables to test the issue on:
CREATE TABLE test1.table1(
id TINYINT(4) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (id)
)
ENGINE = INNODB
AUTO_INCREMENT = 1
CHARACTER SET latin1
COLLATE latin1_swedish_ci;
CREATE TABLE test1.table2(
id TINYINT(4) NOT NULL AUTO_INCREMENT,
table1_id TINYINT(4) NOT NULL,
PRIMARY KEY (id)
)
ENGINE = INNODB
AUTO_INCREMENT = 1
CHARACTER SET latin1
COLLATE latin1_swedish_ci;
2) Rename a column in table1
ALTER TABLE test1.table1
CHANGE COLUMN id table1_id TINYINT(4) NOT NULL AUTO_INCREMENT;
3) Create a foreign key on table2 to reference the renamed column on table1
ALTER TABLE test1.table2
ADD CONSTRAINT FK_table2_table1_table1_id FOREIGN KEY (table1_id)
REFERENCES test1.table1(table1_id);
INNODB STATUS:
------------------------
LATEST FOREIGN KEY ERROR
------------------------
100812 17:26:48 Error in foreign key constraint of table test1/#sql-
f0c_130:
FOREIGN KEY (table1_id)
REFERENCES test1.table1(table1_id):
Cannot resolve column name close to:
)
4) But if we try to add the same Foreign key using the old name it runs without errors:
ALTER TABLE test1.table2
ADD CONSTRAINT FK_table2_table1_table1_id FOREIGN KEY (table1_id)
REFERENCES test1.table1(id);
Execute succeeded [0.021s]
Query opened in 0.002s [0.001s exec, 0.001s fetch]
5) However, since it isn't correct anyway, if we try to modify table1 like for example adding a new column, we will get an error because of the incorrect column reference:
ALTER TABLE test1.table1
ADD COLUMN new_col CHAR(10) DEFAULT NULL AFTER table1_id;
100812 17:29:43 Error in foreign key constraint of table test1/table2:
there is no index in referenced table which would contain
the columns as the first columns, or the data types in the
referenced table do not match the ones in table. Constraint:
,
CONSTRAINT "FK_table2_table1_table1_id" FOREIGN KEY ("table1_id") REFERENCES "table1" ("id")
The index in the foreign key in table is "FK_table2_table1_table1_id"
See http://dev.mysql.com/doc/refman/5.1/en/innodb-foreign-key-constraints.html
for correct foreign key definition.
*********************************************************
We do know that recreating the table would give us a workaround on the issue, but recreating tables wouldn't always be an available option :)
Description: We are unable to add new foreign key references to a renamed column. We will always get: 100812 16:51:20 Error in foreign key constraint of table test1/#sql-f0c_130: FOREIGN KEY (customer_id) REFERENCES test1.customer(c_id): Cannot resolve column name close to: ) How to repeat: 1) Create the tables to test the issue on: CREATE TABLE test1.table1( id TINYINT(4) NOT NULL AUTO_INCREMENT, PRIMARY KEY (id) ) ENGINE = INNODB AUTO_INCREMENT = 1 CHARACTER SET latin1 COLLATE latin1_swedish_ci; CREATE TABLE test1.table2( id TINYINT(4) NOT NULL AUTO_INCREMENT, table1_id TINYINT(4) NOT NULL, PRIMARY KEY (id) ) ENGINE = INNODB AUTO_INCREMENT = 1 CHARACTER SET latin1 COLLATE latin1_swedish_ci; 2) Rename a column in table1 ALTER TABLE test1.table1 CHANGE COLUMN id table1_id TINYINT(4) NOT NULL AUTO_INCREMENT; 3) Create a foreign key on table2 to reference the renamed column on table1 ALTER TABLE test1.table2 ADD CONSTRAINT FK_table2_table1_table1_id FOREIGN KEY (table1_id) REFERENCES test1.table1(table1_id); INNODB STATUS: ------------------------ LATEST FOREIGN KEY ERROR ------------------------ 100812 17:26:48 Error in foreign key constraint of table test1/#sql- f0c_130: FOREIGN KEY (table1_id) REFERENCES test1.table1(table1_id): Cannot resolve column name close to: ) 4) But if we try to add the same Foreign key using the old name it runs without errors: ALTER TABLE test1.table2 ADD CONSTRAINT FK_table2_table1_table1_id FOREIGN KEY (table1_id) REFERENCES test1.table1(id); Execute succeeded [0.021s] Query opened in 0.002s [0.001s exec, 0.001s fetch] 5) However, since it isn't correct anyway, if we try to modify table1 like for example adding a new column, we will get an error because of the incorrect column reference: ALTER TABLE test1.table1 ADD COLUMN new_col CHAR(10) DEFAULT NULL AFTER table1_id; 100812 17:29:43 Error in foreign key constraint of table test1/table2: there is no index in referenced table which would contain the columns as the first columns, or the data types in the referenced table do not match the ones in table. Constraint: , CONSTRAINT "FK_table2_table1_table1_id" FOREIGN KEY ("table1_id") REFERENCES "table1" ("id") The index in the foreign key in table is "FK_table2_table1_table1_id" See http://dev.mysql.com/doc/refman/5.1/en/innodb-foreign-key-constraints.html for correct foreign key definition. ********************************************************* We do know that recreating the table would give us a workaround on the issue, but recreating tables wouldn't always be an available option :)