Ensuring members of a link table share a common property

Question

I'm using a link table to represent a many-to-many relationship as follows (slightly modified for my use case from this previous answer):

CREATE TABLE owner(
  owner_id uuid DEFAULT gen_random_uuid(),
  PRIMARY KEY(owner_id)
);

CREATE TABLE product(
  product_id uuid DEFAULT gen_random_uuid(),
  owner_id uuid NOT NULL,
  PRIMARY KEY(product_id)
  FOREIGN KEY(owner_id) REFERENCES owner(owner_id)
);

CREATE TABLE bill(
  bill_id uuid DEFAULT gen_random_uuid(),
  owner_id uuid NOT NULL,
  PRIMARY KEY(bill_id),
  FOREIGN KEY(owner_id) REFERENCES owner(owner_id)
);

CREATE TABLE bill_product(
  bill_id uuid,
  product_id uuid,
  PRIMARY KEY(bill_id, product_id),
  FOREIGN KEY(bill_id) REFERENCES bill(bill_id),
  FOREIGN KEY(product_id) REFERENCES product(bill_id)
);

This will of course allow a given bill to belong to many products and vice versa. However, I am wondering what the best way is to ensure that the bill and product belong to the same owner.

I see two options:

Trigger - Have the owner of the bill and product checked BEFORE INSERT, e.g.

CREATE OR REPLACE FUNCTION verify_bill_product_owner() RETURNS trigger AS $trg$
BEGIN
  IF (SELECT owner_id FROM product WHERE product_id = NEW.product_id)
     <>
     (SELECT owner_id FROM bill WHERE bill_id = NEW.bill_id)
  THEN
    RAISE EXCEPTION 'bill and product do not belong to different owners';
  END IF;

  RETURN NEW;
END
$trg$ LANGUAGE plpgsql;

CREATE TRIGGER tr_bill_product_biu
  BEFORE INSERT OR UPDATE on bill_product
  FOR EACH ROW
  EXECUTE PROCECURE verify_bill_product_owner();

Compound foreign key - Add the owner_id to the bill_product table and have something like:

-- ..
owner_id uuid,
FOREIGN KEY(owner_id, bill_id) REFERENCES bill(owner_id, bill_id),
FOREIGN KEY(owner_id, product_id) REFERENCES product(product_id, product_id),
-- ..

I think both would work I'm just wondering which is most idiomatic and which would work best in a multi-client/session environment.

I'm using Postgres 9.4.2 :-)

Why products need an owner? The n:m relationship should clearly state ownership, if only `bill` has an owner. — pozs, Jun 30 '15 at 15:27
@pozs I agree that it is clearly stated, I just want to ensure it is enforced :-) — jabclab, Jun 30 '15 at 15:40

score 0 · Answer 1 · answered Jun 30 '15 at 15:43

The compound foreign key is cleaner, but requires more space and may have performance implications when the table gets large. The trigger results in the same effect, but I would rewrite the function as follows:

CREATE OR REPLACE FUNCTION verify_bill_product_owner() RETURNS trigger AS $trg$
BEGIN
  PERFORM *
  FROM product
  JOIN bill USING (owner_id)
  WHERE product_id = NEW.product_id AND bill_id = NEW.bill_id;

  IF NOT FOUND THEN
    RETURN NULL;
  ELSE
    RETURN NEW;
  END IF;
END; $trg$ LANGUAGE plpgsql;

Ensuring members of a link table share a common property

1 Answers1