|
|
Posted by Erland Sommarskog on 12/28/06 22:38
Rico (you@me.com) writes:
> I have a foreign key constraint between two tables (Appointments and
> MissedAppointmentReasons) and I'd like to allow null values in
> Appointments table for the field containing the MissedAppointmentReason,
> 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 MissedAppointReason 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, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|