How to implement a many-to-many relationship in PostgreSQL?

Question

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

Solution

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. In Postgres 10 or later consider an IDENTITY column instead. See:

  • Safely rename tables using serial primary key columns
  • Auto increment table column
  • https://www.2ndquadrant.com/en/blog/postgresql-10-identity-columns/

  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.

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

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

• 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 a given product_id and no bill_id. See:

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

Comments

[Marc B]

remove products from the bill table, create a new table called "bill_products" with two fields: one pointing at products, one pointing at bill. make those two fields this new table's primary key.

[Radu Gheorghiu]

So bill_products(bill, products); ? And both of them PK?

[Marc B]

yeah. they'd be individually an FK pointing at their respective tables, and together they'd be the PK for the new table.

[Radu Gheorghiu]

So, bill_product(product references product.name, bill references bill.name , (product, bill) primary key) ?

[Marc B]

They'd point to whateve the PK fields of the Product and Bill tables would be.

[mercury]

pivot table between the tables

[codyLine]

How can I create an index for the mapping table bill_product? Normally it should looks like: CREATE INDEX idx_bill_product_id ON booked_rates(bill_id, product_id). Is this right?

[Erwin Brandstetter]

@codyLine: This index is created automatically by the PK.

[Christian]

@ErwinBrandstetter: Shouldn't be created an index on bill_product for product_id column?

[Erwin Brandstetter]

@ChristianB.Almeida: That's useful in many cases, yes. I added a bit about indexing.

[Jakov]

Why is the amount column inside the composite table? Shouldn't it be in the bill table?

[Erwin Brandstetter]

@Jakov: There is only 1 row for each bill in table bill. We need the amount per added item in bill_product.

[Jakov]

@ErwinBrandstetter Oh - the amount of same items on a bill. I thought it was the total price of the bill. My bad.