[EMAIL PROTECTED] wrote:
(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
Oki, I will check ... thanks
KEY KM_INDEX_ATTRIBUTE_INSTANCE (id),
Your primary key already indexes this column. This index is also not
needed.
Oki, I will check also.
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?
Yes, read_restring is also an InnoDB table and `read_restring`.`id` is
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.
Show InnoDB status didn't give useful information in this case.
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.
Thanks for your tip. I did reorganize my script and everything works fine.
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.
Thanks and regards
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine