Dependent column constraint

I would like to setup a  dependent column constraint something like so:

check (col1 in('Y') then col2 is not null);  So basically if col1 == 'Y' col2 cannot be null.

Thanks!
ODOTServerAsked:
Who is Participating?
 
sdstuberConnect With a Mentor Commented:
oops, missed some conditions

CHECK (CASE WHEN col1 = 'Y' AND col2 IS NOT NULL THEN 1 WHEN col1 != 'Y' OR col1 IS NULL THEN 1 ELSE 0 END = 1)
0
 
sdstuberCommented:
CHECK (case when col1 = 'Y' and col2 is not null then 1 end = 1)
0
 
sdstuberConnect With a Mentor Commented:
if col1 is already restricted to be not null then you can remove the "OR col1 is NULL"
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
ODOTServerAuthor Commented:
Okay that allowed null on col2 when col1 ='N' which is good.
It did not enforce col2 not null when col1 = 'Y which is bad.
0
 
sdstuberCommented:
which "that"  ?
0
 
sdstuberCommented:
Here's my test case,  the first 4 inserts succeed, the 5th fails
If that's not correct, please extend with additional sample data and expected results

CREATE TABLE testtable
(
    col1   VARCHAR2(1),
    col2   VARCHAR2(1)
);

ALTER TABLE sds.testtable ADD
CONSTRAINT ck
 CHECK (CASE WHEN col1 = 'Y' AND col2 IS NOT NULL THEN 1 WHEN col1 != 'Y' OR col1 IS NULL THEN 1 ELSE 0 END = 1)
 ENABLE
 VALIDATE;

INSERT INTO testtable(col1, col2)
     VALUES (NULL, NULL);

INSERT INTO testtable(col1, col2)
     VALUES ('N', NULL);

INSERT INTO testtable(col1, col2)
     VALUES ('N', 'x');

INSERT INTO testtable(col1, col2)
     VALUES ('Y', 'z');

INSERT INTO testtable(col1, col2)
     VALUES ('Y', NULL);

0
 
ODOTServerAuthor Commented:
Here is what it looks like (CASE WHEN col1 = 'Y' AND col2 IS NOT NULL THEN 1 WHEN col2!= 'Y' THEN 1 ELSE 0 END = 1)

col1 not null default 'N' and has a check col1 in('Y','N').

Thanks !
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.