Unique Index error upsizing

  • Thread starter Thread starter Mark A. Sam
  • Start date Start date
M

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
 
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
 
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
 
Back
Top