Unique constraint
-
Hi all
I am trying to create a Unique constraint on a column in an existing table but I am struggling with the syntax as the BOL is terrible. My issue is, that the value in the table may be null, so, creating a unique index doesnt work (even with skip dup's on). Can someone post an example addition of a unique constraint on a table col?
I feel like a right idiot! 🙂
Frustrated Ck
Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA" -
This is the sort of thing I've used before:
ALTER TABLE [dbo].[TblAuth] WITH NOCHECK ADD
CONSTRAINT [IU_TblAuth] UNIQUE NONCLUSTERED (AuthId)
GO
By the way I cheated to produce this text - when I have a syntax issue like this, what I do it to create a sample table in enterprise manager then look at the sql syntax it uses.
Paul Ibison
Paul Ibison
Paul.Ibison@replicationanswers.com -
Thanks Paul, ill give it a burl when im back at work. I would have thought that the unique constraint was actually an unique index, and explained why the NULL's in the column where causing it to fail. Anyhow, ill try the constraint you've posted as ive had it with databases for one day
Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA" -
Just as I thought...
ALTER TABLE [dbo].[organisation] WITH NOCHECK ADD
CONSTRAINT [UC_ORG_EMPID] UNIQUE NONCLUSTERED (org_emp_id)
..gave me..
Server: Msg 1505, Level 16, State 1, Line 1
CREATE UNIQUE INDEX terminated because a duplicate key was found for index ID 24. Most significant primary key is '<NULL>'.
Server: Msg 1750, Level 16, State 1, Line 1
Could not create constraint. See previous errors.
The statement has been terminated.
Apart from a trigger, any other ideas on creating a UNIQUE constraint against a table column that can have nulls.
Cheers
Chris
Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA" -
Nope. You can only have one null and still call it unique. Maybe an indexed view as a hack? Or how about moving all your null records to a separate table, putting your constraint on the existing table, then using a view for normal stuff, do your inserts into the table so the constraint will catch dupes? Why so many nulls?
Andy
-
Or maybe you need a compound unique index if the nulls will have to exist.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
-
Andy's solution I think is the only real way without going to triggers or expanding the index (composite column unique constraint) that will be created to handle uniqueness. SQL Server 2K will try to use indexes on the base tables wherever possible, can't recall from SQL Server 7 and the BOL for it is on my work computer.
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply