如何在 PostgreSQL 中实现多对多关系? [英] How to implement a many-to-many relationship in PostgreSQL?

问题描述

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

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

我的例子:

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

推荐答案

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

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

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.

• The n:m relationship is normally implemented by a separate table - bill_product in this case.

我添加了 serial 列作为代理主键.在 Postgres 10 或更高版本中，请考虑 IDENTITY 列 代替.见:

I added serial columns as surrogate primary keys. In Postgres 10 or later consider an IDENTITY column instead. See:

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

我强烈建议这样做，因为产品的名称几乎不是唯一的(不是一个好的自然键").此外，使用 4 字节 integer(甚至 8 字节 bigint)来强制唯一性和引用外键中的列通常比使用存储为 integer 的字符串更便宜code>text 或 varchar.

I highly recommend that, because the name of a product is hardly unique (not a good "natural key"). Also, enforcing uniqueness and referencing the column in foreign keys is typically cheaper with a 4-byte integer (or even an 8-byte bigint) than with a string stored as text or varchar.

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

Don't use names of basic data types like date as 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 words and avoid double-quoted mixed case identifiers if you can.

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

"name" is not a good name. I renamed the column of the table product to be product (or product_name or similar). That is a better naming convention. Otherwise, when you join a couple of tables in a query - which you do a lot in a relational database - you end up with multiple columns named "name" and have to use column aliases to sort out the mess. That's not helpful. Another widespread anti-pattern would be just "id" as column name.
I am not sure what the name of a bill would be. bill_id will probably suffice in this case.

price 是数据类型 numeric 以精确地存储输入的小数(任意精度类型而不是浮点类型).如果您只处理整数，请将其设为 integer.例如，您可以将价格保存为美分.

price is of data type numeric to 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.

amount(您问题中的Products")进入链接表 bill_product 并且类型为 数字也是如此.同样，integer 如果你只处理整数.

The amount ("Products" in your question) goes into the linking table bill_product and is of type numeric as well. Again, integer if you deal with whole numbers exclusively.

您看到bill_product 中的外键?我创建了两个来级联更改:ON UPDATE CASCADE.如果 product_id 或 bill_id 应该更改，则更改将级联到 bill_product 中的所有相关条目，并且不会中断.那些只是参考，没有自己的意义.
我还为 bill_id 使用了 ON DELETE CASCADE:如果账单被删除，它的细节也会随之消失.
产品并非如此:您不想删除帐单中使用的产品.如果您尝试这样做，Postgres 将抛出错误.您可以向 product 添加另一列以标记过时的行(软删除").

You see the foreign keys in bill_product? I created both to cascade changes: ON UPDATE CASCADE. If a product_id or bill_id should change, the change is cascaded to all depending entries in bill_product and nothing breaks. Those are just references without significance of their own.
I also used ON DELETE CASCADE for bill_id: If a bill gets deleted, its details die 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 product to mark obsolete rows ("soft-delete") instead.

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

All columns in this basic example end up to be NOT NULL, so NULL values are not allowed. (Yes, all columns - primary key columns are defined UNIQUE NOT NULL automatically.) That's because NULL values 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 NULL handling anyway. Additional columns might allow NULL values, functions and joins can introduce NULL values in queries etc.

阅读关于CREATE TABLE的章节在手册中.

Read the chapter on CREATE TABLE in the manual.

主键是通过键列上的唯一索引实现的，这使得在 PK 列上具有条件的查询更快.但是，键列的顺序与多列键相关.由于 bill_product 上的 PK 在我的示例中位于 (bill_id, product_id) 上，因此您可能只想在 product_id 或 上添加另一个索引>(product_id, bill_id) 如果您有查询寻找给定的 product_id 而没有 bill_id.见:

Primary keys are implemented with a unique index on 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_product is on (bill_id, product_id) in my example, you may want to add another index on just product_id or (product_id, bill_id) if you have queries looking for a given product_id and no bill_id. See:

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

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

这篇关于如何在 PostgreSQL 中实现多对多关系?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！