trouble creating a unique constraint

Question

trouble creating a unique constraint

lior gal-201587

Valued Member

Points: 72
More actions
January 23, 2005 at 10:29 am

#85354

Hi
when i'm trying to create a unique constraint using the Enterprise manager on a numeric (int) field that have only NULL in every record i get this error message:
Unable to create index 'IX_Payments'.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]CREATE UNIQUE INDEX terminated because a duplicate key was found for index ID 41. Most significant primary key is '<NULL>'.
Any Ideas ?
THANKS

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246939 More actions · Answer 1

The multiple [null] occurences are treated as duplicates: the index is therefore not unique and cannot be created as such. As far as I know, there is no simple way of constraining a column to be 'UNIQUE OR NULL'. I have used a trigger in the past to achieve this. Depending on your application, you may be able to force uniqueness by defaulting the column to something like NEWID(), but then your app would have to distinguish between 'proper' entries and newid() entries - certainly not ideal.

Another alternative is to change your db schema - put the column in a new table, linked to your main table on its primary key, and containing rows only if the columns are not null - true uniqueness and your constraint will work fine.

Regards

Phil

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Frank Kalis SSC Guru Points: 111183 More actions · Answer 2

As you already have found out, this is handled differently in SQL Server and Access and Oracle (I believe). A UNIQUE constraint here allows one, and only one index key to be NULL. So, as Phil has said, you first need to fill this column with unique values and than you are able to create the constraint.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

lior gal-201587 Valued Member Points: 72 More actions · Answer 3

lior gal-201587

Valued Member

Points: 72

January 24, 2005 at 6:21 am

#538715

Thanks your answear was very helpful!!

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 4

There's another way to achieve the same result without modifying any table design. You can create an indexed view on that column and create a unique index on the view like this.

CREATE VIEW dbo.vwTest

WITH SCHEMABINDING

AS

Select PkObjSQL, Name, DefaultValue from dbo.ObjSQL where not DefaultValue is null

GO

CREATE UNIQUE CLUSTERED INDEX [Pk_vwMyTest] ON [dbo].[vwTest] ([DefaultValue])