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

Reply via email to