(response interspersed) Boris Villazon <[EMAIL PROTECTED]> wrote on 10/14/2005 08:30:13 AM:
> Hi > > I am working with MySql 4.1.14 under Windows XP. > > I am trying to do a database import from a file. > > I have the following table definiton > > CREATE TABLE attribute_instance ( > name varchar(200) NOT NULL default '', > id int(11) NOT NULL default '0', > PRIMARY KEY (id,name), > KEY id (id,name), This index duplicates your primary key and is not needed > KEY KM_INDEX_ATTRIBUTE_INSTANCE (id), Your primary key already indexes this column. This index is also not needed. > CONSTRAINT `attribute_instance_ibfk_1` FOREIGN KEY (`id`) REFERENCES > `read_restring` (`id`) ON DELETE CASCADE ON UPDATE CASCADE Is `read_restring` also an InnoDB table? Is `read_restring`.`id` also indexed? > ) TYPE=InnoDB; > > When Mysql tries to create this table It shows the following error message : > > ERROR 1005 (HY000) at line 15: Can't create table > '.\x\attribute_instance.frm' ( > errno: 150) > > MySQL error code 150: Foreign key constraint is incorrectly formed Is that really what SHOW INNODB STATUS said or is that the output of perror? Whenever working with Innodb, if you get an error always check SHOW INNODB STATUS for more complete details. > > It seems to be a foreing key error. It is but exactly what was wrong with the foreign key you didn't say. You get that from SHOW INNODB STATUS. At this point in your script, does `read_restring` even exist? If it doesn't, you may need to reorganize your script. If `attribute_instance` has data already in it and at least one row has an `id` value that isn't on `read_restring`, that would also cause an error 150. > > But the database was working fine. I only did an export and then I want > to import of the same database. > > Has anybody an idea of this error? Any advice? > > Thanks in advance and regards > > Boris Villazón > Check my comments above and get back to us with the error description from SHOW INNODB STATUS immediately after re-creating the error if you are still having problems. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
