R
R
rodionov122019-12-09 11:36:54
PostgreSQL
rodionov12, 2019-12-09 11:36:54

How to make foreign key in child table before multiple parent ones?

I have tables users and teams like this:

CREATE TABLE users(
  "guid" uuid NOT NULL DEFAULT uuid_generate_v4(),
  "username" varchar(60) NOT NULL
);

CREATE TABLE teams (
  "guid" uuid NOT NULL DEFAULT uuid_generate_v4(),
  "name" varchar(60) NOT NULL
);

and child table participants
CREATE TABLE participants (
  "contest" uuid NOT NULL,
  "participant" uuid NOT NULL,
);

It would be desirable to make, that in the table participants the column participant was foreign key at the same time on both parent tables. Merging the teams and users tables is not an option, the other columns are different and the logic is very different.

Answer the question

In order to leave comments, you need to log in

2 answer(s)
M
Melkij, 2019-12-09
@rodionov12

In the sense that participants.contest checks that such a uuid is in users.guid or teams.guid?
No, that's not possible.
Make a trigger that will copy the FK logic.
Move common fields to a separate table, merge as 1:1 with teams or users
Move common fields to a separate table, use table inheritance (inherits).

T
TheRonCronix, 2019-12-09
@TheRonCronix

That's it teams and users are different entities and you can't mix them. Therefore, it is necessary to make two foreign keys in the participants table. Separately for users and separately for teams.

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question