如何在 PostgreSQL 中实现多对多关系？

我相信标题是不言而喻的。如何在 PostgreSQL 中创建表结构以建立多对多关系。

我的例子:

Product(name, price);
Bill(name, date, Products);
136415 次浏览
小开
最佳答案

SQL DDL (数据定义语言)语句可以如下所示:

CREATE TABLE product (
product_id serial PRIMARY KEY  -- implicit primary key constraint
, product    text NOT NULL
, price      numeric NOT NULL DEFAULT 0
);


CREATE TABLE bill (
bill_id  serial PRIMARY KEY
, bill     text NOT NULL
, billdate date NOT NULL DEFAULT CURRENT_DATE
);


CREATE TABLE bill_product (
bill_id    int REFERENCES bill (bill_id) ON UPDATE CASCADE ON DELETE CASCADE
, product_id int REFERENCES product (product_id) ON UPDATE CASCADE
, amount     numeric NOT NULL DEFAULT 1
, CONSTRAINT bill_product_pkey PRIMARY KEY (bill_id, product_id)  -- explicit pk
);

我做了一些调整:

  • 在这种情况下，男女关系通常由一个单独的表 bill_product实现。

  • 我加入了 serial列作为 代理主键代理主键代理主键。在 Postgres 10或更高版本中考虑使用 IDENTITY列。参见:

    我强烈推荐这样做，因为一个产品的名称几乎不是唯一的(不是一个好的“自然键”)。此外，使用4字节的 integer(甚至8字节的 bigint)在外键中强制执行惟一性并引用列通常比使用存储为 text或 varchar的字符串更便宜。

  • 不要将基本数据类型(如 date)的名称用作 识别资料。虽然这是可能的，但它是不好的样式，并导致混淆的错误和错误消息。使用 合法的、小写的、未引用的标识符。不要使用 保留意见，如果可以的话，避免使用双引号混合大小写标识符。

  • “名字”不是个好名字。我将表 product的列重命名为 product(或 product_name或类似的名称)。这是一个更好的 变数命名原则。否则，当你在一个查询中加入几个表时——你在一个关系数据库中加入 很多——你最终会得到多个名为“ name”的列，并且不得不使用列别名来解决这个问题。这没什么帮助。另一个广泛使用的反模式是“ id”作为列名。
    我不确定 bill的名字是什么。在这种情况下，bill_id可能就足够了。

  • price为 数据类型numeric，用于存储小数 和输入的一模一样(任意精度类型而不是浮点类型)。如果你只处理整数，那就把它改成 integer。例如，您可以保存 价格为美分。

  • amount(在您的问题中是 "Products")进入链接表 bill_product，它的类型也是 numeric。同样，如果只处理整数，则为 integer。

  • 你看到 bill_product里的 外国钥匙了吗？我创建了两个级联更改: ON UPDATE CASCADE。如果 product_id或 bill_id应该更改，则更改将级联到 bill_product中的所有依赖项，且没有任何中断。那些只是没有意义的参考文献。
    我也使用 ON DELETE CASCADE的 bill_id: 如果一个法案被删除，它的细节与它死亡。
    对于产品则不是这样: 您不希望删除在账单中使用的产品。如果您尝试此操作，Postgres 将抛出错误。您可以向 product添加另一列来标记过时的行(“软删除”)。

  • 这个基本示例中的所有列最终都是 NOT NULL，因此不允许使用 NULL值。(是的，所有列-主键列自动定义为 UNIQUE NOT NULL。)这是因为 NULL值在任何列中都没有意义。它使初学者的生活更容易。但是你不会那么容易脱身，无论如何你需要了解 NULL正在处理。额外的列可能允许 NULL值，函数和连接可以在查询中引入 NULL值等。

  • 阅读关于 手册中的 CREATE TABLE的章节。

  • 主键在键列上使用唯一的 索引实现，这使得在 PK 列上使用条件的查询速度更快。但是，键列的顺序与多列键相关。因为在我的示例中，bill_product上的 PK 位于 (bill_id, product_id)上，所以如果有查询要查找给定的 product_id而没有 bill_id，那么可能只需要在 product_id或 (product_id, bill_id)上添加另一个索引。参见:

  • 读读 手册索引一章。


提交答案