Skip to content

Bug: Incorrect constraint ordering causes failure in apply #248

@sam-axcient

Description

@sam-axcient

Step 1

Add some tables:

CREATE TABLE b (
    id serial,
    owner_id integer NOT NULL,
    PRIMARY KEY (id)
);

CREATE TABLE a (
    id serial,
    b_id integer NOT NULL,
    owner_id integer NOT NULL,
    PRIMARY KEY (id)
);

Step 2

Now add owner_id to the foreign key:

CREATE TABLE b (
    id serial,
    owner_id integer NOT NULL,
    PRIMARY KEY (id),
    UNIQUE (id, owner_id)
);

CREATE TABLE a (
    id serial,
    b_id integer NOT NULL,
    owner_id integer NOT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (b_id, owner_id) REFERENCES b (id, owner_id) ON DELETE CASCADE
);

Expected behavior: To apply step 2 without any issues
Actual behavior: Here's the plan and the error:

Summary by type:
  tables: 2 to modify

Tables:
  ~ a
    + a_b_id_owner_id_fkey (constraint)
  ~ b
    + b_id_owner_id_key (constraint)

DDL to be executed:
--------------------------------------------------

ALTER TABLE a
ADD CONSTRAINT a_b_id_owner_id_fkey FOREIGN KEY (b_id, owner_id) REFERENCES b (id, owner_id) ON DELETE CASCADE NOT VALID;

ALTER TABLE a VALIDATE CONSTRAINT a_b_id_owner_id_fkey;

ALTER TABLE b
ADD CONSTRAINT b_id_owner_id_key UNIQUE (id, owner_id);

Do you want to apply these changes? (yes/no): yes

Applying changes...

Executing group 1/1...
  Executing 3 statements in implicit transaction
Error: failed to execute concatenated statements in group 1: ERROR: there is no unique constraint matching given keys for referenced table "b" (SQLSTATE 42830)

Context

Tested version 1.6.1@e839db7

Metadata

Metadata

Assignees

Labels

No labels
No labels

Type

Projects

No projects

Milestone

No milestone

Relationships

None yet

Development

No branches or pull requests

Issue actions