Re: Adding a UNIQUE constraint

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

Date: Mon, 14 Jun 2004 12:40:00 -0400

Did you not see my follow-up? 

-- 
Tom
---------------------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Adam Machanic" <amachanic@hotmail._removetoemail_.com> wrote in message
news:OYeqGBiUEHA.3420@TK2MSFTNGP12.phx.gbl...
Tom,
I think you forgot 'WHERE SSN IS NOT NULL' in the view definition?
"Tom Moreau" <tom@dont.spam.me.cips.ca> wrote in message
news:el25c2hUEHA.4064@TK2MSFTNGP11.phx.gbl...
> 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
    ... 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: SQL 2005 - Searching Encrypted SSN
    ... SQL Server MVP ... I would like to encrypt the SSN column. ...
    (microsoft.public.sqlserver.security)
  • Re: Indexes and primary keys, from Delaney
    ... Kalen Delaney ... SQL Server MVP ... >> It is meaningless to say that you want a Unique Constraint (or PK ... >> allows SQL Server to guarantee the uniqueness. ...
    (microsoft.public.sqlserver.programming)
  • Re: Adding a UNIQUE constraint
    ... You are allowed only one null in a unique constraint. ... create view MyView ... create unique clustered index C_MyView on MyView (SSN) ... Columnist, SQL Server Professional ...
    (microsoft.public.sqlserver.programming)
  • Re: SQL 2005 - Searching Encrypted SSN
    ... SQL Server MVP ... I would like to encrypt the SSN column. ...
    (microsoft.public.sqlserver.security)