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.
>

Reply via email to