当前位置：theitroad>postgresql 多对多关系中的外键约束

postgresql 多对多关系中的外键约束

声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow 原文地址: http://stackoverflow.com/questions/15582023/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me): StackOverFlow

提示：将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间：2020-10-21 00:50:47 　来源:igfitidea点击:

Foreign key contraints in many-to-many relationships

sqlsqlitepostgresqldatabase-designmany-to-many

提问by AntoineG

Context

语境

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

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

In our blog, we want to be able to set Labelson Posts. The Labelscan't exist by themselves, they only do so if they are related to a Posts. This way, Labelsthat are not used by any Postsshouldn't stay in the database.

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

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

一个以上Label可以属于一个Post，多个一个Post可以使用一个Label。

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

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

Implementation

执行

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

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

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)

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
)

Problem

问题

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

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

  • 使用 SQLite3，Labels当我从LabelPosts表中删除对它的所有引用时，不会从数据库中删除。我认为由于 Postgres 给出的原因，尽管 SQLite 在没有警告的情况下接受了该表。

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

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\""

pq: S:"ERROR" R:"transformFkeyCheckAttrs" L:"6511" C:"42830" F:"tablecmds.c"
M:"没有唯一约束匹配给定键的引用表\"labelposts\""

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

所以我明白我做错了我的约束。但是我不知道如何正确地做到这一点。

回答by Erwin Brandstetter

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

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

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. Related:

这是在自讨苦吃。您将继续遇到轻微的不兼容性。或者甚至直到很久以后，当损坏造成时才注意到它们。不要这样做。也可以在本地使用 PostgreSQL。大多数操作系统都可以免费使用它。对于参与“数据库课程项目”的人来说，这是一个令人惊讶的愚蠢行为。有关的：

Other advice:

其他建议：

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

  • In PostgreSQL use a serialor IDENTITYcolumn (Postgres 10+) instead of SQLite AUTOINCREMENT. See:

  • 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.

  • 正如@Priidu 在评论中提到的，您的外键约束是倒退的。这不值得争论，他们完全错了。

  • 在 PostgreSQL 中使用 a serialorIDENTITY列（Postgres 10+）而不是 SQLite AUTOINCREMENT。看：

  • 使用timestamp（或timestamptz）代替datetime。

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

  • 不要使用非描述性的列名称，如id. 曾经。这是由 half-wit 中间件和 ORM 引入的反模式。当您加入几个表时，您最终会得到多个名称列id。这是积极的伤害。

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

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:

要删除未使用的标签，请实施触发器。我提供了另一个版本，因为我对 @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$;

  • The trigger functionmust be created beforethe trigger.

  • A simple DELETEcommand can do the job. No second query needed - in particular no count(*). EXISTSis cheaper.

  • Single-quotes around the language name are tolerated, but it's an identifier really, so just omit the nonsense: LANGUAGE plpgsql

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

  • 一个简单的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();

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

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

回答by Priidu Neemre

One way to achieve the behaviour you seek (delete unused labels from the database) would be to use triggers.

实现您所寻求的行为（从数据库中删除未使用的标签）的一种方法是使用触发器。

You could try writing something like:

你可以尝试写一些类似的东西：

CREATE OR REPLACE TRIGGER tr_LabelPosts_chk_no_more_associated_posts 
AFTER DELETE ON LabelPosts 
FOR EACH ROW 
EXECUTE PROCEDURE f_LabelPosts_chk_no_more_associated_posts();


CREATE OR REPLACE FUNCTION f_LabelPosts_chk_no_more_associated_posts() 
RETURNS TRIGGER AS $$
DECLARE
    var_associated_post_count INTEGER;
BEGIN
    SELECT Count(*) AS associated_post_count INTO var_associated_post_count FROM LabelPosts WHERE labelId = OLD.labelId;
    IF(var_associated_post_count = 0) THEN
        DELETE FROM Labels WHERE labelId = OLD.labelId;
    END IF;
END
$$ LANGUAGE 'plpgsql';

Basically, what happens here is:

基本上，这里发生的事情是：

  1. A row is deleted from table Posts.
  2. The deletion is cascaded to all associated rows in LabelPosts(thanks to your foreign key constraint).
  3. After the deletion of every single row in LabelPoststhe trigger is activated, which in turn calls the PostgreSQL function.
  4. The function checks whether there are any other posts connected with the labelIdin question. If so, then it finishes without any further modification. However, if there aren't any other rows in the relationship table, then the label is not used elsewhere and can thus be deleted.
  5. The function executes a delete DML on the Labelstable, effectively removing the (now) unused label.
  1. 从表中删除一行Posts。
  2. 删除级联到所有关联的行LabelPosts（由于您的外键约束）。
  3. 在LabelPosts触发器中的每一行的删除被激活后，它依次调用 PostgreSQL 函数。
  4. 该函数检查是否有任何其他帖子与相关labelId问题相关。如果是这样，则无需任何进一步修改即可完成。但是，如果关系表中没有任何其他行，则标签不会在其他地方使用，因此可以删除。
  5. 该函数对Labels表执行删除 DML ，有效地删除（现在）未使用的标签。

Obviously the naming isn't the best and there must be a ton of syntax errors in there, so see hereand herefor more information. There may be better ways to taking this thing down, however at the moment I can't think of a fast method that would not destroy the nice generic-looking table structure.

显然命名不是最好的，那里肯定有大量的语法错误，所以请参阅此处和此处了解更多信息。可能有更好的方法来解决这个问题，但是目前我想不出一种不会破坏漂亮的通用表结构的快速方法。

Although bare in mind - it is not generally a good practice to overburden your database with triggers. It makes every associated query/statement run a tat slower & also makes administration considerably more difficult. (Sometimes you need to disable triggers to perform certain DML operations, etc. depending on the nature of your triggers).

尽管牢记 - 使用触发器使数据库负担过重通常不是一个好习惯。它使每个关联的查询/语句运行得更慢，并且也使管理变得更加困难。（有时您需要禁用触发器以执行某些 DML 操作等，具体取决于触发器的性质）。

相关推荐

postgresql 如何使用preparedstatement获取最后插入行的id？

postgresql 如何使用preparedstatement获取最后插入行的id？

PostgreSQL Simple JOIN 很慢

PostgreSQL Simple JOIN 很慢

postgresql 功能获取正确的年份周数

postgresql 功能获取正确的年份周数

postgresql postgres 中可以创建多少个模式

postgresql postgres 中可以创建多少个模式

相关推荐
最近更新
标签