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

问题描述

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

我的示例：

 产品（名称，价格）; 
 Bill（name，date，Products）; 
  

解决方案

DDL语句可能如下所示：

  CREATE TABLE产品（
 product_id serial PRIMARY KEY  - 隐式主键约束
，产品文本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 p>




• 我添加了 serial 列 surrogate primary keys 。我强烈推荐，因为产品的名称几乎不是唯一的。此外，使用4字节 integer 比使用存储为 text 的字符串强制外键中的列的引用要便宜得多$ c>或 varchar 。




• 不要使用基本数据类型名称 date 作为标识符。虽然这是可能的，但它是坏的风格，导致混乱的错误和错误消息。使用合法，小写，不带引号的标识符。切勿使用保留字，避免使用双引号混合大小写




• name 不是一个好名字。我将表 product 的名称列重命名为产品。这是一个更好的命名约定。否则，当您在查询中加入几个表（在关系数据库中这样做）时，最终会出现多个名为的列并且必须使用列别名来排序混乱。这没有帮助。另一个广泛的反模式是 id 作为列名。

我不知道 bill 将是。




• <$> c $ c> price 是数据类型 数字用于精确输入小数数字 >（任意精度类型，而不是浮点类型）。如果你只处理整数，使 integer 。例如，您可以将价格另存为Cents 。




• amount （Products在您的问题中）进入链接表 bill_product ，类型数字。




• 您会看到外键 bill_product 中的我创建了两个级联更改（ ON UPDATE CASCADE ）：如果 product_id 或 / code>应该更改，更改将级联到 bill_product 中的所有相关条目，并且不会有任何内容。

我也使用 ON DELETE CASCADE 为 bill_id ：如果删除帐单，则会删除其中的详细信息。

不是这样对于产品：您不想删除帐单中使用的产品。如果你试图这样，Postgres会抛出一个错误。




• 此基本范例中的所有资料栏都会显示在下列资料列中： product 最终为 NOT NULL ，因此不允许使用 NULL 值。 （是，所有列 - 主键中使用的列自动定义 UNIQUE NOT NULL 。）这是因为 NULL 值在任何列都没有意义。它使初学者的生活更容易。但你不会这么容易离开，你需要了解 NULL 处理。其他列可能允许 NULL 值，函数和联接可以引入 NULL 值等。
  

• 阅读有关 <$ c的章节$ c> CREATE TABLE 。




• 主键使用独特的 / strong>，使得PK列上的条件的查询更快。但是，键列的顺序与多列键相关。由于我的示例中 bill_product 上的PK位于（bill_id，product_id），因此您可能需要在只要 product_id 或（product_id，bill_id），如果你有查询给定一个 product_id 和 bill_id 。详情：

  
  
  
  
  
  • 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.

My example:

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

解决方案

The DDL 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.

• 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 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.

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