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

Column references another table but reference may have more than one value? – Postgresql

I am trying to set up a database design with assets which have a number of distinct IDs, for example:

CREATE TABLE IF NOT EXISTS assets (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  rl_id INT NOT NULL,
  unique_id VARCHAR(9) NOT NULL UNIQUE,
  UNIQUE(rl_id, unique_id)
);
INSERT INTO assets (rl_id, unique_id) VALUES (3894, 3894); -- a spanish language movie
INSERT INTO assets (rl_id, unique_id) VALUES (3894, '3894dubEN'); -- english dub of that movie
INSERT INTO assets (rl_id, unique_id) VALUES (3894, '3894dubFR'); -- french dub of that movie

So this table describes an asset which has both an rl_id and a unique_id. In my database, multiple assets may have the same rl_id. The unique_id may be the same as the rl_id or it may be different.

However, as long as two items have the same rl_id, they share many of the same data.
Although the movie is dubbed into different languages, for example, both the dubs have the same actors. Here are some other tables:

CREATE TABLE IF NOT EXISTS people (
  id SERIAL PRIMARY KEY,
  speaking_id VARCHAR(255) NOT NULL UNIQUE,
  name TEXT NOT NULL,
);

CREATE TABLE IF NOT EXISTS people_assets_map (
  id SERIAL PRIMARY KEY,
  person_id INT NOT NULL REFERENCES people(id),
  rl_id INT NOT NULL REFERENCES assets(rl_id),
);

However, because I don’t have a unique constraint on the asset’s rl_id, I get the error about there is no unique constraint matching given keys for referenced table "assets".

I am wondering what the best solution for this is. This data structure is something we have been using in other places, and we are just formalizing it into SQL, so I don’t want to change it. I wonder if there’s a way to create a generated table or a view that has all the unique rl_ids? I can think of a couple solutions (make a trigger where every time there’s a new asset, we upsert its rl_ids into an asset_rl_ids table?), but most of them feel clunky — wondering what the best practice is for a situation like this.

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Here is what I ended up with:

    CREATE TABLE IF NOT EXISTS rids (
  rid INT PRIMARY KEY UNIQUE NOT NULL
);

CREATE TABLE IF NOT EXISTS assets (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  rid INT REFERENCES rids ON DELETE CASCADE,
  season_rid INT REFERENCES rids ON DELETE SET NULL,
  series_rid INT REFERENCES rids ON DELETE SET NULL,
  uqid VARCHAR(9) NOT NULL UNIQUE
);

CREATE OR REPLACE FUNCTION insert_into_rlids() RETURNS TRIGGER AS
$$
BEGIN
  INSERT INTO rids (id) VALUES (NEW.rid)
    ON CONFLICT (id) DO NOTHING;
  RETURN NEW;
END;
$$ LANGUAGE 'plpgsql';

CREATE OR REPLACE TRIGGER rids_insert_id BEFORE INSERT ON assets
  FOR EACH ROW EXECUTE PROCEDURE insert_into_rlids();

    Solves my problem because now I can reference the rids table when I need that particular ID but I don't have to manually put things in there, it'll happen automatically when I insert something into my assets table.


  2. 0

    I know you said you can’t change the data structure but the traditional way of doing this would be to put a table between people and people_assets_map. This table would contain the rl_id. Each person would be related to that one rl_id and each rl_id could relate to multiple records in people_assets_map. You would also add any details that are rl_id-specific to this table.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search