SEO
vBulletin Search Engine Optimization
| |||||||
| Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read |
02-28-2008, 05:49 AM
| ||||
| ||||
 Alter table - adding constraints? I really donšt know what to do because I keep getting this error. Any ideas? SQL query: ALTER TABLE pl_reports ADD CONSTRAINT fk_region FOREIGN KEY ( region ) REFERENCES Region( id ) ON UPDATE CASCADE ON DELETE CASCADE MySQL said: Documentation #1005 - Can't create table './survey_localhost/#sql-113_f8.frm' (errno: 150) Thanks!      |
|
02-28-2008, 05:49 AM
| |||
| |||
| Re: Alter table - adding constraints? Jay Paulson wrote: > I really donšt know what to do because I keep getting this error. Any > ideas? > > SQL query: > > ALTER TABLE pl_reports ADD CONSTRAINT fk_region FOREIGN KEY ( region ) > REFERENCES Region( id ) ON UPDATE CASCADE ON DELETE CASCADE > > MySQL said: Documentation > #1005 - Can't create table './survey_localhost/#sql-113_f8.frm' (errno: 150) > > Thanks! > > 1) Are both tables InnoDB? 2) Do you have data in either tables? 3) Are the data types of both columns exactly the same ( int(20) unsigned and int(20) will fail, the unsigned has to be there)? you can also run SHOW INNODB STATUS as root to find out exactly what the error is. |
|
02-28-2008, 05:50 AM
| |||
| |||
| Re: Alter table - adding constraints? 1) both tables are InnoDB. 2) both tables have data in them. 3) both table are the exact same data types. On 2/20/07 3:51 PM, "Chris White" <chriswhite@interfuel.com> wrote: > Jay Paulson wrote: >> I really donšt know what to do because I keep getting this error. Any >> ideas? >> >> SQL query: >> >> ALTER TABLE pl_reports ADD CONSTRAINT fk_region FOREIGN KEY ( region ) >> REFERENCES Region( id ) ON UPDATE CASCADE ON DELETE CASCADE >> >> MySQL said: Documentation >> #1005 - Can't create table './survey_localhost/#sql-113_f8.frm' (errno: 150) >> >> Thanks! >> >> > 1) Are both tables InnoDB? > 2) Do you have data in either tables? > 3) Are the data types of both columns exactly the same ( int(20) > unsigned and int(20) will fail, the unsigned has to be there)? > > you can also run SHOW INNODB STATUS as root to find out exactly what the > error is. |
|
02-28-2008, 05:50 AM
| |||
| |||
| Re: Alter table - adding constraints? Jay Paulson wrote: > 2) both tables have data in them. > > This is most likely your issue then, depending on the table size, go through and make sure that anything in the referenced column matches the referencing column. You should also be able to use SHOW INNODB STATUS to see what's possibly failing. |
|
02-28-2008, 05:50 AM
| ||||
| ||||
| Re: Alter table - adding constraints? Chris White wrote: > Jay Paulson wrote: >> 2) both tables have data in them. >> >> > This is most likely your issue then, depending on the table size, go > through and make sure that anything in the referenced column matches the > referencing column. You should also be able to use SHOW INNODB STATUS > to see what's possibly failing. No, it's because you already have a constraint in your schema called fk_regions. Just change the constraint name to one that is unique for your schema... This is the reason I usually name myu constraints like: fk_fromtable_totable So this doesn't become an issue. cheers, Jay |
Linear Mode
