phpmyadmin doesnt allow me to add foreign key relationship

Question

i tried to add foreign key but cudn't add it. i read several questions regarding this matter. but those options are not works with me. ( Tried as this way too

My table structure as below

tab_project

column Name

ProjectID -- > primary key

ProjectName

tab_project_day

Primary Key ----> ProjectID + Day

foreign key ----> ProjectID references ProjectID in tab_project

enter image description here

tab_photo

Primary Key ----> enter image description here

i want to create tab_photo foreign keys as follows:

tab_photo.ProjectID --- > reference tab_project_day.ProjectID

tab_photo.Day ------- > reference tab_project_day.day

in here it does not allow me to add tab_photo.Day key.

i tried this one--

ALTER TABLE `tab_photo` ADD FOREIGN KEY (`Day`) REFERENCES
`xyz`.`tab_project_day`(`Day`) ON DELETE RESTRICT ON UPDATE RESTRICT;

it gives below error :

#1005 - Can't create table 'xyz.#sql-97c_1c6' (errno: 150)

what is the reason for this? please help!!!

@legendinmaking yes... in tab_project_day has data. but no data in tab_photo? is that can be reason? — DevT, Jan 15 '13 at 06:59
there should be no value in tab_photo.Day which is not present in tab_project_day.Day — Manoj Purohit, Jan 15 '13 at 07:03
@legendinmaking i removed all data from tab_project_day table too.. but still it doesn't allow me to add foreign key — DevT, Jan 15 '13 at 07:03

score 2 · Accepted Answer · answered Jan 15 '13 at 07:11

Please check the collation order of the two tables. I have found that if table A is not the same collation as table B the foreign key can tbe created. Both tables have to be the same collation. Also the columns need to be the same collation.

I suspect this might be your problem. Run the following queries to see the collation

Table Collation:

SELECT   TABLE_NAME,
         TABLE_COLLATION
 FROM    INFORMATION_SCHEMA.TABLES
 WHERE   TABLE_NAME = "tab_project" 
         OR TABLE_NAME="tab_project_day"

Column Collation:

SELECT       TABLE_NAME
             , COLUMN_NAME
             , COLLATION_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE   TABLE_NAME = "tab_project" 
        OR TABLE_NAME="tab_project_day"

I also hit my head on that one. It was maddening beyond belief as I could not figure it out then I just recreated the tables in a new database and it worked. Took me a while to realise the collations where wrong. — Namphibian, Jan 15 '13 at 09:16

phpmyadmin doesnt allow me to add foreign key relationship

1 Answers1