Foreign Keys

[StuRReaL]

Its been a while since I did any seriously complex SQL, and so I've forgotten :s

Is it possible to have multiple foreign keys in a table? and if that is possible, can I have a foreign key which might contain a null?

My reason for doing this is that i have a list of countries in one table and a list of US states in another, and as everyone registering might not be from the US obviously the state field will contain a null.

I'm using MySQL and using the innodb engine so i have foreign key constraints.

[huuggee]

"Starting from version 3.23.50, InnoDB does not check foreign key constraints on those foreign key or referenced key values which contain a NULL column."

and if memory serves, you can have as many FK's as youlike. It's only PK's that you can have one of

[davemania]

Depending on the database, a FK can be null if the integrity rule isn't enforced. It's best not to use null for fk, for non-us, use a code i.e INT (for international)

[StuRReaL]

ok thanks :) i had just forgotten i'll get on with my site now

[StuRReaL]

Oh this is not a good day anyone have any ideas why my SQL script for MySQL 4.1 won' t execute it looks correct to me, theres a problem with the foreign keys but i can't see what it is. I've created the correct indices for each of the foreign keys and i've even added constraints the way the MySQL manual demonstrats

CREATE TABLE country (
cid int NOT NULL auto_increment,
name varchar(40) NOT NULL,
shortname varchar(3),
PRIMARY KEY (cid)
)type=myisam;

CREATE TABLE state (
sid int NOT NULL auto_increment,
name varchar(40) NOT NULL,
shortname varchar(3),
PRIMARY KEY (sid)
)type=myisam;

CREATE TABLE users (
uid int NOT NULL auto_increment,
email varchar(250) NOT NULL,
password varchar(32) NOT NULL,
firstname varchar(50) NOT NULL,
lastname varchar(50) NOT NULL,
dob varchar(10) NOT NULL,
address1 varchar(200) NOT NULL,
address2 varchar(100),
address3 varchar(100),
city varchar(100) NOT NULL,
postcode varchar(20),
phone varchar(16) NOT NULL,
company varchar(200),
cid int NOT NULL DEFAULT 0, /*country id from country table*/
sid int NOT NULL DEFAULT 0, /*state id from states table*/
PRIMARY KEY (uid),
UNIQUE KEY (email),
INDEX user_auth (email, password),
INDEX country_id (cid),
INDEX state_id (sid),
CONSTRAINT FOREIGN KEY country_fk(cid) REFERENCES country (cid) ON DELETE SET DEFAULT ON UPDATE CASCADE,
CONSTRAINT FOREIGN KEY state_fk(sid) REFERENCES state (sid) ON DELETE SET DEFAULT ON UPDATE CASCADE
)TYPE=INNODB;

Ok i've worked it out all should work perfectly if I change the ON DELETE SET DEFAULT to ON DELETE SET NULL

Edited February 4, 2005 by StuRReaL