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


Check Constraint


Check Constraint

Author
Message
mithun.gite
mithun.gite
Old Hand
Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)

Group: General Forum Members
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
Vladan
Vladan
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1812 Visits: 751
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).



mithun.gite
mithun.gite
Old Hand
Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)

Group: General Forum Members
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
noeld
noeld
SSCertifiable
SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)

Group: General Forum Members
Points: 6328 Visits: 2048
You can make qty NOT NULL and you will have consistent behavior.


* Noel
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