1. 首页
  2. 问题
  3. 多对多关系中的外键约束
一尘不染

多对多关系中的外键约束

sql

语境

我们正在为介绍创建博客。到数据库课程项目。

在我们的博客，我们希望能够设置Labels上Posts。在Labels不能独立存在，他们只能这样做，如果他们都涉及到一个Posts。这样，Labels任何人都不会使用的不Posts应该保留在数据库中。

一个Label可以属于一个以上的人，一个可以包含一个Post以上Post的人Label。

我们同时使用SQLite3（本地/测试）和PostgreSQL（部署）。

执行

这是我们用来创建这两个表以及关系表的SQL（SQLite3风格）：

帖子

CREATE TABLE IF NOT EXISTS Posts(
   id INTEGER PRIMARY KEY AUTOINCREMENT,
   authorId INTEGER,
   title VARCHAR(255),
   content TEXT,
   imageURL VARCHAR(255),
   date DATETIME,
   FOREIGN KEY (authorId) REFERENCES Authors(id) ON DELETE SET NULL
)

标签

CREATE TABLE IF NOT EXISTS Labels(
   id INTEGER PRIMARY KEY AUTOINCREMENT,
   name VARCHAR(255) UNIQUE,
   -- This is not working:
   FOREIGN KEY (id) REFERENCES LabelPosts(labelId) ON DELETE CASCADE 
)

LabelPosts （Post[1 .. ]-之间的关系Label）

CREATE TABLE IF NOT EXISTS LabelPosts(
    postId INTEGER,
    labelId INTEGER,
    PRIMARY KEY (postId, labelId),
    FOREIGN KEY (postId) REFERENCES Posts(id) ON DELETE CASCADE
)

问题

  • Labels当我从LabelPosts表中删除对SQLite3的所有引用时，不会从数据库中删除它们。我认为是出于Postgres给出的原因，尽管SQLite毫无警告地接受了该表。

  • PostgreSQL抱怨在labelId中不是唯一的LabelPosts，这是正确的并且也是必需的，因为它是多对多的：

pq：S：“错误” R：“ transformFkeyCheckAttrs” L：“ 6511” C：“ 42830” F：“ tablecmds.c”
M：“给定表键” labelposts \“的键没有唯一匹配的约束

所以我知道我做错了约束。但是我不知道该怎么做。


阅读 185

收藏
2021-03-17

共1个答案

一尘不染

我们同时使用SQLite3（本地/测试）和PostgreSQL（部署）。

乞求麻烦。您将不断遇到轻微的不兼容问题。甚至要等到很晚才损坏才注意到它们。 不要这样
也可以在本地使用PostgreSQL。大多数操作系统都可以免费使用它。对于参与“数据库课程项目”的人来说，这是一个令人惊讶的愚蠢。有关的：

其他建议：

  • 正如@Priidu在评论中提到的那样，您的外键约束是向后的。这不值得争论，他们 只是错了 。

  • 在PostgreSQL中，请使用serial或IDENTITY列（Postgres 10+），而不要使用SQLite AUTOINCREMENT。看：

  • 不要使用大小写混合的标识符。

  • 请勿使用非描述性的列名，例如id。曾经。这是半机智的中间件和ORM引入的反模式。当您加入几个表时，您最终会得到名称的多列id。这是非常痛苦的。

  • 命名方式有很多，但大多数人都同意，最好使用单数形式的表名。它更短，至少是直观/合逻辑的。label，不是labels。

一切放在一起，看起来可能像这样：

CREATE TABLE IF NOT EXISTS post (
   post_id   serial PRIMARY KEY
 , author_id integer
 , title     text
 , content   text
 , image_url text
 , date      timestamp
);

CREATE TABLE IF NOT EXISTS label (
   label_id  serial PRIMARY KEY
 , name      text UNIQUE
);

CREATE TABLE IF NOT EXISTS label_post(
    post_id  integer REFERENCES post(post_id) ON UPDATE CASCADE ON DELETE CASCADE
  , label_id integer REFERENCES label(label_id) ON UPDATE CASCADE ON DELETE CASCADE
  , PRIMARY KEY (post_id, label_id)
);

扳机

要删除未使用的标签，请实现一个 触发器
。我提供了另一个版本，因为我对@Priidu提供的版本不满意：

CREATE OR REPLACE FUNCTION f_trg_kill_orphaned_label() 
  RETURNS trigger
  LANGUAGE plpgsql AS
$func$
BEGIN
   DELETE FROM label l
   WHERE  l.label_id = OLD.label_id
   AND    NOT EXISTS (
      SELECT 1 FROM label_post lp
      WHERE  lp.label_id = OLD.label_id
      );
END
$func$;

  • 必须在 触发器 之前 创建触发器 函数 。

  • 一个简单的DELETE命令就可以完成这项工作。无需第二次查询-尤其是count(*)。EXISTS比较便宜

  • 语言名称周围的单引号是可以容忍的，但实际上它是一个标识符，因此请忽略废话： LANGUAGE plpgsql

    CREATE TRIGGER label_post_delaft_kill_orphaned_label
    AFTER DELETE ON label_post
    FOR EACH ROW EXECUTE PROCEDURE f_trg_kill_orphaned_label();

有没有CREATE OR REPLACE TRIGGER在PostgreSQL的，但。只是CREATE TRIGGER。

2021-03-17