PostgreSQL: Add constraint with mulitple conditions

Question

I'm having difficulties adding a constraint with two constraint conditions to an already existing table. I'm working with the two relations below, and I'm trying to add the constraint to the "books" table.

The relations are:

books((book_id), title, author_id, subject_id)
subjects((subject_id), subject, location)

Where the keys within the parantheses are primary keys, and the italics are foreign keys.

The first criteria/condition is that the subject_id is NOT NULL, and that the subject_id when inserting a new book-tuple into books, already has to exist as a primary key in the subjects relation.

ALTER TABLE books ADD CONSTRAINT hasSubject 
    CHECK(subject_id IS NOT NULL AND subject_id REFERENCES subjects(subject_id))

I keep getting the error message: "syntax error at or near 'REFERENCES'"

Any ideas? Thank you in advance!

Gordon Linoff · Accepted Answer · 2017-09-05 15:56:37Z

2

REFERENCES is a separate type of constraint. So, you need two constraints:

ALTER TABLE books ADD CONSTRAINT chk_books_subject_id
    CHECK (subject_id IS NOT NULL);

ALTER TABLE books ADD CONSTRAINT fk_books_subject_id
    FOREIGN KEY (subject_id) REFERENCES subjects(subject_id);

I put these in two ALTER TABLE statements, to emphasize that they are different. They can be combined in one statement.

EDIT:

In one statement:

ALTER TABLE books
    ADD CONSTRAINT chk_books_subject_id CHECK (subject_id IS NOT NULL),
    ADD CONSTRAINT fk_books_subject_id FOREIGN KEY (subject_id) REFERENCES subjects(subject_id);

edited Sep 5, 2017 at 15:56

answered Sep 5, 2017 at 15:22

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

3 Comments

Ninu Over a year ago

Thank you! Would you care to show how to combine these two constraints? I tried combining the two with an "AND" statement. So: "... IS NOT NULL) AND FOREIGN KEY(subject_id) ...", but recieved a syntax error message complaining "at or near "AND".

Ninu Over a year ago

Once again, thank you!! :) Would it be possible to join the two constraints and refer to them with one mutual name "hasSubjects"? In other words, is it possible to rewrite this single statement with only one "ADD CONSTRAINT" term, whilst keeping the same functionality?

Gordon Linoff Over a year ago

@Ninu . . . No, that is not possible. There are two constraints.

Collectives™ on Stack Overflow

PostgreSQL: Add constraint with mulitple conditions

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related