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

Check Constraint Expand / Collapse
Author
Message
Posted Tuesday, March 24, 2009 12:54 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, May 11, 2010 2:15 AM
Points: 327, Visits: 364
Dear Friends,

I want one clarification about the Check Constraint....

please look into below code

create table vv1 (id int identity(1,10),qty numeric(18,2))

create view view_vv1
as
select * from vv1 WHERE (qty>10) WITH CHECK OPTION


Now when i m trying to insert that below data in view it is giving error.
INSERT INTO VIEW_VV1 VALUES (NULL)
--GIVES ERROR SAYING THAT NOT ALLOWED AS PER THE CHECK CONSTRAINT

Now i am altering my table adding the constraint to the table
ALTER TABLE VV1 ADD CONSTRAINT C1 CHECK (QTY>10)
--ASSGINING SAME CONSTRAINT IN TABEL NOW

Now i m trying to insert the same data as i tried for the view here its allowing..
INSERT INTO VV1 VALUES (NULL)
--ITS ALLOWING THE INSERTS

So please some one explain me why it like this?
Thanks in
Advance........


Regards,

Mithun Gite



Post #682103
Posted Tuesday, March 24, 2009 1:59 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, May 5, 2011 1:38 AM
Points: 1,636, Visits: 604
BOL says:
"When a row is modified through a view, the WITH CHECK OPTION makes sure the data remains visible through the view after the modification is committed."
and
"CHECK constraints reject values that evaluate to FALSE. Because null values evaluate to UNKNOWN, their presence in expressions may override a constraint."

That explains it - although in both cases there is the same word - CHECK - the action performed is different in each case.
WITH CHECK OPTION works the same as if you write a query : SELECT * FROM mytable WHERE col1 > 10 selects rows where (col1 > 10) evaluates to TRUE, meaning it will not return rows where col1 is NULL, therefore you will not be allowed to enter NULL value into the view. You are allowed to enter rows where CHECK evaluates to TRUE.
CHECK constraint prevents entering rows where constraint evaluates to FALSE.
Any expression can evaluate to TRUE, FALSE or UNKNOWN. Result of this is that "allow if TRUE" and "not allow if FALSE" is not the same. If the expression results in NULL, it is neither TRUE nor FALSE, and so a check working with "allow if TRUE" fails, while check working with "not allow if FALSE" succeeds (allows data to be entered).



Post #682117
Posted Tuesday, March 24, 2009 3:13 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, May 11, 2010 2:15 AM
Points: 327, Visits: 364
hellosir,


Thanks for ur valuable time and comment......

I m more clear now...... thanks a lot for the clarification......

I checked by using select * from tab1 where qty>0... and the result was the same as u said...

so i m considering it very useful answer from u...

Thanks & regards,

Mithun Gite
Post #682151
Posted Tuesday, March 24, 2009 9:03 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 5:47 AM
Points: 6,259, Visits: 2,029
You can make qty NOT NULL and you will have consistent behavior.



* Noel
Post #682460
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse