469,603 Members | 2,098 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,603 developers. It's quick & easy.

reflexive foreign keys

Hi!

I have a question how to solve the folloing task:

I have 2 tables referencing each over: An user table and an address
table. Each user has one or no address. If an user is dropped the
address should be dropped, too. If an address is dropped its reference
in the user table should be set to null. I wrote the following create
statements:

CREATE TABLE user (
id int(10) unsigned NOT NULL auto_increment,
username varchar(255) NOT NULL default '',
password varchar(255) binary NOT NULL default '',
address int(10) unsigned default NULL,
PRIMARY KEY (id),
UNIQUE KEY username (username),
KEY address (address),
CONSTRAINT 0_1301
FOREIGN KEY (address)
REFERENCES address (id)
ON DELETE SET NULL
ON UPDATE CASCADE
) TYPE=InnoDB;
CREATE TABLE address (
id int(10) unsigned NOT NULL auto_increment,
lastname varchar(255) default NULL,
firstname varchar(255) default NULL,
[...]
PRIMARY KEY (id),
CONSTRAINT 0_1303
FOREIGN KEY (id)
REFERENCES user (address)
ON DELETE CASCADE
ON UPDATE CASCADE
) TYPE=InnoDB;
Now I insert an user:

INSERT INTO user (id, username, password, address)
VALUES (1,'username','password', NULL);

Later I want to assign an address to the user, but this is impossible,
because if insert the address first it fails, because its id is not in
user.address and if i first update user.address it fails because the
address is still not inserted.

How can I solve this without setting foreign_key_checks=0? If there is
no possibility, how can I be sure, that all foreign keys are correct
after switching back to foreign_key_checks=1?

thanks for help,
rainer
Jul 19 '05 #1
0 1538

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

5 posts views Thread by Olivier Crèvecoeur | last post: by
reply views Thread by Rainer Collet | last post: by
10 posts views Thread by Bodza Bodza | last post: by
1 post views Thread by Vinodh Kumar P | last post: by
2 posts views Thread by Ian Davies | last post: by
reply views Thread by guiromero | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.