There are 2 tables. The first is user users who can refer to an unlimited number of entries in the site . It turns out the attitude of many to many when each user belongs to many sites, and each site to many users. How to arrange such links in the form of a table? They say you need to create the 3rd table, but it is not clear how. I would be very grateful for a small but illustrative example. Thank.

 CREATE TABLE user ( id SERIAL PRIMARY KEY, name TEXT UNIQUE NOT NULL ); CREATE TABLE site ( id SERIAL PRIMARY KEY, src TEXT UNIQUE NOT NULL );

    1 answer 1

      CREATE TABLE user_to_site ( id SERIAL PRIMARY KEY, user_id INTEGER NOT NULL, site_id INTEGER NOT NULL, CONSTRAINT "FK_user_id" FOREIGN KEY ("user_id") REFERENCES "user" ("id"), CONSTRAINT "FK_site_id" FOREIGN KEY ("site_id") REFERENCES "site" ("id"), );

    And add a unique index:

     CREATE UNIQUE INDEX "UI_user_to_site_site_id_user_id" ON "user_to_site" USING btree ("site_id", "user_id");
    • 3
      The presence of a synthetic key in the link table seems redundant - in any case, I do not see him as a sane application. The specified unique index may well be primary (or inverse - depending on the layout by type of query), and supplementary - inverse, or single in the second field. - Akina
    • + Taking into account commentary Akin-s - Sergey
    • + to comment by @Akina; primary key there may be needed unless to store some data about each connection. But in this case it is easier to consider the connection as a separate entity, and this is already somewhat thicker than bare many-to-many. - D-side
    • Could you explain a little to me CREATE UNIQUE INDEX ... this is what and why is it impossible without it? - Pavel
    • @Pavel can be without an index, but then you risk tying the same site to the same user more than once. - Ordman