One column is nullable on the composite key

[Andy]

Hi All!

I would like to have a composite PK on 3 columns, one of them is null
CREATE TABLE TableA (
ColA int NOT NULL ,
ColB int NOT NULL ,
ColC char (3) NULL ,
......
)
GO

ALTER TABLE TableA ADD
CONSTRAINT TableA_PK PRIMARY KEY CLUSTERED
(
ColA,
ColB,
ColC
)
GO
SQL Server does not allow having a composite PK with one nullable column:

What is wrong to have values?
1,100,NULL
1,200,ABC
1,200,ABD
.....

Code in C applies to Values in B and for some values in B the code does not exist.

I can work out and define a special Code:
NEV(not existing value), but in general I do not understand this restriction.

Thanks

[Anith Sen]

It is simple: You cannot identify something, if a portion of the identifying
information is missing.

Considering your example, if you have another row, say ( 1, 100, XYZ ) would
it be sensible to have a value missing in the composite key? BTW, SQL has
screwed up this area royally with FKs allowing NULLs in composite key
references -- which is why surrogates are recommended in certain scenarios.

--
Anith

[--CELKO--]

>> I can work out and define a special Code: NEV(not existing value),
but in general I do not understand this restriction. <<

Get a book on RDBMS; read the part about keys; read the definition of
NULL. That ought to answer your question. This is like thinking that
you can locate a point on earth without both Longitude and Latitude.

[Erland Sommarskog]

Andy (ne********@hotmail.com) writes:

I would like to have a composite PK on 3 columns, one of them is null
CREATE TABLE TableA (
ColA int NOT NULL ,
ColB int NOT NULL ,
ColC char (3) NULL ,
......
)
GO

All columns in a primary key must be NOT NULL. This is a basic concept
in relational databases.

However, you can define a UNIQUE constraint on the columns. SQL Server will
then allow exactly one row with NULL in ColC for any given value of ColA and
ColB.

If you have references to this table, you should probably throw in a
surrogate key, and use that as foreign key to other tables.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp