SQL CHECK issue with adding a CONSTRAINT

Question

This is two-part question involving CHECK statements, In my first CHECK for some reason it works but actually allows null to be inserted into the field... any ideas how to remedy this? In my second CHECK, It plain and simple does not work. It's allowing anything to be inserted.

ALTER TABLE table_name ADD (CONSTRAINT CK_CON (name IN ('Jon','Bill','Ted','Ron','Jeff')));

ALTER TABLE table_name ADD (CONSTRAINT CK_NAME CHECK(
         (name='John') OR (name='Mary') OR 
          (name='Jane') OR (name= 'Bruce') OR (name= '')));

In a WHERE clause, the entire predicate has to be TRUE in order to match. In a CHECK constraint, the predicate needs to not be FALSE. Where this makes a difference is precisely in the areas of NULLs and the fact that SQL has a three-valued logic which sometimes produces UNKNOWN when NULL crops up. — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Oct 19, 2015 at 12:19

Moudiz · Accepted Answer · 2015-10-19 12:18:50Z

3

as for first question change column name to not null

as for second question

try do it such way

ALTER TABLE table_name ADD (CONSTRAINT CK_NAME CHECK(
         (name='John') OR (name='Mary') OR 
          (name='Jane') OR (name= 'Bruce') )); -- you have to remove this OR (name= '')

answered Oct 19, 2015 at 12:18

Moudiz

7,42723 gold badges87 silver badges166 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Josh123 Over a year ago

I'm getting the error - USER.CK_NAME - check constraint violated. No ideaaa why?

Moudiz Over a year ago

because you have value name inserted related to the checking you provide. you have to clean your data or modify you checking depending on what you want

Jota · Accepted Answer · 2015-10-19 12:13:33Z

0

ALTER TABLE table_name ADD (CONSTRAINT CK_CON (name IN ('Jon','Bill','Ted','Ron','Jeff')));

ALTER TABLE table_name ADD (CONSTRAINT CK_NAME CHECK( (name='John') OR (name='Mary') OR (name='Jane') OR (name= 'Bruce')));

Can prove this

answered Oct 19, 2015 at 12:13

Jota

11

2 Comments

Jota Over a year ago

I think so but prove this

Jota Over a year ago

When u create a table and put constraints i think so but i dont sure. If not u can prove make a function for restrinctions

Collectives™ on Stack Overflow

SQL CHECK issue with adding a CONSTRAINT

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related