1

I have two tables tags and 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)
);
  • 1
    It strikes me that your two foreign keys are incompatible. Did you upgrade your server version also? Even if it's not an upgrade issue, I would resolve the conflict if I were you - computers don't like ambiguity! :-) – Vérace Jun 29 '15 at 20:54
  • Why is the 1st FK needed when you have the 2nd? Why the 1st FK has ON DELETE CASCADE and the second has ON DELETE NO ACTION? What exact statement did you run and you got this error? – ypercubeᵀᴹ Jun 29 '15 at 22:25
  • It happens when I do delete from tags where id = '6936db98-000e-4490-b439-eab99cdac8ce'. I take it I should delete the first foreign key and make it so the second on deletes? – cdmckay Jun 29 '15 at 22:32
  • @ypercube: the keys were created at different times, so that's why they both exist – cdmckay Jun 29 '15 at 22:32
  • Please show the CREATE TABLE script. If both columns are not null, then the 1st FK can be dropped. And you should change your 2nd FK if you want deletes to cascade (or keep it as it is if you don't.) If the columns are nullable, it becomes complicated. – ypercubeᵀᴹ Jun 29 '15 at 22:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.