How check constaint works?

Question

How check constaint works?

mote.ajit2

Old Hand

Points: 308
More actions
September 27, 2013 at 6:13 am

#283778

Hi All!
I have added check constraint on table. I am calling one function in that check constraint.
Actually I have to prevent two records of same type should not have same date range as a result of
update.
Added check constraint like...
ALTER TABLE TABLENAME WITH NOCHECK ADD CONSTRAINT [CHK_TABLENAME] CHECK ((dbo.PAEXTENDADD_DUPLICATECHK (ID, VALIDFROM, VALIDTO) = 1))
go
Just want to know how does it works?? I mean is it work like first update will be done then go for check constraint validation. if check constraint violated then it will rollback.
is it so??
Many thanks

Viewing 6 posts - 1 through 5 (of 5 total)

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

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 1

mote.ajit2 (9/27/2013)
Just want to know how does it works?? I mean is it work like first update will e done then go for check constraint validation. if check constraint violated then it will rollback.

i dont think first it will update/insert the value in column and if validation fails , it rolls back. Sql server engine should not be design to work like this.

i also googled for same things but couldnt find anything.

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

T.Ashish SSCarpal Tunnel Points: 4248 More actions · Answer 2

First it will validate check constraint on the row.

If you are inserting/updating row by row then in case of violation, it will raise exception on the row and will move to next row.

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 3

Plz send any link.. if there is any detailed information on this

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

mote.ajit2 Old Hand Points: 308 More actions · Answer 4

Check constraint does work in way what I have mentioned in a problem.

May be logic what I have written in the function which is getting called from constraint is little bit tricky. I have to fix it.

Igor Micev SSC-Dedicated Points: 33109 More actions · Answer 5

mote.ajit2 (9/27/2013)
Hi All!
I have added check constraint on table. I am calling one function in that check constraint.
Actually I have to prevent two records of same type should not have same date range as a result of
update.
Added check constraint like...
ALTER TABLE TABLENAME WITH NOCHECK ADD CONSTRAINT [CHK_TABLENAME] CHECK ((dbo.PAEXTENDADD_DUPLICATECHK (ID, VALIDFROM, VALIDTO) = 1))
go
Just want to know how does it works?? I mean is it work like first update will be done then go for check constraint validation. if check constraint violated then it will rollback.
is it so??
Many thanks

Why do you create with "WITH NOCHECK"?

Try with "WITH CHECK" or try with "WITH CHECK CHECK".

Enabling with double CHECKs means enabling the constraint and checking whether the data in the table satisfies the constraint defined with the check constraint, i.e you're making the constraint trusted. After entrusting, you'll also see differences in the execution plans of queries using the trusted constraints.

Regards,

Igor

Igor Micev,My blog: www.igormicev.com