Paul,
----- Original Message -----
From: "Paul Vincent Craven" <[EMAIL PROTECTED]>
Newsgroups: mailing.database.mysql
Sent: Saturday, November 30, 2002 6:32 AM
Subject: Enforcement of foreign keys
> Shouldn't the following be illegal?
>
> drop database registry;
> create database registry;
> use registry;
>
> CREATE TABLE parent(id INT NOT NULL, PRIMARY KEY (id)) TYPE=INNODB;
> CREATE TABLE child(id INT, parent_id INT, INDEX par_ind (parent_id),
> FOREIGN KEY (parent_id) REFERENCES parent(id)
> ON DELETE SET NULL
> ) TYPE=INNODB;
>
> insert into child values(1,1);
> insert into child values(1,1);
> insert into child values(1,1);
> insert into child values(1,1);
>
> MySQL is inserting these items just fine. As there are no parent
> records, I'm not sure why it doesn't error out. When I do a status
> command, the server version comes across as:
> Server version: 3.23.53a-Max
>
> What am I missing?
probably you have not put the line
innodb_data_file_path=ibdata1:10M:autoextend
to the [mysqld] section of my.cnf. You have InnoDB disabled and MySQL
creates your tables in the MyISAM type. You can use SHOW CREATE TABLE
tablename to check what is the table type.
I tested the script:
heikki@hundin:~/mysql/client> mysql test
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 3.23.53a-max-log
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> drop database registry;
ERROR 1008: Can't drop database 'registry'. Database doesn't exist
mysql> create database registry;
Query OK, 1 row affected (0.01 sec)
mysql> use registry;
Database changed
mysql>
mysql> CREATE TABLE parent(id INT NOT NULL, PRIMARY KEY (id)) TYPE=INNODB;
Query OK, 0 rows affected (0.03 sec)
mysql> CREATE TABLE child(id INT, parent_id INT, INDEX par_ind (parent_id),
-> FOREIGN KEY (parent_id) REFERENCES parent(id)
-> ON DELETE SET NULL
-> ) TYPE=INNODB;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> insert into child values(1,1);
ERROR 1216: Cannot add a child row: a foreign key constraint fails
mysql> insert into child values(1,1);
ERROR 1216: Cannot add a child row: a foreign key constraint fails
mysql> insert into child values(1,1);
ERROR 1216: Cannot add a child row: a foreign key constraint fails
mysql> insert into child values(1,1);
ERROR 1216: Cannot add a child row: a foreign key constraint fails
mysql>
mysql> show create table child;
+-------+-------------------------------------------------------------------
----
----------------------------------------------------------------------------
----
------------------------------------------------------------------+
| Table | Create Table
|
+-------+-------------------------------------------------------------------
----
----------------------------------------------------------------------------
----
------------------------------------------------------------------+
| child | CREATE TABLE `child` (
`id` int(11) default NULL,
`parent_id` int(11) default NULL,
KEY `par_ind` (`parent_id`),
FOREIGN KEY (`parent_id`) REFERENCES `registry.parent` (`id`) ON DELETE
SET NU
LL
) TYPE=InnoDB |
+-------+-------------------------------------------------------------------
----
----------------------------------------------------------------------------
----
------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql>
> Paul Vincent Craven
Best regards,
Heikki Tuuri
Innobase Oy
---
InnoDB - transactions, hot backup, and foreign key support for MySQL
See http://www.innodb.com, download MySQL-Max from http://www.mysql.com
sql query
---------------------------------------------------------------------
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
