Force Unique Values on Column - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Force Unique Values on Column

Author

Topic

ronnieoverby
Starting Member

36 Posts

Posted - 2008-09-09 : 11:30:46

I have a table with a column called UserID that is of type UniqueIdentifier.

I want to allow nulls, but if it has a value I want that value to be unique.

I created a unique constraint on this column and was able to set one record to NULL, but when I tried the 2nd time it wouldnt commit because it was not unique.

How can I do this?

Thanks

mcrowley
Aged Yak Warrior

771 Posts

Posted - 2008-09-09 : 11:55:23

Triggers have all sorts of pitfalls associated with them, but this should get you started....


create table test111
(col1 int null)
go
create trigger testtrig on test111 instead of insert, update
as
set nocount on
if exists (select * from inserted i join test111 on i.col1 = test111.col1)
  begin
	raiserror('Uh-uh-uh.  You didn''t say the magic word.', 1, 1)
	rollback
  end
else
  begin
	insert into test111 select * from inserted
  end

Always be sure you test inserts and updates of multiple rows, before attempting a trigger. I suspect this will nto give you good results for a multi-row update.

ronnieoverby
Starting Member

36 Posts

Posted - 2008-09-09 : 13:02:11

there is no way to design the table to do this?

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-09-09 : 14:01:38

You can create a view that only has the rows with non-null values, and put a unique index on that view.

create view MyView as select * from MyTable where UserID is not null

There are significant issues with doing this, so make sure you read all about indexed views in SQL Server 2005 Books Online before you chose this option.

CODO ERGO SUM

ronnieoverby
Starting Member

36 Posts

Posted - 2008-09-09 : 14:05:22

I think im just going to disallow nulls

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources