Re: Adding Foreign Key

[Ady Wicaksono]

Thanks Mr Heikki
I try to remove ON DELETE SET DEFAULT
like this
mysql> alter table t_quiz_trivia add foreign key (client_id) references 
t_client(client_id);

I found new error
ERROR 1216 (23000): Cannot add or update a child row: a foreign key 
constraint fails

Finally i found the error
1. DDL of the t_client is like this
CREATE TABLE `t_client` (
`client_id` int(11) NOT NULL default '0',
`client_desc` varchar(255) NOT NULL default '',
PRIMARY KEY  (`client_id`)
) TYPE=InnoDB;
2. I change field client_id on t_quiz_trivia
from
`client_id` int(11) NOT NULL default '0'
to
`client_id` int(11) NOT NULL
no default value and create index on this field
3. I re run
alter table t_quiz_trivia add foreign key (client_id) references 
t_client (client_id);

and it works
Thanks all
Heikki Tuuri wrote:
Ady,
----- Original Message ----- From: "Ady Wicaksono" 
<[EMAIL PROTECTED]>
Newsgroups: mailing.database.myodbc
Sent: Wednesday, December 01, 2004 6:43 AM
Subject: Adding Foreign Key


mysql> alter table t_quiz_trivia add foreign key (client_id) references
t_client (client_id) on delete set default;
ERROR 1005: Can't create table './smsserver/#sql-215d_11eff.frm' (errno:
150)

sorry, InnoDB does not support SET DEFAULT yet.
Why not use ON DELETE SET NULL? Of course, you have to remove the NOT 
NULL clause from client_id then.

May i know what makes error ?
Here is the reference table
CREATE TABLE `t_client` (
 `client_id` int(11) NOT NULL default '0',
 `client_desc` varchar(255) NOT NULL default '',
 PRIMARY KEY  (`client_id`)
) TYPE=InnoDB;

Best regards,
Heikki Tuuri
Innobase Oy
Foreign keys, transactions, and row level locking for MySQL
InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up 
MyISAM tables
http://www.innodb.com/order.php