UNIQUE CONSTRAINTS & UNIQUE INDEXES/PRIMARY KEYS

[Martin Speight]

The on-line books says the following about PRIMARY KEYS and UNIQUE
CONSTRAINTS

UNIQUE constraints enforce the uniqueness of the values in a set of columns.
No two rows in the table are allowed to have the same non NULL values for
the columns in a UNIQUE constraint.
Primary keys also enforce uniqueness, but primary keys do not allow NULL
values.
A UNIQUE constraint is preferred over a unique index.

But I tried to apply a UNIQUE CONSRAINT having removed the duplicate data,
with the following :-

ALTER TABLE TABLE1 WITH NOCHECK
ADD CONSTRAINT CONS1 UNIQUE (COL1)
ON [PRIMARY]

But it will not allow it while you have NULL values just as if I were
applying a UNIQUE INDEX even though I told it to create a constraint ?

Not only that if I remove the rows with NULL values and apply the UNIQUE
CONSTRAINT successfully,
it will not allow more than one row with a value of NULL to be inserted ?

Please can any explain?

Martin Speight.

[Mux]

UNIQUE constraints will only accept one NULL if it is not Primary Key.

[BP Margolin]

Martin,

Please ALWAYS post which version of SQL Server you are using.

Assuming you are using SQL Server 2000, then you might try this workaround
to the fact that SQL Server does not supporting multiple NULLs in a UNIQUE
constraint:

http://www.sqlmag.com/Articles/Index.cfm?ArticleID=21293&pg=3

-------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.

"Martin Speight" <mar...@aspeight.freeserve.co.uk> wrote in message
news:a2s9ob$643$1...@news8.svr.pol.co.uk...