How Can I Reference Multiple Primary Keys For A Vector Type Primary Key

Question

Hi, I have the following scenario: a table of projects and a table of persons, working on one or serveral projects. Also, I have a project id column (of type int), in the first table, which is a primary key there and I have a vector of project ids, as a column of type int, in my second table (persons), that references primary keys from the first table.

What is the correct syntax for referencing multiple primary keys, from a vector foreign key. This is the way I am trying to create the table, but I am not sure what to place at the indicated line:

CREATE TABLE Persons(
    Person_Id int,
    ...,
    ProjectsList int[],
    FOREIGN KEY (ProjectsList) REFERENCES Projects(Project_id) -- not sure what how to define the link here
);

I hope my explanations are not over-complicated. Thank you in advance for helping!

Answer 1

No database I know can do that (using real foreign keys, not some custom triggers). You might want to normalize the tables:

CREATE TABLE Persons (
  Person_ID int,
  ...
  PRIMARY KEY (Person_ID)
);

CREATE TABLE PersonProjects (
  Person_ID int,
  Project_ID int,
  PRIMARY KEY (Person_ID, Project_id),
  FOREIGN KEY (Person_ID) REFERENCES Persons(Person_ID),
  FOREIGN KEY (Project_ID) REFERENCES Projects(Project_ID)
);

Answer 2

I assume you're using PostgreSQL, since that's one of the rare databases that supports array data types.

There is no syntax for declaring a foreign key that applies to the elements of an array.

You could write a trigger or a CHECK constraint as mentioned here.

But the better solution is to add a table to implement the many-to-many relationship between Projects and Persons, as Lukáš Lalinský shows. This supports First Normal Form.