'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 |
|---|
