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

All we need is an easy explanation of the problem, so here it is.

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

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 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:

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Tags: , , ,

Leave a Reply