How to define expression in Check Constrain ? | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

How to define expression in Check Constrain ?

My question is I have field called Batch_status and I want that field should have a values either null or 2 or 3 and it have only one 2 in all rows. field doesn’t have more than one 2 in the field. therefore I need to define that check constrain for batch_status. I hope you understand the problem well now. Please provide the solution accordingly. Example :
Batch_Status
2 ——————-> only one 2 value allow for this field.
3
3
3
3
null
3
null
null
3 HOW TO DEFINE CHECK CONTRAIN EXPRESSION FOR BATCH_STATUS ?
if exists(Select count(*) from BatchSummary where batch_status = 2 having count(*) =1 )
is not working . Thanks in advance.
You can’t control row dependencies by check constraints. You can use trigger instead.
I believe you can use a UDF in a CHECK constraint, but that will get messy. Use a trigger instead. —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs
Ich unterstuetze PASS Deutschland e.V. http://www.sqlpass.de)
One of the most important aspects of Trigger can be realised in this situation. As suggested use Trigger that denies 2 if it is already in a table Madhivanan Failing to plan is Planning to fail
Thanks appreciate your reply.
]]>