?
Solved

Dependent column constraint

Posted on 2011-03-22
7
Medium Priority
?
402 Views
Last Modified: 2012-05-11
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!
0
Comment
Question by:ODOTServer
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 2
7 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 35191256
CHECK (case when col1 = 'Y' and col2 is not null then 1 end = 1)
0
 
LVL 74

Accepted Solution

by:
sdstuber earned 1000 total points
ID: 35191286
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
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 1000 total points
ID: 35191296
if col1 is already restricted to be not null then you can remove the "OR col1 is NULL"
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:ODOTServer
ID: 35191379
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 35191393
which "that"  ?
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 35191438
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
 

Author Comment

by:ODOTServer
ID: 35191511
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

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

649 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question