Foreign Key Error

--0023544fb8dc1d1de0049c4623c8
Content-Type: text/plain; charset=ISO-8859-1

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

--0023544fb8dc1d1de0049c4623c8--

RE: Foreign Key Error

" or column types in the table and the referenced table do not match for co=
nstraint"

The columns Parent and Child are signed integers and ID is unsigned.

Regards,
Gavin Towey

-----Original Message-----
From: Victor Subervi [mailto:victorsubervi@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=3Dinnodb;

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=3Dinnodb:
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 >=3D 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-co nstraints.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 individua=
l to whom, or entity to which, it is addressed and may contain information =
that is privileged, confidential and exempt from disclosure under applicabl=
e law. If you are NOT the intended recipient, you are hereby notified that =
any use, dissemination, distribution or copying of this communication is st=
rictly prohibited. If you have received this communication in error, pleas=
e 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=3Dgcdmg-mysql-2@m.gmane.o rg

Re: Foreign Key Error

--0023547c90230cb443049c485508
Content-Type: text/plain; charset=ISO-8859-1

Thank you!
V

On Mon, Feb 14, 2011 at 9:08 PM, Gavin Towey 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:victorsubervi@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-co nstraints.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.
>

--0023547c90230cb443049c485508--