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

Question

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

我的例子：

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

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

我强烈建议您这样做，因为产品名称几乎不是唯一的（不是很好的“自然键”）。此外，强制使用唯一性并在外键中引用该列通常比使用存储为或的字符串便宜（4字节integer（甚至8字节bigint））。text``varchar

• 不要使用基本数据类型的名称date作为 标识符 。尽管这是可能的，但这是不好的样式，并导致令人困惑的错误和错误消息。使用合法的，小写的，未加引号的标识符。切勿使用保留字，并尽可能避免使用双引号混合大小写标识符。

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

• price是 数据类型， numeric用于 精确 存储 所输入的 分数（任意精度类型，而不是浮点类型）。如果您只处理整数，则使integer。例如，您可以将 价格 另存 为美分 。

• 在amount（"Products"你的问题）进入连结表bill_product，是类型的numeric为好。同样，integer如果您只处理整数。

• 您看到 外键 了bill_product吗？我创建了这两者，以级联更改：ON UPDATE CASCADE。如果aproduct_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列的条件。但是，键列的顺序与多列键有关。由于在我的示例中PK onbill_product是打开的(bill_id, product_id)，因此您可能想在just上添加另一个索引，product_id或者(product_id, bill_id)如果您有查询在寻找给定product_id和no的查询bill_id。