I have mysql database that hasparent_table utf-8 with 2 columns(id varchar(14) , name varchar(256)) then I created a primary key for this table composing the 2 columns, but I had to create it with limited 200 length , -because of a max index length problem- like this
alter table parent_table add primary key(id,name (200));
every thing was OK , until I came to create a new child_table with foreign key references the parent, the child table has 2 columns (child_id varchar(14) , child_name varchar(256))
NOTE : both tables are the same encoding(utf-8), same engine, same data type , same length although whenever I try to create a foreign key
alter table child_table add foreign key test_foreign_key (child_id,
child_name) references parent_table (id, name);
I always get an error of
Error Code: 1005. Can't create table
mci.#sql-1724_3(ero: 150 "Foreign key constraint is incorrectly formed")
then, when I dropped the primary key in parent_table and modified the name column in both parent_table and child_table to be varchar(200) and then created the primary key in the parent_table normally without limitation , as in :
alter table parent_table add primary key(id,name);
then when I created the foreign key in the child_table , it worked!!!
unfortunately , I can't change the name column length to 200 in my real database , because it is already stuffed with longer data.
how can I create a foreign key in the child_table that references a composite(2-column) primary key in the parent_table with limited index length(200)? can I do something like this ..
alter table child_table add foreign key test_foreign_key (child_id,
child_name) references parent_table (id, name(200)); // 200
