postgresql 列引用另一个表，但引用可能有多个值？

mwngjboj 于 2023-04-05 发布在 PostgreSQL

关注(0)|答案(2)|浏览(156)

我尝试使用具有多个不同ID的资产来设置数据库设计，例如：

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

因此，此表描述了同时具有rl_id和unique_id的资产。在我的数据库中，多个资产可能具有相同的rl_id。unique_id可能与rl_id相同，也可能不同。
然而，只要两个项目具有相同的rl_id，它们就共享许多相同的数据。例如，尽管电影被配音成不同的语言，但两个配音都有相同的演员。下面是一些其他的表：

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),
);

但是，由于我没有对资产的rl_id的唯一约束，所以我得到了关于there is no unique constraint matching given keys for referenced table "assets"的错误。
我想知道最好的解决方案是什么。这个数据结构是我们在其他地方一直使用的，我们只是将其形式化为SQL，所以我不想改变它。我想知道是否有一种方法可以创建一个生成的表或视图，其中包含所有唯一的rl_id？我可以想到两个解决方案（创建一个触发器，每当有新资产时，我们就将它的rl_id插入到asset_rl_ids表中？），但大多数人都感到笨拙--不知道对于这种情况的最佳实践是什么。

postgresql

来源：https://stackoverflow.com/questions/75869795/column-references-another-table-but-reference-may-have-more-than-one-value

2条答案

按热度按时间

pjngdqdw1#

我知道你说过你不能改变数据结构，但传统的方法是在people和people_assets_map之间放一个表。这个表将包含rl_id。每个人都与一个rl_id相关，每个rl_id都可以与people_assets_map中的多个记录相关。你还可以向这个表添加任何特定于rl_id的细节。

赞(0）回复(0）举报 2023-04-05

shyt4zoc2#

以下是我最终得到的结果：

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();

解决了我的问题，因为现在我可以在需要特定ID时引用rids表，但我不必手动将内容放入其中，当我向assets表中插入内容时，它会自动发生。

赞(0）回复(0）举报 2023-04-05

我来回答

postgresql 列引用另一个表，但引用可能有多个值？

2条答案

相关问题

热门标签

最新问答