Error in SQL Query Alter Table MySql

Question

I get an error when executing the 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)

What might I be doing wrong?

doza · Accepted Answer · 2009-11-25 08:36:25Z

2

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?

answered Nov 25, 2009 at 8:36

doza

1,56112 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Sunny Milenov Over a year ago

You saved my day with this link. Thanks. I spend hours of investigating why the create fails.

intgr · Accepted Answer · 2009-11-25 08:42:02Z

0

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.

answered Nov 25, 2009 at 8:42

intgr

20.7k6 gold badges63 silver badges73 bronze badges

Collectives™ on Stack Overflow

Error in SQL Query Alter Table MySql

2 Answers 2

1 Comment

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Related