Reply to Re: SS 2005 Express- Allowing null values and Foriegn Key Constraints

Your name:

Reply:


Posted by Rico on 12/29/06 02:17

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" <esquel@sommarskog.se> wrote in message
news:Xns98A7F088A665Yazorman@127.0.0.1...
> 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

[Back to original message]


Удаленная работа для программистов  •  Как заработать на Google AdSense  •  England, UK  •  статьи на английском  •  PHP MySQL CMS Apache Oscommerce  •  Online Business Knowledge Base  •  DVD MP3 AVI MP4 players codecs conversion help
Home  •  Search  •  Site Map  •  Set as Homepage  •  Add to Favourites

Copyright © 2005-2006 Powered by Custom PHP Programming

Сайт изготовлен в Студии Валентина Петручека
изготовление и поддержка веб-сайтов, разработка программного обеспечения, поисковая оптимизация