Re: Adding a UNIQUE constraint

From: Adam Machanic (amachanic_at_hotmail._removetoemail_.com)
Date: 06/14/04 

Date: Mon, 14 Jun 2004 11:00:02 -0400

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
    ... 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)
  • adding UNIQUE Constraint to existing column
    ... duplicate key using WITH NOCHECK in SQL Server 2000. ... ADD CONSTRAINT unq_user_email_email UNIQUE ... Query Analyzer keeps giving me the error message that duplicate key was found: ... Can someone please tell me how i can add a UNIQUE CONSTRAINT without ...
    (microsoft.public.sqlserver.programming)
  • Re: adding UNIQUE Constraint to existing column
    ... > Can someone please tell me how i can add a UNIQUE CONSTRAINT without ... > removing the duplicate keys. ... Controls what happens when an attempt is made to insert a duplicate key ... SQL Server MVP ...
    (microsoft.public.sqlserver.programming)
  • Re: Adding a UNIQUE constraint
    ... Not one that has non-unique data in it:) A uniuqe index only allows a ... select SSN ... All other replies will be ignored:) "Tom T." ... Is there a way to add a UNIQUE constraint to an already existing table/column? ...
    (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)
Loading