SQL: Is creating multiple foreign keys with one statement equivalent to creating them with one statement?

Question

For example let's have the following table definition:

CREATE TABLE table1 
(
    id INT UNIQUE,
    name VARCHAR(100) UNIQUE,
    description VARCHAR(100),

    PRIMARY KEY (id, name)
);

Now I would like to create another table which would have a foreign key to the above composite primary key. Would the following two statements be equivalent?

1)

CREATE TABLE table2 
(
     id INT PRIMARY KEY,
     table1_id INT,
     table1_name VARCHAR(100),

     FOREIGN KEY (table1_id) REFERENCES table1(id),
     FOREIGN KEY (table1_name) REFERENCES table1(name)
);

2)

CREATE TABLE table2 
(
     id INT PRIMARY KEY,
     table1_id INT,
     table1_name VARCHAR(100),

     FOREIGN KEY (table1_id, table1_name) REFERENCES table1(id, name),
);

I noticed that behind the scenes Postgre SQL creates two FK db objects in the case of 1) and one FK object in the case of 2). Would everything work the same anyway?

(Obviously--) This is a faq. Before considering posting please always google any error message & many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names; read many answers. If you post a question, use one phrasing as title. See [ask] & the voting arrow mouseover texts. PS This shows "does not show any research effort". — philipxy, Jul 06 '19 at 21:35

Gordon Linoff · Accepted Answer · 2019-07-06T13:36:09.057

Not at all. A foreign key reference should be to the primary key. In this case you have a composite primary key (although that is probably not needed, see below). Although foreign key references to unique keys are allowed (and some databases even allow foreign key references to any indexed columns), that is not a best practice.

When you use a composite primary key (your second example) you are guaranteeing that id/name are aligned in the first table. When you use separate references (your first example), you do not know that they are aligned, so the id could refer to one row in table1 and the name to another row. I doubt that is your intention.

In any case, repeating redundant data among tables is a bad practice. The better data model is:

CREATE TABLE table1 (
  id INT PRIMARY KEY,
  name VARCHAR(100) UNIQUE,
  description VARCHAR(100),
);

CREATE TABLE table2 (
  id INT PRIMARY KEY,
  table1_id INT,
  FOREIGN KEY (table1_id) REFERENCES table1(id)
);

Then, if you want the corresponding name, look up the name in the first table.

As a note, in Postgres, I would expect the INT to really be SERIAL so the database assigns a unique, increasing value when you insert new rows.

If you actually want two references to table1 then use two id references:

CREATE TABLE table2 (
  id INT PRIMARY KEY,
  table1_id INT,
  table1_id_2 INT,
  FOREIGN KEY (table1_id) REFERENCES table1(id),
  FOREIGN KEY (table1_id_2) REFERENCES table1(id)
);

I know that it might not be the best example of having id and name as being composite primary key, but let's suppose that's the case (there are situations where a composite primary key needs to be referenced as a foreign key). So what I understand is that in such cases, I should define the FOREGIN KEY statement compositely, like in example 2) I gave? — Mr. Nicky, Jul 06 '19 at 13:08
@Mr.Nicky . . . Just reference the primary key and look up other values when you need them. That is the best way to use databases. In most databases, you can only reference unique or primary keys, so if one key is unique and you have a composite primary key, your data model doesn't seem correct. — Gordon Linoff, Jul 06 '19 at 13:10
I didn't downvote but maybe this was downvoted because per the downvote arrow mouseover text it "is not useful" because it is (yet another answer by you) adding nothing but clutter to the site & promoting future clutter by answering an obviously easily found researchless duplicate non-useful question. Granted the site owners are self-contradictory re their goals around duplicates. — philipxy, Jul 06 '19 at 21:29

SQL: Is creating multiple foreign keys with one statement equivalent to creating them with one statement?

1 Answers1