How to Implement Many-to-Many Relationships in PostgreSQL Using Junction Tables?

Use join table to implement many-to-many relationship in PostgreSQL

Understanding many-to-many relationships

In a many-to-many relationship, a single entity can be related to multiple other entities and vice versa. To represent this in a database, a separate table called a "join table" is usually used.

Create table structure in PostgreSQL

To create a many-to-many relationship in PostgreSQL, you first need to define two main tables and then create the join table.

CREATE TABLE product (
  product_id serial PRIMARY KEY  -- 隐式主键约束
, 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)  -- 显式主键
);

Key features of table structure

• Product: represents a single product with a name and price.
• Bill: represents a single bill with description and date.
• Bill_Product: Create a connection table for many-to-many relationships. It contains two foreign keys to the Product and Bill tables, and an amount column for each combination.

Notes

• Surrogate primary key: We have defined the serial column as the surrogate primary key for the Product and Bill tables. These are unique integer values ​​automatically generated by the system, which makes joining tables in queries more efficient.
• Naming Convention: We follow a consistent naming convention, using singular nouns for tables and lowercase letters for column names.
• Data Type: We have chosen appropriate data types such as price as number type and bill date as date type to ensure accuracy.
• Foreign Keys: Foreign keys have been defined as ON UPDATE CASCADE and ON DELETE CASCADE to automatically propagate changes or deletions.
• NOT NULL constraint: We have defined all columns as NOT NULL to enforce data integrity.
• Index: will automatically create an index on the primary key of the Bill_Product table, thus speeding up queries.

The above is the detailed content of How to Implement Many-to-Many Relationships in PostgreSQL Using Junction Tables?. For more information, please follow other related articles on the PHP Chinese website!