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

Product(name, price);
Bill(name, date, Products);

#1 楼

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。


我添加了serial列作为代理主键。在Postgres 10或更高版本中，请考虑考虑使用IDENTITY列。请参阅：

使用串行主键列安全地重命名表
自动递增表列
https://www.2ndquadrant.com/en/blog/postgresql-10-identity-专栏/

我强烈建议这样做，因为产品名称几乎不是唯一的（不是很好的“自然键”）。同样，强制使用唯一性并在外键中引用该列通常比使用存储为integer或bigint的字符串便宜（使用4字节text（甚至8字节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而没有(product_id, bill_id)的查询，则可能只想在product_id或bill_id上添加另一个索引。请参阅：

PostgreSQL复合主键
复合索引是否也适合对第一个字段进行查询？
在PostgreSQL中使用索引



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