Peter,
InnoDB does not allow the index name to be specified in a foreign key
constraint:
"
The syntax of a foreign key constraint definition in InnoDB:
[CONSTRAINT symbol] FOREIGN KEY (index_col_name, ...)
REFERENCES table_name (index_col_name, ...)
[ON DELETE CASCADE | ON DELETE SET NULL]
Both tables have to be InnoDB type and there must be an index where the
foreign key and the referenced key are listed as the first columns. InnoDB
does not auto-create indexes on foreign keys or referenced keys: you have to
create them explicitly.
"
Since the syntax is wrong in the test run below, InnoDB simply ignores the
foreign key specification. I have to change the InnoDB parser so that it
gives an error in this case.
Tested with InnoDB-4.0.5:
mysql> CREATE TABLE pub_user(
-> user_id INT UNSIGNED,
-> parent_id INT,
-> user_fname VARCHAR (25),
-> user_lname VARCHAR (25),
-> user_password VARCHAR (25),
-> INDEX par_ind(parent_id),
-> FOREIGN KEY pub_user (parent_id) REFERENCES
pub_access_type(access_type_
id)
-> ON DELETE SET NULL
-> ) TYPE=INNODB;
Query OK, 0 rows affected (0.00 sec)
mysql> show create table pub_user;
+----------+----------------------------------------------------------------
----
----------------------------------------------------------------------------
----
----------------------------------------------------------------------------
----
------------------------------------------------+
| Table | Create Table
|
+----------+----------------------------------------------------------------
----
----------------------------------------------------------------------------
----
----------------------------------------------------------------------------
----
------------------------------------------------+
| pub_user | CREATE TABLE `pub_user` (
`user_id` int(10) unsigned default NULL,
`parent_id` int(11) default NULL,
`user_fname` varchar(25) default NULL,
`user_lname` varchar(25) default NULL,
`user_password` varchar(25) default NULL,
KEY `par_ind` (`parent_id`)
) TYPE=InnoDB |
+----------+----------------------------------------------------------------
----
----------------------------------------------------------------------------
----
----------------------------------------------------------------------------
----
------------------------------------------------+
1 row in set (0.00 sec)
mysql>
Regards,
Heikki
Innobase Oy
sql query
.................
Subject: InnoDb referencing key bug?
From: Peter Brawley
Date: Tue, 29 Oct 2002 14:13:45 -0800
................
In Jannie's problem,
CREATE TABLE pub_access_type(
access_type_id INT UNSIGNED NOT NULL,
access_type_name VARCHAR(25),
PRIMARY KEY(access_type_id)
) TYPE=INNODB;
CREATE TABLE pub_user(
user_id INT UNSIGNED,
parent_id INT,
user_fname VARCHAR (25),
user_lname VARCHAR (25),
user_password VARCHAR (25),
INDEX par_ind(parent_id),
FOREIGN KEY parent_id REFERENCES pub_access_type(access_type_id)
ON DELETE SET NULL
) TYPE=INNODB;
MySQL correctly refuses to create pub_user because it has been asked to
reference an unsigned key, pub_access_type(access_type_id), with the signed
key parent_id.
Jannie found that MySQL accepts the incorrect key reference if the
referencing key is aliased ...
...
FOREIGN KEY pub_user(parent_id) ...
...
But consider what will happen when parent_id wraps around. Looks like a bug.
PB
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
