Check constraint with multiple columns

Question

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'));

onedaywhen · Accepted Answer · 2013-11-06 22:29:19Z

2

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')));

edited Nov 6, 2013 at 22:29

onedaywhen

57.4k12 gold badges103 silver badges142 bronze badges

answered Nov 5, 2013 at 22:04

Przemyslaw Kruglej

8,1312 gold badges30 silver badges41 bronze badges

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

3 Comments

user1873736 Over a year ago

Ok, then I just make an almost identical constraint for males, correct?

Przemyslaw Kruglej Over a year ago

@user1873736 You have to do it in one check, I've edited my answer.

onedaywhen Over a year ago

"you have to do it in one check" -- why do you say this? See my answer.

onedaywhen · Accepted Answer · 2013-11-06 22:28:18Z

2

"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' ) );

answered Nov 6, 2013 at 22:28

onedaywhen

57.4k12 gold badges103 silver badges142 bronze badges

1 Comment

Przemyslaw Kruglej Over a year ago

Read the author's comment - he asked if he is supposed to add additional trigger for dep_gender = 'M'. If he did that, he would end up with two checks that would not allow any record to be inserted into the table. That's why I said that he has to do it in one check, not two how he was going to. +1 for those checks.

Collectives™ on Stack Overflow

Check constraint with multiple columns

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related