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

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

我做了一些调整：

n:m 关系通常由一个单独的表实现 - 在这种情况下为 bill_product。

我添加了串行列作为代理主键。在 Postgres 10 或更高版本中，请考虑使用 IDENTITY 列。请参阅：使用串行主键列安全地重命名表自动递增表列 https://www.2ndquadrant.com/en/blog/postgresql-10-identity-columns/ 我强烈建议这样做，因为产品的名称几乎不是唯一的（不是一个好的“自然键”）。此外，使用 4 字节整数（甚至 8 字节 bigint）在外键中强制唯一性和引用列通常比使用存储为文本或 varchar 的字符串便宜。

使用串行主键列安全地重命名表

自动递增表列

https://www.2ndquadrant.com/en/blog/postgresql-10-identity-columns/

不要使用日期等基本数据类型的名称作为标识符。虽然这是可能的，但这是一种糟糕的风格，会导致令人困惑的错误和错误消息。使用合法的、小写的、不带引号的标识符。如果可以，切勿使用保留字，并避免使用双引号混合大小写标识符。

“名字”不是一个好名字。我将表产品的列重命名为产品（或产品名称或类似名称）。这是一个更好的命名约定。否则，当您在查询中加入几个表时（在关系数据库中会做很多事情），您最终会得到多个名为“name”的列，并且必须使用列别名来整理混乱。那没有帮助。另一个广泛使用的反模式将只是“id”作为列名。我不确定法案的名称是什么。在这种情况下， bill_id 可能就足够了。

price 是 numeric 数据类型，用于精确存储输入的小数（任意精度类型而不是浮点类型）。如果您专门处理整数，请制作该整数。例如，您可以将价格保存为美分。

金额（您问题中的“产品”）进入链接表 bill_product 并且也是数字类型。同样，如果您专门处理整数，则为整数。

您看到 bill_product 中的外键了吗？我创建了两者以级联更改：ON UPDATE CASCADE。如果 product_id 或 bill_id 应该更改，则更改将级联到 bill_product 中的所有相关条目，并且不会中断。这些只是参考，没有任何意义。我还对 bill_id 使用了 ON DELETE CASCADE：如果账单被删除，它的详细信息也会随之消失。产品并非如此：您不想删除账单中使用的产品。如果你尝试这个，Postgres 会抛出一个错误。您将向产品添加另一列以标记过时的行（“软删除”）。

此基本示例中的所有列最终都不是 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) 上添加另一个索引。请参阅：PostgreSQL 复合主键 复合索引是否也适用于第一个字段的查询？ PostgreSQL中索引的工作

PostgreSQL 复合主键

复合索引是否也适用于第一个字段的查询？

PostgreSQL中索引的工作

阅读手册中关于索引的章节。

---