" or column types in the table and the referenced table do not match for constraint"
The columns Parent and Child are signed integers and ID is unsigned. Regards, Gavin Towey -----Original Message----- From: Victor Subervi [mailto:victorsube...@gmail.com] Sent: Monday, February 14, 2011 3:09 PM To: mysql@lists.mysql.com Subject: Foreign Key Error Hi; I have this command: create table if not exists categoriesRelationships (ID integer auto_increment primary key, Store varchar(60), Parent integer not null, foreign key (Parent) references categories (ID), Child integer not null, foreign key (Child) references categories (ID)) engine=innodb; show innodb status prints out this: ------------------------ LATEST FOREIGN KEY ERROR ------------------------ 110214 15:03:43 Error in foreign key constraint of table test/categoriesRelationships: foreign key (Parent) references categories (ID), Child integer not null, foreign key (Child) references categories (ID)) engine=innodb: Cannot find an index in the referenced table where the referenced columns appear as the first columns, or column types in the table and the referenced table do not match for constraint. Note that the internal storage type of ENUM and SET changed in tables created with >= InnoDB-4.1.12, and such columns in old tables cannot be referenced by such columns in new tables. See http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html for correct foreign key definition. mysql> describe categories; +----------+-----------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+-----------------+------+-----+---------+----------------+ | ID | int(3) unsigned | NO | PRI | NULL | auto_increment | | Store | varchar(60) | YES | | NULL | | | Category | varchar(40) | YES | | NULL | | | Parent | varchar(40) | YES | | NULL | | +----------+-----------------+------+-----+---------+----------------+ 4 rows in set (0.00 sec) Please advise. TIA, Victor IMPORTANT: This email message is intended only for the use of the individual to whom, or entity to which, it is addressed and may contain information that is privileged, confidential and exempt from disclosure under applicable law. If you are NOT the intended recipient, you are hereby notified that any use, dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please reply to the sender immediately and permanently delete this email. Thank you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
