Foreign Keys Don't Work?

[clops]

Dear Collegues,

can anyone explain me WHY the following set of instructions does not
spit any errors (MySQL 5.017) -- the second insert should not have
worked as it references a non-existing record!

mysql> CREATE TABLE struct(
-> node_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
-> parent_node_id INT UNSIGNED REFERENCES
ep_catalog_structure.node_id ON DELETE CASCADE ON UPDATE CASCADE,
->
-> # Labels
-> name VARCHAR(255) NOT NULL,
->
-> PRIMARY KEY(node_id)
-> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.03 sec)

mysql> insert into struct set name = 'Hello World';
Query OK, 1 row affected (0.00 sec)

mysql> insert into struct set name = 'Hello World Child',
parent_node_id = 1;
Query OK, 1 row affected (0.03 sec)

mysql> insert into struct set name = 'Hello World Child',
parent_node_id = 6;
Query OK, 1 row affected (0.00 sec)

mysql> select * from struct;
+---------+----------------+-------------------+
| node_id | parent_node_id | name |
+---------+----------------+-------------------+
| 1 | NULL | Hello World |
| 2 | 1 | Hello World Child |
| 3 | 6 | Hello World Child |
+---------+----------------+-------------------+
3 rows in set (0.00 sec)

mysql>

[clops]

of course I reference "struct.node_id" -- just a small typo while
renaming the table for this post

[Bill Karwin]

clops wrote:

mysql> CREATE TABLE struct(
-> node_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
-> parent_node_id INT UNSIGNED REFERENCES
struct.node_id ON DELETE CASCADE ON UPDATE CASCADE,

.. . .

Normally the syntax is "REFERENCES tablename(columnname)", not
"tablename.columnname".

See http://dev.mysql.com/doc/refman/5.0/...ate-table.html

I'm not sure how MySQL is interepreting this CREATE TABLE, or why it
didn't give you an error. But it may not be enforcing what you think
it's enforcing.

Regards,
Bill K.

[Heikki Tuuri]

Hi!

It is this bug:

http://bugs.mysql.com/bug.php?id=13301

MySQL still does not give a warning for syntax that does not work.

Best regards,

Heikki

Oracle Corp./Innobase Oy
InnoDB - transactions, row level locking, and foreign keys for MySQL

InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM
tables
http://www.innodb.com/order.php

"Bill Karwin" <bi**@karwin.com> kirjoitti
viestissä:e2********@enews2.newsguy.com...

clops wrote:

mysql> CREATE TABLE struct(
-> node_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
-> parent_node_id INT UNSIGNED REFERENCES
struct.node_id ON DELETE CASCADE ON UPDATE CASCADE,

. . .

Normally the syntax is "REFERENCES tablename(columnname)", not
"tablename.columnname".

See http://dev.mysql.com/doc/refman/5.0/...ate-table.html

I'm not sure how MySQL is interepreting this CREATE TABLE, or why it
didn't give you an error. But it may not be enforcing what you think it's
enforcing.

Regards,
Bill K.

[clops]

thanks!!

I got it working with the normal sytax "FOREIGN KEY (col) REFERENCES
blablabla"

regards,

ak

[clops]

thanks!!

I got it working with the normal sytax "FOREIGN KEY (col) REFERENCES
blablabla"

regards,

ak