Thank you! V On Mon, Feb 14, 2011 at 9:08 PM, Gavin Towey <gto...@ffn.com> wrote:
> " 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. >