Hi! >From the changelog of 3.23.50:
* We suppress the FOREIGN KEY check if any of the column values in the foreign key or referenced key to be checked is the SQL NULL. This is compatible with Oracle, for example. It is conceptually a bit strange that we first declare a FOREIGN KEY constraint, but then let a user to slip it through using a NULL value. But, since we may declare a column as NOT NULL, we can still force full referential integrity in 3.23.50. Best regards, Heikki Tuuri Innobase Oy --- InnoDB - transactions, row level locking, and foreign key support for MySQL See http://www.innodb.com, download MySQL-Max from http://www.mysql.com ----- Original Message ----- From: ""Alain Del Giust"" <[EMAIL PROTECTED]> Newsgroups: mailing.database.mysql Sent: Tuesday, April 09, 2002 7:59 PM Subject: Re: mySQL and innoDB > "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 > --------------------------------------------------------------------- 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
