Check Constraint

• mithun.gite

  SSCrazy

  Points: 2333

  More actions

  March 24, 2009 at 12:54 am

  #384190

  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

  SSC-Insane

  Points: 22072

  More actions

  March 24, 2009 at 1:59 am

  #965001

  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

  SSCrazy

  Points: 2333

  More actions

  March 24, 2009 at 3:13 am

  #965029

  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

  SSC Guru

  Points: 96590

  More actions

  March 24, 2009 at 9:03 am

  #965295

  You can make qty NOT NULL and you will have consistent behavior.

  
  * Noel