Posted by Erland Sommarskog on 03/21/06 00:57

David Portas (REMOVE_BEFORE_REPLYING_dportas@acm.org) writes:
> Change it to:
>
> CHECK (R_ID1 = R_ID)
>
> The UNKNOWN case where R_ID1 is null will still be permitted.

Actually, the data-modelling tool that I use, PowerDesiger 9.5, insist on
adding IS NULL conditions to all my column constraints for my nullable
columns. I would guess the reason for this is that there was a bug in SQL
2000 RTM where NULL values actually can give you constraint violations.
(There is a similar bug with rules that has been around since SQL 7 RTM,
and I suspect never will get fixed.)

> Better still, get rid of R_ID1, which is apparently redundant - except
> maybe if it is part of a foreign key. In the case of a foreign key I
> would still look for a better design without the nullable column.

To me it looks like a funny sort of bit column, as there are only two
possible values. But maybe Susanne only gave us a scaled-down example,
and the resl-world table looks a little different.


--
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]