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