Check Constraint on boolean

Question

Post reply

Check Constraint on boolean

Scaramouche

SSC Eights!

Points: 837
More actions
October 28, 2011 at 6:18 am

#245996

Hello,
I have to implement an active/inactive field on a table as we cannot delete the records. So putting this Boolean field will allow us after to filter this customer in our application (only active , inactive or all if we want to reactivate it latter).
I can do it in my java code but I was wondering if there is a way to use the check constraint to avoid to update this value (put it to false as inactive ) if the customer is used in another table .
Can we use queries in CHECK constraint (I did not find any close example to my problem) ? (I need to check another table to check this)
thanks a lot for your help.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

calvo SSChampion Points: 12930 More actions · Answer 1

I'm not sure what you want here. So I'll just guess and look for clarification.

Yes, you can use a bit data type in SQL Server. This will allow 1/0 to be read as active/inactive in your case.

From what I can understand, you might be looking for a trigger instead of a check constraint.

A check constraint only verifies data before being inserted. For example, check to make sure only positive numbers are entered.

A table trigger fires when a specified DML statement is executed (insert, update, or delete) against the table. In this case, if in a secondary table a customer becomes active, you can modify the primary customer table and update the customer's status through use of the trigger.

Again, that's me taking a guess at trying to understand your issue.

Look into table triggers in BOL and get back to us with some clarification. Perhaps a table definition or two.

______________________________________________________________________________________________
Forum posting etiquette.[/url] Get your answers faster.

JonFox SSCrazy Points: 2157 More actions · Answer 2

It's technically possible, you can create a UDF that executes a query against a different table, and then call that UDF in your CHECK constraint. This MSDN page on CHECK constraints includes an example of a constraint that references a UDF: http://msdn.microsoft.com/en-us/library/ms188258.aspx. However, it does sound like you may be looking for a trigger, not a constraint. Can you provide more details on what the desired behavior is?

Scaramouche SSC Eights! Points: 837 More actions · Answer 3

thanks for the info.

Yes I was wondering if I can use Sql Code in a CHECK Constraint the same way as in a trigger .

Thanks !.

Scaramouche SSC Eights! Points: 837 More actions · Answer 4

Thanks Jon fox,

I saw this page but I wanted to avoid to create a UDF .

Sorry it was more by curiosity and for my knowledge I wanted to knew if I could use a query directly in a Check constraint

(in my case if a user want to inactivate a customer, put the active value to false , I wanted to check before that this customer was not used in others tables to to a "select count (customeroid )... .

thanks for the answers.

Sean Lange SSC Guru Points: 286590 More actions · Answer 5

You can't just have select statement in a check constraint. For the purposes of what you are trying to accomplish a check constraint is just not the right approach. You want a delete trigger. In your case you want to NOT delete the record in some cases and instead mark it as inactive.

Keep in mind that check constraints are not validated on delete. Here is the same link Jon posted above for reference.

http://msdn.microsoft.com/en-us/library/ms188258.aspx

And just for the record a bit is NOT a boolean. It has 3 possible values (0, 1, null). Something to keep in mind when writing your trigger if your table allows nulls for your Active bit column. 😉

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

calvo SSChampion Points: 12930 More actions · Answer 6

Sean Lange (10/28/2011)
And just for the record a bit is NOT a boolean. It has 3 possible values (0, 1, null). Something to keep in mind when writing your trigger if your table allows nulls for your Active bit column. 😉

RABBLE RABBLE RABBLE!!!

You had to go there, eh?

But yeah, I agree. That's a good reminder.

I've had long discussions on how you can have an IsActive BIT NULL field and what NULL means in that sense.