conditional check constraint? - 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 2008 Forums

Transact-SQL (2008)

conditional check constraint?

Author

Topic

havab
Starting Member

3 Posts

Posted - 2009-08-05 : 20:06:47

Hi,
I'm pretty new to constraints, and I'm wondering if something is possible.

Basically I have a table that holds some basic information about an Order. It has a User_Id and Status column (among others, of course). I'm storing both completed orders and in process orders in this table.

I'd like to have a constraint where a particular User_Id can only have one order in the table with a Status of 1 (which corresponds to an order that is in process).

Is this possible? If so, what is the SQL to apply it to an existing table?

Thanks in advance for any help!

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-08-05 : 21:26:07

This is a bit of a hack, but if you have a primary key/unique constraint on an identity column, you can add a computed column with a CASE expression and then put a unique constraint on it, like so:

create table #a (i int not null identity(1,1), uid int not null, status tinyint not null,
consCol as case when status=1 then cast(uid as varchar) + '_' + cast(status as varchar) else
cast(i as varchar) end,
constraint PK_#a primary key(i),
constraint unq_consCol unique(consCol))

insert #a(uid, status) select 1,1
insert #a(uid, status) select 2,1
insert #a(uid, status) select 3,1
insert #a(uid, status) select 1,2
insert #a(uid, status) select 1,2
insert #a(uid, status) select 1,2
insert #a(uid, status) select 1,2
select * from #a
insert #a(uid, status) select 1,1  --this will fail
select * from #a

SQL Server doesn't support CHECK constraints that perform SELECTs. You could create a user-defined function that does a SELECT on the table and return a value if it finds a dupe, but you will incur a LOT of disk activity if you do multi-row INSERT or UPDATE operations. You could also do something similar in a trigger, but again you'll incur excess I/O.

The downside of using the constraint on the computed column is disk space used to maintain the unique index, but otherwise this is the method I would use. You just need to modify it to use your column names.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-08-07 : 14:03:25

You could also use a trigger to enforce those kinds of rules.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources