How to Implement Many-to-Many Relationships in PostgreSQL?

Implementing many-to-many relationships in PostgreSQL

Implementing many-to-many relationships in PostgreSQL requires creating a separate bridge table. This table, called the "join table", is used to join the main tables involved in the relationship.

To do this, create the following three tables:

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

This structure effectively connects the bill_product and product tables through the bill join table, thereby establishing a many-to-many relationship.

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