CODEWITHSUNDEEP
sqldatabaseoracle-database
user1873736
user1873736

Reputation: 101

Check constraint with multiple columns

I have a table DEPENDENT

CREATE TABLE DEPENDANT (DEPENDANT_NM VARCHAR2(15) PRIMARY KEY,
  DEP_GENDER CHAR(1), DEP_BDAT DATE, RELATED_HOW VARCHAR2 (8), ESSNO CHAR(9));
ALTER TABLE DEPENDANT ADD CONSTRAINT FK_ESSNO FOREIGN KEY (ESSNO)
 REFERENCES HW_EMPLOYEE(SSNO);
ALTER TABLE DEPENDANT ADD CONSTRAINT CK_DEP_GENDER CHECK
(DEP_GENDER IN ('M','F'));

I need to add a constraint that makes sure that female dependents can only be realted as 'daughter' or 'spouse' and male dependents or only 'son' or 'spouse'

Do I use a check constraint like ...

ALTER TABLE DEPENDANT ADD CONSTRAINT CK_RELATEDF CHECK (DEP_GENDER = 'F' AND 
RELATED_HOW IN('Daughter' OR 'Spouse'));

Upvotes: 2

Views: 2198

Answers (2)

Przemyslaw Kruglej
Przemyslaw Kruglej

Reputation: 8123

You have to write it like this:

ALTER TABLE DEPENDANT 
    ADD CONSTRAINT CK_RELATEDF 
        CHECK (DEP_GENDER = 'F' AND RELATED_HOW IN ('Daughter', 'Spouse'));

Edit: you have to do it in one check:

ALTER TABLE DEPENDANT ADD CONSTRAINT CK_RELATEDF
  CHECK ((DEP_GENDER = 'F' AND RELATED_HOW IN ('Daughter', 'Spouse'))
    OR (DEP_GENDER = 'M' AND RELATED_HOW IN ('Son', 'Spouse')));

Upvotes: 2

onedaywhen
onedaywhen

Reputation: 57073

"you have to do it in one check"

I was intrigued by this assertion (no pun intended).

It seems to me that four constraints are not only possible but desirable (to provide increased granularity of failure messages) e.g. 

ALTER TABLE DEPENDANT ADD CONSTRAINT Gender_legal_values 
    CHECK ( DEP_GENDER IN ( 'M', 'F' ) );

ALTER TABLE DEPENDANT ADD CONSTRAINT Related_legal_values 
    CHECK ( RELATED_HOW IN ( 'Son', 'Daughter', 'Spouse' ) );

ALTER TABLE DEPENDANT ADD CONSTRAINT Female_legal_values
    CHECK ( DEP_GENDER = 'M' OR RELATED_HOW IN ( 'Daughter', 'Spouse' ) );

ALTER TABLE DEPENDANT ADD CONSTRAINT Male_legal_values
    CHECK ( DEP_GENDER = 'F' OR RELATED_HOW IN ( 'Son', 'Spouse' ) );

Upvotes: 2

Related Questions