From: Heikki Tuuri To: Serdioukov Edouard Cc: [EMAIL PROTECTED] Sent: Tuesday, October 29, 2002 7:23 PM Subject: Re: InnoDB, Foreign Key
Edouard, I tested this on Linux using mysql-max-3.23.51, the .tar.gz distro. It worked ok. Are you sure you were using 3.23.51? Starting from 3.23.50, InnoDB does not check a foreign key constraint when the column value is NULL. The behavior you had would have happened with 3.23.49a, for example. http://www.innodb.com/ibman.html#InnoDB_foreign_keys Regards, Heikki sql query heikki@hundin:~/mysql-max-3.23.51-pc-linux-gnu-i686/bin> mysql test Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 to server version: 3.23.51-max-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> CREATE TABLE t_Folders ( -> FOLDER_ID integer NOT NULL, -> FOLDER_NAME varchar(50) NOT NULL, -> PARENT integer NULL, -> PRIMARY KEY (FOLDER_ID), -> KEY XIE1t_Folders (PARENT), -> FOREIGN KEY (PARENT) REFERENCES t_Folders (FOLDER_ID) ON DELETE CASCADE -> ) TYPE=INNODB; Query OK, 0 rows affected (0.03 sec) mysql> INSERT INTO t_Folders VALUES (1, 'Notes', NULL); Query OK, 1 row affected (0.01 sec) mysql> select * from t_Folders; +-----------+-------------+--------+ | FOLDER_ID | FOLDER_NAME | PARENT | +-----------+-------------+--------+ | 1 | Notes | NULL | +-----------+-------------+--------+ 1 row in set (0.01 sec) mysql> mysql> mysql> show create table t_Folders; +-----------+--------------------------------------------------------------- ---- ---------------------------------------------------------------------------- ---- ---------------------------------------------------------------------------- ---- ---------------------------------------------------------------------------- ---- ----------+ | Table | Create Table | +-----------+--------------------------------------------------------------- ---- ---------------------------------------------------------------------------- ---- ---------------------------------------------------------------------------- ---- ---------------------------------------------------------------------------- ---- ----------+ | t_Folders | CREATE TABLE `t_Folders` ( `FOLDER_ID` int(11) NOT NULL default '0', `FOLDER_NAME` varchar(50) NOT NULL default '', `PARENT` int(11) default NULL, PRIMARY KEY (`FOLDER_ID`), KEY `XIE1t_Folders` (`PARENT`), FOREIGN KEY (`PARENT`) REFERENCES `test.t_Folders` (`FOLDER_ID`) ON DELETE CAS CADE ) TYPE=InnoDB | +-----------+--------------------------------------------------------------- ---- ---------------------------------------------------------------------------- ---- ---------------------------------------------------------------------------- ---- ---------------------------------------------------------------------------- ---- ----------+ 1 row in set (0.00 sec) mysql> ----- Original Message ----- From: Serdioukov Edouard To: Heikki Tuuri Sent: Tuesday, October 29, 2002 6:55 PM Subject: RE: Question ... ----- Original Message ----- ... From: Serdioukov Edouard To: [EMAIL PROTECTED] Sent: Wednesday, October 23, 2002 3:42 PM Subject: Question Hello I use Linux Red Hat 7.3 and MySQL-max version 2.23.51. And I have one problem. I created table: CREATE TABLE t_Folders ( FOLDER_ID integer NOT NULL, FOLDER_NAME varchar(50) NOT NULL, PARENT integer NULL, PRIMARY KEY (FOLDER_ID), KEY XIE1t_Folders (PARENT), FOREIGN KEY (PARENT) REFERENCES t_Folders (FOLDER_ID) ON DELETE CASCADE ) TYPE=INNODB; And when I try insert this record INSERT INTO t_Folders VALUES (1, 'Notes', NULL); I get error message: 'ERROR 1216: Cannot add a child row: a foreign key constraint fails'. On Windows all is ok in this case. Can you advise? Thank you Eduard --------------------------------------------------------------------- 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
