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