Re: Re: Foreign Keys and NULL

2002-05-30 Thread Egor Egorov 

Chris,
Thursday, May 30, 2002, 1:58:54 AM, you wrote:

 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 CASCADE) TYPE=INNODB;


CK OI!!!  I'm going to sound like a complete ass now (I know) But since
CK when does MySQL support FOREIGN keys?? *gasp* MAN this is going to
CK make my life S much easier *curses at phpMyAdmin for not having the
CK option*

Yes, MySQL has foreign key constraints, but ON DELETE CASCADE is
supported since 3.23.50:
  http://www.mysql.com/doc/S/E/SEC446.html

CK *lick* *bounce* *all happy now* *goes to redo all his DBs*
CK Now, I like MySQL 10 times more





-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Egor Egorov
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.net
   ___/   www.mysql.com



-
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

Foreign Keys and NULL

2002-05-29 Thread ashok 

Hi,

I am evaluating InnoDB's Foreign Keys and am stuck.

Using the sample tables I want to add records in the Child table with a NULL 
parent_id (this being referenced to the Parent table) as follows:
 

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 CASCADE) TYPE=INNODB;

mysql insert into parent values(1);
Query OK, 1 row affected (0.00 sec)

mysql insert into child (id, parent_id) values(4, 1);
Query OK, 1 row affected (0.00 sec)

mysql insert into child (id, parent_id) values(5, null);
ERROR 1216: Cannot add a child row: a foreign key constraint fails

mysql insert into child (id) values(5);
ERROR 1216: Cannot add a child row: a foreign key constraint fails

Is this possible in InnoDB ?

Is there a setting that I have missed ?
 
I am using:mysqld-max.exe Ver 4.0.1-alpha-max for Win95/Win98 on i32


Any help will be highly appreciated.

Thanks in advance.

Ashok Kumar Parmar
ISL NZ Limited.



-
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

Re: Foreign Keys and NULL

2002-05-29 Thread Chris Knipe 

 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 CASCADE) TYPE=INNODB;


OI!!!  I'm going to sound like a complete ass now (I know) But since
when does MySQL support FOREIGN keys?? *gasp* MAN this is going to
make my life S much easier *curses at phpMyAdmin for not having the
option*

*lick* *bounce* *all happy now* *goes to redo all his DBs*

Now, I like MySQL 10 times more



-
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

Re: Foreign Keys and NULL

2002-05-29 Thread Gabriel 

snip tables

 OI!!!  I'm going to sound like a complete ass now (I know) But since
 when does MySQL support FOREIGN keys?? *gasp* MAN this is going to
 make my life S much easier *curses at phpMyAdmin for not having the
 option*

Do remember that this is only valid for mysql 3.23.50+ and 4.0.2+.

 Now, I like MySQL 10 times more

Cool. :)

-- 
Gabriel Cain
Unix Systems Administrator   [EMAIL PROTECTED]
Dialup USA, Inc.  888-460-2286 ext 208


-
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