'Using PostgreSQL composite foreign keys to prevent cross organization associations

I am considering using composite foreign keys in PostgreSQL to ensure that rows which belongs to different organizations cannot be associated with each other. Is this a good idea, or will I live to regret it?

For example, in a system which has addresses and shipments, both of which belong directly to an organization, if a shipment has an address, with composite foreign key constraints I could limit that the address must belong to the same organization.

So simplified, I would have something like this:

CREATE TABLE organizations (
    id SERIAL PRIMARY KEY,
    name TEXT
);

CREATE TABLE addresses (
    id SERIAL PRIMARY KEY,
    organization_id INTEGER NOT NULL,
    FOREIGN KEY (organization_id) REFERENCES organizations (id),
    UNIQUE (id, organization_id)
);

CREATE TABLE shipments (
    id SERIAL PRIMARY KEY,
    organization_id INTEGER NOT NULL,
    address_id INTEGER NOT NULL,
    FOREIGN KEY (organization_id) REFERENCES organizations (id),
    FOREIGN KEY (address_id, organization_id) REFERENCES addresses (id, organization_id),
    UNIQUE (id, organization_id)
);

On the surface this seems good, since it ensures better data integrity, but possibly insertion could become slow, and maybe there are other issues I am not foreseeing?



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source