create fails with on delete set null

2003-02-21 Thread Ross Davis 
Simply said this works:
drop table if exists detail;
create table detail
(
detail_id int not null auto_increment
,  master_id int not null
,  name varchar(50) not null
,  primary key (detail_id)
,  index master_idx(master_id)
,  foreign key (master_id) references master (master_id) on delete cascade 
on update cascade
)
type=InnoDB; 

This doesn't:
drop table if exists detail;
create table detail
(
detail_id int not null auto_increment
,  master_id int not null
,  name varchar(50) not null
,  primary key (detail_id)
,  index master_idx(master_id)
,  foreign key (master_id) references master (master_id) on delete set null 
on update cascade
)
type=InnoDB; 

mysql 4.0.10 on Windows XP Pro 

Anyone else have this problem?  Is it a bug? 

Ross

-
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: create fails with on delete set null

2003-02-21 Thread Heikki Tuuri 
Ross,

- Original Message -
From: Ross Davis [EMAIL PROTECTED]
Newsgroups: mailing.database.mysql
Sent: Saturday, February 22, 2003 12:14 AM
Subject: create fails with on delete set null



 Simply said this works:
 drop table if exists detail;
 create table detail
 (
  detail_id int not null auto_increment
 ,  master_id int not null
 ,  name varchar(50) not null
 ,  primary key (detail_id)
 ,  index master_idx(master_id)
 ,  foreign key (master_id) references master (master_id) on delete cascade
 on update cascade
 )
 type=InnoDB;

 This doesn't:
 drop table if exists detail;
 create table detail
 (
  detail_id int not null auto_increment
 ,  master_id int not null
 ,  name varchar(50) not null
 ,  primary key (detail_id)
 ,  index master_idx(master_id)
 ,  foreign key (master_id) references master (master_id) on delete set
null
 on update cascade
 )
 type=InnoDB;

 mysql 4.0.10 on Windows XP Pro

 Anyone else have this problem?  Is it a bug?

you have defined master_id as not null, but want it to be set null on the
delete of the parent row. That is why InnoDB gives an error.

 Ross

Regards,

Heikki
Innobase Oy
sql query




-
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: create fails with on delete set null

2003-02-21 Thread Paul DuBois 
At 22:07 + 2/21/03, Ross Davis wrote:
Simply said this works:
drop table if exists detail;
create table detail
(
detail_id int not null auto_increment
,  master_id int not null
,  name varchar(50) not null
,  primary key (detail_id)
,  index master_idx(master_id)
,  foreign key (master_id) references master (master_id) on delete 
cascade on update cascade
)
type=InnoDB;
This doesn't:
drop table if exists detail;
create table detail
(
detail_id int not null auto_increment
,  master_id int not null
,  name varchar(50) not null
,  primary key (detail_id)
,  index master_idx(master_id)
,  foreign key (master_id) references master (master_id) on delete 
set null on update cascade
)
type=InnoDB;
mysql 4.0.10 on Windows XP Pro
Anyone else have this problem?  Is it a bug?
It's a bug on your part. :-)

You've defined master_id as NOT NULL.  How to you expect it to be
set to NULL when you delete a parent table record?
Ross


-
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