Constraint help - 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 2000 Forums

SQL Server Development (2000)

Constraint help

Author

Topic

sqlDan33
Starting Member

5 Posts

Posted - 2007-02-14 : 13:11:09

I have this field that I want to have unique values, but accept NULLs. There is a primary key column on this table already.

For instance:

PK ColA
-------
1, NULL
2, NULL
3, ABCD
4, EFGH
5, ABCD <- Fails

I'd need to have a constraints on this field no? If so, any idea of the type of expression needed to accomplish this?

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-02-14 : 14:24:43

I can't think of a way to do this with a table constraint. You can only have a single row with a NULL if you put a unique constraint on a column.

You can do it with a unique index on a View where the view does not include the rows containing the NULLs.


create table dbo.MyTable (
ID int not null identity(1,1) primary key clustered,
ColA varchar (20) null
)
go
Create view dbo.MyView
with SCHEMABINDING
as
Select ID , ColA from dbo.MyTable
where ColA is not null
go
create unique clustered index MyIndex on dbo.MyView ( ColA )
go
-- OK
insert into dbo.MyTable (ColA) select 'a'
go
-- Will fail on dupe key
insert into dbo.MyTable (ColA) select 'a'
go
-- OK
insert into dbo.MyTable (ColA) select null
go
--OK
insert into dbo.MyTable (ColA) select null
go
select * from dbo.MyTable
go
drop view dbo.MyView
go
drop table dbo.MyTable

Results:



ID          ColA                 
----------- -------------------- 
1           a
3           NULL
4           NULL

(3 row(s) affected)

There are other issues with indexed views that I won't get into now, so make sure you really need this.

You might also be able to do it with a trigger.

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources