Open order_items in Monster Market. Two columns have a key icon.

A key of several columns

One order has several items. One product is in several orders. Neither order_id nor product_id identifies a row alone. The pair does:

A composite keycomposite keyA primary key made of two or more columns: the combination is unique is a primary key of two or more columns. Write it after the columns:

CREATE TABLE order_items (
  order_id   integer NOT NULL REFERENCES orders (id),
  product_id integer NOT NULL REFERENCES products (id),
  quantity   integer NOT NULL,
  PRIMARY KEY (order_id, product_id)
);

PRIMARY KEY after each column fails: that is two primary keys.

ERROR: multiple primary keys for table "order_items" are not allowed

A duplicate pair fails with the error from Lesson 1:

ERROR: duplicate key value violates unique constraint "order_items_pkey"
DETAIL: Key (order_id, product_id)=(101, 7) already exists.

Add a constraint to a table that exists

ALTER TABLE … ADD CONSTRAINT adds any constraint from this chapter to a table that holds rows:

ALTER TABLE enrolments ADD CONSTRAINT enrolments_pkey PRIMARY KEY (student_id, course_id);
ALTER TABLE products ADD CONSTRAINT price_not_negative CHECK (price >= 0);
ALTER TABLE reviews ADD CONSTRAINT reviews_product_fkey FOREIGN KEY (product_id) REFERENCES products (id);

ALTER TABLE … DROP CONSTRAINT name removes one.

The rows that exist must obey the new constraint. If one row breaks it, the statement fails and nothing changes:

ERROR: could not create unique index "enrolments_pkey"
DETAIL: Key (student_id, course_id)=(S201, CS-340) is duplicated.

PostgreSQL enforces a key with an index, so the error names one (Chapter 13 explains indexes). Find the bad rows, fix or remove them, then add the constraint again.