Unique Index error upsizing

[Mark A. Sam]

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

 

[Tom Moreau]

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

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
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

 

[Mark A. Sam]

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