PostgreSQL foreign key with inheritance

Question

I have a PostgreSQL database, 3 tables and my schema as follows.

CREATE TABLE table_a (
    id SERIAL PRIMARY KEY,
    name_a VARCHAR(255)
);

CREATE TABLE table_b (
    name_b VARCHAR(255) 
) INHERITS (table_a);

CREATE TABLE table_c (
    name_c VARCHAR(255)
) INHERITS (table_a);

insert into table_b (name_a, name_b) values('table A','table B1');
insert into table_b (name_a, name_b) values('table A','table B2');
insert into table_c (name_a, name_c) values('table A','table C1');
insert into table_c (name_a, name_c) values('table A','table C2');

select * from table_a;
select * from table_b;
select * from table_c;

Now i want to add an association between Table Band Table C like this:

I do not know if this is possible when we inherit the same table ?

I do not see how I can create this association ?

dani herrera · Accepted Answer · 2019-02-18 10:33:04Z

3

You need a unique identifier or primary key on table_b. Quoting docs:

All check constraints and not-null constraints on a parent table are automatically inherited by its children, unless explicitly specified otherwise with NO INHERIT clauses. Other types of constraints (unique, primary key, and foreign key constraints) are not inherited.

CREATE TABLE table_a (
    id SERIAL PRIMARY KEY,
    name_a VARCHAR(255)
);

CREATE TABLE table_b (
    name_b VARCHAR(255),
    primary key (id)     --> here you set the PK
) INHERITS (table_a);

CREATE TABLE table_c (
    name_c VARCHAR(255),
    id_b int references table_b(id) --> the fk to b through pk
) INHERITS (table_a);

Alternative way

In your second diagram, do you have a kind of identifier for table_b. It is right because you can reference table by a unique field. In this case, the DDL will be like this:

CREATE TABLE table_a (
    id SERIAL PRIMARY KEY,
    name_a VARCHAR(255)
);

CREATE TABLE table_b (
    name_b VARCHAR(255),
    id_b SERIAL UNIQUE     --> Here the unique id for b
    --, primary key (id)   -- optionally
) INHERITS (table_a);

CREATE TABLE table_c (
    name_c VARCHAR(255),
    id_b int references table_b(id_b)  --> the fk to b through unique
) INHERITS (table_a);

I prefer the first approach, but I have posted also this one just for academical purposes.

edited Feb 18, 2019 at 10:33

answered Feb 18, 2019 at 10:07

dani herrera

52k11 gold badges133 silver badges188 bronze badges

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

2 Comments

Mercer Over a year ago

my column id_b of my Table C is null

dani herrera Over a year ago

@Mercer, thanks about your comments. I improved my answer. Also, you can found an example with FK here: db-fiddle.com/f/kG1Vxo8jMWsNsVRvREfapr/0

Collectives™ on Stack Overflow

PostgreSQL foreign key with inheritance

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related