SS 2005 Express- Allowing null values and Foriegn Key Constraints

[Rico]

Hello,

I have a foreign key constraint between two tables (Appointments and
MissedAppointme ntReasons) and I'd like to allow null values in Appointments
table for the field containing the MissedAppointme ntReason, but currently, I
get a Foreign Key Constraint Error when I try to add a record to the
Appointments table.

Any ideas how I can have the null values but still maintain the Foreign Key
Constraint?

Thanks!
Rick

[Jason Cumberland]

I would suggest adding a lookup value for "did not miss appointment"
and then storing the ID value of that in your appointments table. That
way you can keep the FK relationship and still have a valid value,
which you could even make the default value for the field for ease of
use.

On Dec 28, 1:57 pm, "Rico" <[e-mail] te:

Hello,

I have a foreign key constraint between two tables (Appointments and
MissedAppointme ntReasons) and I'd like to allow null values in Appointments
table for the field containing the MissedAppointme ntReason, but currently, I
get a Foreign Key Constraint Error when I try to add a record to the
Appointments table.

Any ideas how I can have the null values but still maintain the Foreign Key
Constraint?

Thanks!
Rick

[Erland Sommarskog]

Rico (yo*@me.com) writes:

I have a foreign key constraint between two tables (Appointments and
MissedAppointme ntReasons) and I'd like to allow null values in
Appointments table for the field containing the MissedAppointme ntReason,
but currently, I get a Foreign Key Constraint Error when I try to add a
record to the Appointments table.

Then you are writing a value to MissedAppointRe ason that does exist
in that table, for instance 0.

That is, NULL values do not cause FK violation. Try this:

CREATE TABLE first (a int NOT NULL,
CONSTRAINT pk_first PRIMARY KEY(a))
CREATE TABLE second (b int NOT NULL,
a int NULL,
CONSTRAINT pk_second PRIMARY KEY(b),
CONSTRAINT fk FOREIGN KEY (a) REFERENCES first(a))
go
INSERT first (a) VALUES (1)
go
INSERT second (b, a) VALUES( 1, 1) -- goes fine.
INSERT second (b, a) VALUES( 2, NULL) -- goes fine.
INSERT second (b, a) VALUES( 3, 2) -- error.
go
SELECT * FROM second
go
DROP TABLE second, first

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Rico]

Doh! Thanks Erland. I have no idea how that happened since I never user
defaults except for booleans and dates.

I suppose I should have double checked that before posting here. (*blush*)

Thanks for your help!

Rick

"Erland Sommarskog" <es****@sommars kog.sewrote in message
news:Xn******** *************@1 27.0.0.1...

Rico (yo*@me.com) writes:

>I have a foreign key constraint between two tables (Appointments and
MissedAppointm entReasons) and I'd like to allow null values in
Appointments table for the field containing the MissedAppointme ntReason,
but currently, I get a Foreign Key Constraint Error when I try to add a
record to the Appointments table.

Then you are writing a value to MissedAppointRe ason that does exist
in that table, for instance 0.

That is, NULL values do not cause FK violation. Try this:

CREATE TABLE first (a int NOT NULL,
CONSTRAINT pk_first PRIMARY KEY(a))
CREATE TABLE second (b int NOT NULL,
a int NULL,
CONSTRAINT pk_second PRIMARY KEY(b),
CONSTRAINT fk FOREIGN KEY (a) REFERENCES first(a))
go
INSERT first (a) VALUES (1)
go
INSERT second (b, a) VALUES( 1, 1) -- goes fine.
INSERT second (b, a) VALUES( 2, NULL) -- goes fine.
INSERT second (b, a) VALUES( 3, 2) -- error.
go
SELECT * FROM second
go
DROP TABLE second, first

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx