Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How check constaint works?


How check constaint works?

Author
Message
mote.ajit2
mote.ajit2
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 53
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
Bhuvnesh
Bhuvnesh
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2926 Visits: 4076
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
T.Ashish
Right there with Babe
Right there with Babe (767 reputation)Right there with Babe (767 reputation)Right there with Babe (767 reputation)Right there with Babe (767 reputation)Right there with Babe (767 reputation)Right there with Babe (767 reputation)Right there with Babe (767 reputation)Right there with Babe (767 reputation)

Group: General Forum Members
Points: 767 Visits: 582
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
Bhuvnesh
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2926 Visits: 4076
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
mote.ajit2
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 53
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
Igor Micev
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4168 Visits: 4849
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,
‌SQL Server developer at Seavus
www.seavus.com
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search