How to constrain row values in one table to match row values in another table?

Question

Given:

CREATE TABLE operation
(
  id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY;
);
CREATE TABLE listing
(
  id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
  operation_id BIGINT REFERENCES operation (id)
);
CREATE TABLE listing_card
(
  id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
  listing_id BIGINT REFERENCES listing (id),
  listing_operation_id BIGINT REFERENCES operation (id),
  index SMALLINT NOT NULL,
  UNIQUE (listing_operation_id, index)
);

• Multiple listings may reference the same operation.

How do I ensure that table listing contains a (id, operation_id) combination that matches listing_card's (listing_id, listing_operation_id)?

I want something along the lines of FOREIGN KEY (listing_id, listing_operation_id) REFERENCES listing (id, operation_id) but I cannot do this because (id, operation_id) is not unique (multiple listings could reference the same operation).

Operations are referenced by different kinds of tables. The ultimate goal of the above schema is to ensure that listing_card.index is unique across any listing operations. A single operation may be referenced by 3 different listings, but their card indexes must be unique across each other (across the entire operation).

Answer 1

I want something along the lines of FOREIGN KEY (listing_id, listing_operation_id) REFERENCES listing (id, operation_id) but I cannot do this because (id, operation_id) is not unique (multiple listings could reference the same operation).

No, this is a misunderstanding. listing.id is the PK, so (id, operation_id) is guaranteed to be UNIQUE, and you can proceed as planned.

While being at it, make that UNIQUE constraint on (operation_id, id), since id is already indexed via PK anyway. See:

• Is a composite index also good for queries on the first field?