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

How to implement a many-to-many relationship in PostgreSQL?

提问by Radu Gheorghiu

I believe the title is self-explanatory. How do you create the table structure in PostgreSQL to make a many-to-many relationship.

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

My example:

我的例子：

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

回答by Erwin Brandstetter

The SQL DDL (data definition language) statements could look like this:

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

I made a few adjustments:

我做了一些调整：

• The n:m relationshipis normally implemented by a separate table - bill_productin this case.

• I added serialcolumns as surrogate primary keys. I highly recommend that, because the name of a product is hardly unique. Also, enforcing uniqueness and referencing the column in foreign keys is much cheaper with a 4-byte integerthan with a string stored as textor varchar.
  In Postgres 10 or later consider an IDENTITYcolumninstead. Details:

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

• Don't use names of basic data types like dateas identifiers. While this is possible, it is bad style and leads to confusing errors and error messages. Use legal, lower case, unquoted identifiers. Never use reserved wordsand avoid double-quoted mixed case identifiers if you can.

• nameis not a good name. I renamed the namecolumn of the table productto be product. That is a better naming convention. Otherwise, when you join a couple of tables in a query - which you do a lotin a relational database - you end up with multiple columns named nameand have to use column aliases to sort out the mess. That's not helpful. Another widespread anti-pattern would be just idas column name.
  I am not sure what the name of a billwould be. Maybe bill_idcan be the namein this case.

• priceis of data typenumericto store fractional numbers precisely as entered(arbitrary precision type instead of floating point type). If you deal with whole numbers exclusively, make that integer. For example, you could save prices as Cents.

• The amount("Products"in your question) goes into the linking table bill_productand is of type numericas well. Again, integerif you deal with whole numbers exclusively.

• You see the foreign keysin bill_product? I created both to cascade changes (ON UPDATE CASCADE): If a product_idor bill_idshould change, the change is cascaded to all depending entries in bill_productand nothing breaks.
  I also used ON DELETE CASCADEfor bill_id: If you delete a bill, the details are deleted with it.
  Not so for products: You don't want to delete a product that's used in a bill. Postgres will throw an error if you attempt this. You would add another column to productto mark obsolete rows instead.

• All columns in this basic example end up to be NOT NULL, so NULLvalues are not allowed. (Yes, allcolumns - columns used in a primary key are defined UNIQUE NOT NULLautomatically.) That's because NULLvalues wouldn't make sense in any of the columns. It makes a beginner's life easier. But you won't get away so easily, you need to understand NULLhandlinganyway. Additional columns might allow NULLvalues, functions and joins can introduce NULLvalues in queries etc.

• Read the chapter on CREATE TABLEin the manual.

• Primary keys are implemented with a unique indexon the key columns, that makes queries with conditions on the PK column(s) fast. However, the sequence of key columns is relevant in multicolumn keys. Since the PK on bill_productis on (bill_id, product_id)in my example, you may want to add another index on just product_idor (product_id, bill_id)if you have queries looking for given a product_idand no bill_id. Details:

  • PostgreSQL composite primary key
  • Is a composite index also good for queries on the first field?
  • Working of indexes in PostgreSQL

• Read the chapter on indexes in the manual.

• 在N：M关系通常由一个单独的表来实现-bill_product在这种情况下。

• 我添加了serial列作为代理主键。我强烈建议这样做，因为产品的名称几乎不是唯一的。此外，integer与存储为textor的字符串相比，使用4 字节强制唯一性并引用外键中的列要便宜得多varchar。
  在 Postgres 10 或更高版本中，请考虑使用IDENTITY列。细节：

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

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

• name不是个好名字。我name将表的列重命名product为product. 这是一个更好的命名约定。否则，当你加入一对情侣在一个查询表的-你做了很多在关系型数据库-你最终命名多个列name，并必须使用列别名理清混乱。那没有帮助。另一个广泛使用的反模式就是id列名。
  我不确定 a 的名称是什么bill。在这种情况下，也许bill_id可以是名称。

• price是用于精确存储输入的小数的数据类型（任意精度类型而不是浮点类型）。如果您只处理整数，请使用. 例如，您可以将价格保存为 Cents。numericinteger

• 在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)，您可能想要添加另一个索引 on justproduct_id或者(product_id, bill_id)如果您有查询寻找给定的 aproduct_id和 no bill_id。细节：

  • PostgreSQL 复合主键
  • 复合索引是否也适用于第一个字段的查询？
  • 在 PostgreSQL 中使用索引

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