外交关系在多对多的关系中 [英] Foreign key contraints in many-to-many relationships

问题描述

上下文

我们正在构建一个介绍博客。到数据库课程项目。

在我们的博客中，我们希望能够在<$ c $上设置标签 c>帖子。 标签不能自己存在，他们只能与帖子相关联。这样，标签不会被任何帖子使用，不应该留在数据库中。

多个标签可以属于单个 Post ，而不止于一个 Post 可以使用标签。

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

实现

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

帖子

  CREATE TABLE IF NOT EXISTS帖子（
 id INTEGER PRIMARY KEY AUTOINCREMENT，
 authorId INTEGER，
 title VARCHAR（255），
 content TEXT， 
 imageURL VARCHAR（255），
日期DATETIME，
 FOREIGN KEY（authorId）参考作者（id）ON DELETE SET NULL 
）
  

标签

  CREATE TABLE IF NOT EXISTS Labels （
 id INTEGER PRIMARY KEY AUTOINCREMENT，
 name VARCHAR（255）UNIQUE，
  - 这不工作：
 FOREIGN KEY（id）参考LabelPosts（labelId）ON DELETE CASCADE 
）
  

LabelPosts （帖子 [1 .. *] - * 标签）

  CREATE TABLE IF NOT EXISTS LabelPosts（
 postId INTEGER，
 labelId INTEGER，
 PRIMARY KEY（postId，labelId），
 FOREIGN KEY（postId）参考帖子（id）ON DELETE CASCADE 
）
  

问题

• 使用SQLite3，标签不会从数据库中删除， code> LabelPosts 表。我认为Postgres给出的原因，尽管SQLite接受了表而没有警告。




• PostgreSQL抱怨说 labelId 在 LabelPosts 中不是唯一的，这是真实的，也是必需的，因为它是多对多的：










pq：S：ERRORR：transformFkeyCheckAttrsL：6511C：42830F：tablecmds.c br>
M：没有唯一的约束匹配引用表的给定键\labelposts\




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

解决方案



• 你的第一个错误：









我们正在使用SQLite3（本地/测试）和PostgreSQL ）。




这是乞求麻烦。你会继续遇到轻微的不兼容。或者甚至没有注意到，直到很久以后，当损坏完成。 不要这样做。也在本地使用PostgreSQL。大多数操作系统都可以免费使用它。对于参与数据库课程项目的人来说，这是令人惊讶的愚蠢的。







• 在PostgreSQL中使用 serial 列而不是SQLite AUTOINCREMENT 。
  
  使用 timestamp （或 timestamptz ），而不是 datetime 。




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




• 不要使用非描述性的列名称，如 id 。永远这是半机制中间件和ORM引入的反模式。当您加入几张表格时，最终会出现名称为 id 的多列。这是非常有害的。




• 有很多命名风格，但大多数人认为将单数术语作为表名更好。它更短，至少直观/逻辑。 标签，而不是标签。




• As @ Priidu在评论中提到了，你的外键约束倒退了。







一切都放在一起，它可能如下所示：








  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提供的一个：









 创建或替换功能f_trg_kill_orphaned_label（）
 RETURNS TRIGGER AS 
 $ func $ 
 BEGIN 
 DELETE FROM label 
 WHERE label_id = OLD.label_id 
 AND NOT EXISTS（
 SELECT 1 FROM label_post 
 WHERE label_id = OLD.label_id 
）; 
 END 
 $ func $ LANGUAGE plpgsql; 
  








• 触发功能必须先创建 em>触发器。




• 一个简单的 DELETE 命令可以完成这项工作。不需要第二个查询 - 特别是没有 count（*）。 EXISTS 更便宜。




• ppgsql 。这是一个标识符，而不是一个价值！










  CREATE TRIGGER label_post_delaft_kill_orphaned_label 
删除后label_post 
为每个ROW EXECUTE PROCEDURE f_trg_kill_orphaned_label（）; 
  

没有 CREATE OR REPLACE TRIGGER in PostgreSQL，但。 只需 CREATE TRIGGER 。




Context

We're building a blog for an intro. to databases course project.

In our blog, we want to be able to set Labels on Posts. The Labels can't exist by themselves, they only do so if they are related to a Posts. This way, Labels that are not used by any Posts shouldn't stay in the database.

More than one Label can belong to a single Post, and more than a single Post can use a Label.

We are using both SQLite3 (locally/testing) and PostgreSQL (deployment).

Implementation

Here is the SQL (SQLite3 flavor) that we use to create those two tables, along with the relationship table:

Posts

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
)

Labels

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 (relation between 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
)

Problem

• Using SQLite3, Labels are not deleted from the database when I remove all references to it from the LabelPosts table. I think for the reason given by Postgres, despite SQLite accepting the table without warning.

• PostgreSQL complains that labelId is not unique within LabelPosts, which is true and also required, since it's many-to-many:

pq: S:"ERROR" R:"transformFkeyCheckAttrs" L:"6511" C:"42830" F:"tablecmds.c"
M:"there is no unique constraint matching given keys for referenced table \"labelposts\""

So I understand that I'm doing my constraint wrong. However I don't know how to do it properly.

解决方案

• Your first big mistake:

We are using both SQLite3 (locally/testing) and PostgreSQL (deployment).

This is begging for trouble. You will keep running into minor incompatibilities. Or not even notice them until much later, when damage is done. Don't do it. Use PostgreSQL locally, too. It's freely available for most every OS. For someone involved in a "databases course project" this is a surprising folly.

• In PostgreSQL use a serial column instead of SQLite AUTOINCREMENT.
  Use timestamp (or timestamptz) instead of datetime.

• Don't use mixed case identifiers.

• Don't use non-descriptive column names like id. Ever. That's an anti-pattern introduced by half-wit middleware and ORMs. When you join a couple of tables you end up with multiple columns of the name id. That's actively hurtful.

• There are many naming styles, but most agree it's better to have singular terms as table names. It's shorter and at least as intuitive / logical. label, not labels.

• As @Priidu mentioned in the comments, your foreign key constraints are backwards. This is not up for debate, they are simply wrong.

Everything put together, it could look like this:

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

Trigger

• To delete unused labels, implement a trigger. I supply another version since I am not happy with the one provided by @Priidu:

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

• trigger function must be created before the trigger.

• A simple DELETE command can do the job. No second query needed - in particular no count(*). EXISTS is cheaper.

• No single-quotes around plpgsql. It's an identifier, not a value!

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

There is no CREATE OR REPLACE TRIGGER in PostgreSQL, yet. Just CREATE TRIGGER.

这篇关于外交关系在多对多的关系中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！