Unique Index error upsizing

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Mark A. Sam - 26 Jul 2005 23:05 GMT

Hello,

I am trying to upsize a table from Access to SQL Server and get this
message:

CREATE UNIQUE INDEX PHONE ON Contacts(PHONE)

Server Error 1505: CREATE UNIQUE INDEX terminated because a duplicate key
was found for index ID 5. Most significant primary key is '<NULL>'.
Server Error 3621: The statement has been terminated.

Phone is a unique key, but not a primary key. I tried removing the key,
upsizing then adding the key from an Access project, but it wouldn't let me.

Thanks for any help and God bless,

Mark A. Sam

Reply to this Message

Tom Moreau - 26 Jul 2005 23:08 GMT

You have multiple rows with Phone = NULL. If you still want to index this
and accommodate the duplicate NULLs, the add the following indexed view
instead:

create view dbo.PhoneView
with schemabinding
as
select
PHONE
from
dbo.Contacts
where
PHONE is not NULL
go

create unique clustered index idx_Phone on dbo.PhoneView (PHONE)
go

Signature

Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.

Reply to this Message

Mark A. Sam - 27 Jul 2005 14:24 GMT

Thanks Tom, you were right about the Nulls. I don't know what you mean by
adding the view below, but I put in values where they were Null and was able
to upsize the table.

God Bless,

Mark

> You have multiple rows with Phone = NULL. If you still want to index this
> and accommodate the duplicate NULLs, the add the following indexed view
[quoted text clipped - 31 lines]
>
> Mark A. Sam

Reply to this Message