PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Adding an array of foreign keys to another table in PostgeSQL

I recently started learning postgresql, and I had a little problem setting up relationships between tables.

I have 2 tables – users and projects. Each project has its creator (user), and each user has an array with his projects.

create TABLE users(
     id SERIAL PRIMARY KEY,
     name VARCHAR(200) UNIQUE,
     email VARCHAR(1000) UNIQUE,
     password VARCHAR,
     tags VARCHAR[],
     about VARCHAR(2000),
     socials VARCHAR[],
     projects VARCHAR[], //array of projects
     avatarurl VARCHAR,
)

create TABLE projects(
     id SERIAL PRIMARY KEY,
     title VARCHAR(200),
     text VARCHAR(3000),
     needs VARCHAR[],
     socials VARCHAR[],
     creator_id INTEGER FOREIGN KEY (creator_id) REFERENCES users(id),
)

I need that when creating a new project, its ID is automatically added to the array of user projects. How can this best be achieved?

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    As it turned out, storing foreign keys in an array is not the best idea; everything could have been done much simpler by simply leaving the foreign key to the user in the project that he created.

    create TABLE users(
     id SERIAL PRIMARY KEY,
     name VARCHAR(200) UNIQUE,
     email VARCHAR(1000) UNIQUE,
     password VARCHAR,
     tags VARCHAR[],
     about VARCHAR(2000),
     socials VARCHAR[],
     avatarurl VARCHAR,
)
create TABLE projects(
     id SERIAL PRIMARY KEY,
     title VARCHAR(200),
     text VARCHAR(3000),
     needs VARCHAR[],
     socials VARCHAR[],
     creator_id INTEGER FOREIGN KEY (creator_id) REFERENCES users(id),
)

    As for the speed of searching in the table, as far as I understand, PostgresSCL has already taken care of this.


  2. 0

    That is a classical many to many relationship which requires a third table since a user may be in 0 or many projects and a project may have 0 or many users

    CREATE TABLE user (
    id SERIAL PRIMARY KEY
    ,name VARCHAR(255) NOT NULL
    ,...
);

    CREATE TABLE project (
    id SERIAL PRIMARY KEY
    ,name VARCHAR(255) NOT NULL
    ,...
    ,creator INT
    ,FOREIGN KEY (creator)
        REFERENCES user (id)
        ON DELETE SET NULL
        ON UPDATE CASCADE
);

    CREATE TABLE user_and_project (
    user INT
    ,project INT
    ,PRIMARY KEY (user,project)
    ,FOREIGN KEY (user)
        REFERENCES user (id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
    ,FOREIGN KEY (project)
        REFERENCES project (id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search