"if you have a foreign key constraint, that field must be one of the values of the referenced column and that column, being an index, is never NULL"
OK so my understanding is that it is impossible with mySQL to do such things wich are basic with at least: Oracle Sybase Adaptive Server Enterprise Sybase Adaptive Server Anywhere Thanks, Alain ----- Original Message ----- From: "Rance Hall" <[EMAIL PROTECTED]> To: "Alain Del Giust" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Tuesday, April 09, 2002 6:42 PM Subject: RE: mySQL and innoDB well, your right about one thing, I did misread the order of your SQL statement, but if you have a foreign key constraint, that field must be one of the values of the referenced column, and that column, being an index, is never NULL so inserting NULL into a properly configured foreign key column should fail inserting anything into that column that isn't in the referenced column should fail, and it does. so my apologies for my mistake from earlier Rance Hall 308.238.2455 Internal Office Extensions: 2455 or 6655 PC Programmer, The Buckle, Inc. [EMAIL PROTECTED] -----Original Message----- From: Alain Del Giust [mailto:[EMAIL PROTECTED]] Sent: Tuesday, April 09, 2002 11:45 AM To: Rance Hall; [EMAIL PROTECTED] Subject: Re: mySQL and innoDB Nope insert into b values ('BBB',null); here: BBB is PK of table b null is FK of table b referencing table a for example insert into b values ('BBB','aaa'); works well Alain ----- Original Message ----- From: "Rance Hall" <[EMAIL PROTECTED]> To: "Alain Del Giust" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Tuesday, April 09, 2002 6:25 PM Subject: RE: mySQL and innoDB The "insert into b values ('BBB', NULL)" statement should fail under the conditions you describe, BBB was not inserted into the A table, and therefore the foreign key constraint fails as it should. Rance Hall 308.238.2455 Internal Office Extensions: 2455 or 6655 PC Programmer, The Buckle, Inc. [EMAIL PROTECTED] -----Original Message----- From: Alain Del Giust [mailto:[EMAIL PROTECTED]] Sent: Tuesday, April 09, 2002 11:28 AM To: [EMAIL PROTECTED] Subject: mySQL and innoDB Hi all, I am pretty new to mySQL actually I started yesterday. mySQL with innoDB handles integrity constraints. I tried : create table a ( id varchar(3) not null, primary key(id) ) TYPE=INNODB; insert into a values ('aaa'); create table b ( id varchar(3) not null, a_id varchar(3), index idx_b_a_id (a_id), foreign key (a_id) references a (id), primary key(id) ) TYPE=INNODB; insert into b values ('AAA','aaa'); insert into b values ('BBB',null); the last statment doesn't work ... ERROR 1216: Cannot add a child row: a foreign key constraint fails I am using mySQL version 3.23.49 (and 4.0.1-alpha) on win2Ksp2 "mysqld-max --standalone --console" Thanks, Alain --------------------------------------------------------------------- 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 --------------------------------------------------------------------- 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
