I have two tables
tag_memberships in a PostgreSQL database. On the
tag_memberships table, I have two constraints. The first is this:
ALTER TABLE tag_memberships ADD CONSTRAINT tag_memberships_tag_id_fkey FOREIGN KEY (tag_id) REFERENCES tags (id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE CASCADE
Which means when a tag is deleted, delete all tag memberships that are referencing it.
The second constraint is:
ALTER TABLE tag_memberships ADD CONSTRAINT tag_memberships_organization_id_fkey1 FOREIGN KEY (organization_id, tag_id) REFERENCES tags (organization_id, id) MATCH SIMPLE ON UPDATE CASCADE ON DELETE NO ACTION;
The purpose of this constraint is to ensure that the organization_id on this record will always be the same as the one on the tag it is referencing.
Now these constraints worked fine until I moved my database to a new laptop. Now I get this error on deletion:
ERROR: update or delete on table "tags" violates foreign key constraint "tag_memberships_organization_id_fkey1" on table "tag_memberships"
Detail: Key (organization_id, id)=(38712483-a0f3-46d3-b9fc-6085327475b0, 6936db98-000e-4490-b439-eab99cdac8ce) is still referenced from table "tag_memberships".
Why would the constraints suddenly not work? Is it because when I re-created the database they were created in a difference order and that is important for foreign key checks?
-- Create table scripts CREATE TABLE tags ( id uuid NOT NULL, name character varying(255) NOT NULL, organization_id uuid NOT NULL, -- tenant id PRIMARY KEY (id) ); CREATE TABLE tag_memberships ( id uuid NOT NULL, tag_id uuid NOT NULL, template_id uuid NOT NULL, organization_id uuid NOT NULL, -- tenant id PRIMARY KEY (id) );