Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How check constaint works? Expand / Collapse
Author
Message
Posted Friday, September 27, 2013 6:13 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, April 2, 2014 12:28 AM
Points: 12, Visits: 52
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
Post #1499340
Posted Friday, September 27, 2013 6:58 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, July 14, 2014 4:48 AM
Points: 2,834, Visits: 3,950
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
Post #1499359
Posted Saturday, September 28, 2013 2:33 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Yesterday @ 12:05 AM
Points: 718, Visits: 542
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.

Post #1499657
Posted Saturday, September 28, 2013 2:58 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, July 14, 2014 4:48 AM
Points: 2,834, Visits: 3,950
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
Post #1499661
Posted Sunday, September 29, 2013 11:28 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, April 2, 2014 12:28 AM
Points: 12, Visits: 52
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.

Post #1499836
Posted Monday, February 3, 2014 2:32 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 11:03 AM
Points: 2,901, Visits: 2,925
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
Post #1537519
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse