如何在PostgreSQL中实现多对多的关系？(How to implement a many

如何在PostgreSQL中实现多对多的关系？(How to implement a many-to-many relationship in PostgreSQL?)

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

我的例子：

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

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

My example:

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

最满意答案

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列作为代理主键 。 我强烈建议，因为产品的名称不是唯一的。 此外，在外键中实施唯一性和引用列对于使用4字节integer比使用text或varchar存储的字符串要便宜得多。 在Postgres 10或更高版本中，请考虑使用IDENTITY列 。 细节：

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

不要使用date等基本数据类型的名称作为标识符 。 虽然这是可能的，但它是不好的风格，并导致错误和错误消息混淆。 使用合法，小写，未加引号的标识符 。 如果可以的话 ，切勿使用保留字并避免使用双引号混合大小写标识符。

name不是一个好名字。 我将表格product的name列更名为product 。 这是一个更好的命名约定 。 否则，当你在一个查询中加入一对表格时 - 你在关系数据库中做了很多工作 - 最终你会得到多个名为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所有相关条目，并且没有任何中断。 我还在bill_id使用了ON DELETE CASCADE ：如果您删除了一个帐单，其详细信息将被删除。 产品并非如此：您不想删除账单中使用的产品。 如果你尝试这个，Postgres会抛出一个错误。 您可以将另一列添加到product来代替标记废弃的行。

这个基本示例中的所有列最终都是NOT 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中处理索引

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

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

I made a few adjustments:

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

I added serial columns 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 integer than with a string stored as text or varchar. In Postgres 10 or later consider an IDENTITY column instead. Details:

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

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.

name is not a good name. I renamed the name column of the table product to be product. 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. Maybe bill_id can be the name in this case.

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.

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.

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. I also used ON DELETE CASCADE for 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 product to mark obsolete rows instead.

All columns in this basic example end up to be NOT NULL, so NULL values are not allowed. (Yes, all columns - columns used in a primary key 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.

Read the chapter on CREATE TABLE in the manual.

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 given a product_id and 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.