Error in SQL Query Alter Table MySql .

Question

Got an error if I executed below query.

ALTER TABLE property_res_details 
ADD CONSTRAINT PropertyIdLink FOREIGN KEY ( Property_ID )  
REFERENCES properties( Property_ID )  ON DELETE CASCADE ;

#1005 - Can't create table './resfi/#sql-10e1_8df.frm' (errno: 150)

Please Help Me Out.

Answer 1

This page might give a clue as to what's going on...

Since you're adding a foreign key, it sounds relevant. It suggests you try

SHOW ENGINE INNODB STATUS;

to see the latest constraint error which may cause the error you're seeing.

Maybe if you try

SET FOREIGN_KEY_CHECKS = 0;

before your command, it will disable the checks and allow you to continue?

Answer 2

Yeah, don't you just love MySQL's awesome error messages.

ADD CONSTRAINT PropertyIdLink FOREIGN KEY ( Property_ID )  
REFERENCES properties( Property_ID ) ON DELETE CASCADE;

Make sure that the Property_ID columns have the exact same datatype in both tables. That's how I have fixed similar errors for myself in the past.