Postgres: Are Nested Foreign Keys allowed?

Question

I mean things like:

FK1 -> 1FK2 -> 2PK

Please, note that 1FK2 is a PK to FK1, but the 1FK2 is a FK to 2PK. So the key in the nested structure is a FK and a PK.

An example:

--- Like an interface
CREATE TABLE q_content (
user_id SERIAL PRIMARY KEY, ---);

---- tables that refer to it
CREATE TABLE questions (
user_id SERIAL 
    references q_content(user_id) ---);

CREATE TABLE removed_questions (
user_id SERIAL
    references questions(user_id) ---);

Answer 1

Yes.

It's pretty common when you try to use natural or mostly-natural keys -- tables for subobjects often have primary keys that "contain" the primary keys of their parents. To extend your example, you might have:

CREATE TABLE questions
(
    user_id INTEGER REFERENCES q_content(user_id),
    topic TEXT,

    PRIMARY KEY (used_id, topic)
);

-- CREATE TABLE removed_questions (that has the same primary key as questions, which is also a foreign key to questions's primary).

I've changed the user_id to an INTEGER here, because you don't generally want to generate it out of thing air when it's supposed to correspond to the value in the q_content table.

Answer 2

Yes, this is perfectly legal, and in fact could be rather common.

Answer 3

Yes, a column can be a primary key of its table as well as a foreign key to a parent table.

However, in this case you don't need to use SERIAL as the data type for questions.user_id and removed_questions.user_id. The SERIAL data type implicitly creates a new sequence object, and you don't need that since these primary keys must contain only values that already exist in the table they reference.

Also this is tangential to your question, but I wouldn't define a removed_questions table at all. This should be an attribute column in the questions table.