CODEWITHSUNDEEP
mysqlphpmyadmin
DevT
DevT

Reputation: 4933

phpmyadmin doesnt allow me to add foreign key relationship

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!!!

Upvotes: 0

Views: 1662

Answers (1)

Namphibian
Namphibian

Reputation: 12221

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"

Upvotes: 2

Related Questions