[SQL] Global foreign keys?

I'm using PostgreSQL with these tables:

CREATE TABLE transports (
"company_id" smallint NOT NULL,
"id" uuid NOT NULL,
"measure_unit_id" uuid NOT NULL
);

ALTER TABLE ONLY transports ADD CONSTRAINT transports_pkey PRIMARY KEY (company_id, id);
ALTER TABLE ONLY transports ADD CONSTRAINT transports_measure_unit_id_fkey FOREIGN KEY (company_id, measure_unit_id) REFERENCES measure_units(company_id, id);

CREATE TABLE measure_units (
"company_id" smallint NOT NULL,
"id" uuid NOT NULL,
"name" character varying NOT NULL,
);

ALTER TABLE ONLY measure_units ADD CONSTRAINT measure_units_pkey PRIMARY KEY (company_id, id);


Everything works good.

Since the PK is a combo of company_id and id right now I'm adding the same rows in table measure_units with only column company_id being different.

Is there a way to tell Postgres that some rows are OK for each company_id and other not?

Example:

INSERT INTO "measure_units" ("company_id", "id", "name") VALUES (1, new_uuid(), 'KG');
INSERT INTO "measure_units" ("company_id", "id", "name") VALUES (1, new_uuid(), 'G');
INSERT INTO "measure_units" ("company_id", "id", "name") VALUES (1, new_uuid(), 'M');
INSERT INTO "measure_units" ("company_id", "id", "name") VALUES (1, new_uuid(), 'L');

INSERT INTO "measure_units" ("company_id", "id", "name") VALUES (2, new_uuid(), 'KG');
INSERT INTO "measure_units" ("company_id", "id", "name") VALUES (2, new_uuid(), 'G');
INSERT INTO "measure_units" ("company_id", "id", "name") VALUES (2, new_uuid(), 'M');
INSERT INTO "measure_units" ("company_id", "id", "name") VALUES (2, new_uuid(), 'L');

INSERT INTO "measure_units" ("company_id", "id", "name") VALUES (3, new_uuid(), 'KG');
INSERT INTO "measure_units" ("company_id", "id", "name") VALUES (3, new_uuid(), 'G');
INSERT INTO "measure_units" ("company_id", "id", "name") VALUES (3, new_uuid(), 'M');
INSERT INTO "measure_units" ("company_id", "id", "name") VALUES (3, new_uuid(), 'L');


I would like to use something like:

INSERT INTO "measure_units" ("company_id", "id", "name") VALUES (ALL_COMPANIES, new_uuid(), 'KG');
INSERT INTO "measure_units" ("company_id", "id", "name") VALUES (ALL_COMPANIES, new_uuid(), 'G');
INSERT INTO "measure_units" ("company_id", "id", "name") VALUES (ALL_COMPANIES, new_uuid(), 'M');
INSERT INTO "measure_units" ("company_id", "id", "name") VALUES (ALL_COMPANIES, new_uuid(), 'L');

Continue reading...