January 23, 2005 at 10:29 am
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
January 23, 2005 at 3:11 pm
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
January 23, 2005 at 3:25 pm
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]
January 24, 2005 at 6:21 am
Thanks your answear was very helpful!!
January 24, 2005 at 6:51 am
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])
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply