0

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

1 Answer 1

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:

4
  • Oops. Good point. Thank you for pointing this out. Once I added a UNIQUE constraint on listing (id, operation_id) I was able to add the corresponding FOREIGN KEY to listing_card.
    – Gili
    Commented Jun 12, 2022 at 3:28
  • 1
    And you only need the two-column FK. The other 2 FKs from listing_card can be removed. Commented Jun 12, 2022 at 12:01
  • If I were to drop listing_card.listing_operation_id as you suggested, I wouldn't be able to enforce the cross-table constraint UNIQUE (listing_operation_id, index), right? So I don't think this column is redundant.
    – Gili
    Commented Jun 13, 2022 at 3:11
  • 1
    @Gili: I forgot about your last paragraph when adding the bit about dropping listing_operation_id. So you need the column. Removing my misguided addition again. Commented Jun 13, 2022 at 3:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.