Re: Adding a UNIQUE constraint

From: Tom Moreau (tom_at_dont.spam.me.cips.ca)
Date: 06/14/04 

Date: Mon, 14 Jun 2004 10:40:46 -0400

You are allowed only one null in a unique constraint. You can use an
indexed view to do the same:

create view MyView
with schemabinding
as
select
    SSN
from
    dbo.MyTable
go

create unique clustered index C_MyView on MyView (SSN)
go 

-- 
Tom
---------------------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Tom T." <Tom T.@discussions.microsoft.com> wrote in message
news:6F12D49D-E07E-4D7F-90CF-D7807587C506@microsoft.com...
Hi all!
This is making me nuts.
Using SQL 2000, I am trying to add a UNIQUE constraint to an existing table
(already populated with data).  I have "scrubbed" the data so that the only
repeated values are NULL.  According to all the documentation I can find,
UNIQUE constraints are allowed where the only duplicate value is NULL.
However, every time I try to do this:
ALTER TABLE myTable
ADD CONSTRAINT u_SSN UNIQUE (SSN)
I get the following response:
Server: Msg 1505, Level 16, State 1, Line 1
CREATE UNIQUE INDEX terminated because a duplicate key was found for index
ID 24. Most significant primary key is '<NULL>'.
Server: Msg 1750, Level 16, State 1, Line 1
Could not create constraint. See previous errors.
The statement has been terminated.
Am I missing something?  Is there a way to add a UNIQUE constraint to an
already existing table/column?
Thanks!
Tom

Relevant Pages

  • Re: Adding a UNIQUE constraint
    ... Columnist, SQL Server Professional ... I think you forgot 'WHERE SSN IS NOT NULL' in the view definition? ... > You are allowed only one null in a unique constraint. ... > SQL Server MVP ...
    (microsoft.public.sqlserver.programming)
  • Re: Adding a UNIQUE constraint
    ... I think you forgot 'WHERE SSN IS NOT NULL' in the view definition? ... > You are allowed only one null in a unique constraint. ... > Columnist, SQL Server Professional ... > CREATE UNIQUE INDEX terminated because a duplicate key was found for index ...
    (microsoft.public.sqlserver.programming)
  • Re: Multi-Field Primary Key
    ... I.E. the worst arrangement is to have the autonumber alone be the ... Such a key could implemented using a UNIQUE constraint i.e. ... you are a fan of the 'CASCADE to null' feature in Jet 4.0 ). ... SQL Server will disregard all values in the referencing columns if one ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Multi-Field Primary Key
    ... Such a key could implemented using a UNIQUE constraint i.e. ... So why would you want to use an autonumber plus another field? ... you are a fan of the 'CASCADE to null' feature in Jet 4.0 ). ... SQL Server will disregard all values in the referencing columns if one ...
    (microsoft.public.access.tablesdbdesign)
  • Re: simple query locking too much data
    ... Do you have a primary key constraint, unique constraint or unique index on ... the username column? ... Check the execution plan to ensure SQL Server is ...
    (microsoft.public.sqlserver.programming)