0

Is there any material difference between these two ways of defining foreign key constraints?

CREATE TABLE table1 (
    id INT PRIMARY KEY,
    other_id INT REFERENCES table2(id)
)

CREATE TABLE table1 (
    id INT,
    other_id INT,

    CONSTRAINT pk_table1 PRIMARY KEY (id),
    CONSTRAINT fk_table1_other_id FOREIGN KEY (other_id) REFERENCES table2(id)
)

Obviously one is more verbose. I've also read that the explicit constraint names might be useful within application code during exception handling.

Is one preferred over the other?

2
  • 3
    The second one is mandatory if the foreign key is on two columns. – a_horse_with_no_name Dec 24 '20 at 0:18
  • Main advantage with second approach is for maintaining your schema. – Lennart Dec 24 '20 at 9:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.