0

Please tell me, is it possible somehow in Postgres to specify an array as a data type that contains foreign key elements I have next table:

CREATE TABLE User
(
    id SERIAL PRIMARY KEY,
    name VARCHAR(50)
);

CREATE TABLE Team
(
    id SERIAL PRIMARY KEY,
    idOwnerTeam INT,
    idTeamUsers INT[], -- PROBLEM
    FOREIGN KEY (idOwnerTeam) REFERENCES User(id),
    FOREIGN KEY (idTeamUsers ) REFERENCES User(id) -- PROBLEM
);

I will be grateful for any help

I know that this problem can be solved if you specify the command from the user. Then the solution will look like this:

CREATE TABLE User
(
    id SERIAL PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    idTeam INT
);

CREATE TABLE Team
(
    id SERIAL PRIMARY KEY,
    idOwnerTeam INT NOT NULL,
    FOREIGN KEY (idOwnerTeam) REFERENCES User(id),
);

ALTER TABLE User
ADD FOREIGN KEY (idTeam) REFERENCES Team(id);

But unfortunately this solution doesn't work for me.

Fa4stik
  • 9
  • 4
  • 1
    Google for many to many relation. See for example https://en.wikipedia.org/wiki/Many-to-many_(data_model) You should have another relation called `UserTeam` which would link users to teams. – fukanchik Mar 01 '23 at 15:43
  • 1
    Don't use arrays for that. use a proper many-to-many relationship. Additionally: the use of `serial` is [discouraged](https://wiki.postgresql.org/wiki/Don't_Do_This#Don.27t_use_serial) in favour of the standard compliant `identity` columns. –  Mar 01 '23 at 15:45
  • 1
    Do yourself a favor and fix your data model. Don’t use arrays for this, that will cause many issues and this is just the first – Frank Heikens Mar 01 '23 at 15:54
  • `idTeamUsers INT[], -- PROBLEM` -- Yes, that's not a normalized design, and in fact is not even 1NF. I would strongly recommend to improve the design at least to 3NF. As it is, it looks like a no-sql design. – The Impaler Mar 01 '23 at 16:22
  • "...is it possible somehow in Postgres to specify an array as a data type that contains foreign key elements..." -- No. You can store them, yes, but they won't act as foreign keys. – The Impaler Mar 01 '23 at 16:26
  • 1
    Thank you all very much for your help! Still, I will use an additional table to implement a many-to-many relationship – Fa4stik Mar 01 '23 at 16:29

0 Answers0