Uploaded image for project: 'RAML Module Builder'
  1. RAML Module Builder
  2. RMB-555

Upgrade creates duplicate foreign key constraints

    XMLWordPrintable

    Details

    • Template:
    • Sprint:
      CP: sprint 89, CP: sprint 90, CP: sprint 91
    • Story Points:
      2
    • Development Team:
      Core: Platform

      Description

      How to reproduce:

      create table a (id uuid primary key);
      create table b (id uuid primary key);
      alter table b add column if not exists aid uuid references a(id);
      alter table b add column if not exists aid uuid references a(id);
      \d b
      
                      Table "public.b"
       Column | Type | Collation | Nullable | Default
      --------+------+-----------+----------+---------
       id     | uuid |           | not null |
       aid    | uuid |           |          |
      Indexes:
          "b_pkey" PRIMARY KEY, btree (id)
      Foreign-key constraints:
          "b_aid_fkey" FOREIGN KEY (aid) REFERENCES a(id)
          "b_aid_fkey1" FOREIGN KEY (aid) REFERENCES a(id)
      

      The alter statement adds the foreign key constraint even if the column already exists. This creates a new constraint on each module upgrade.

      Suggested solution:

      alter table b add column if not exists aid;
      do $$
      begin
        begin
          alter table b add constraint b_aid_fkey foreign key (aid) references a(id);
        exception
          when duplicate_object or duplicate_table then null;
        end;
      end $$;
      

      This is needed because PostgreSQL doesn't have an "add constraint if not exists" statement. Reference: https://stackoverflow.com/a/32526723

      In addition drop existing duplicate constraints:

      alter table b drop constraint if exists b_aid_fkey1;
      alter table b drop constraint if exists b_aid_fkey2;
      alter table b drop constraint if exists b_aid_fkey3;
      

        TestRail: Results

          Attachments

            Issue Links

              Activity

                People

                Assignee:
                julianladisch Julian Ladisch
                Reporter:
                julianladisch Julian Ladisch
                Votes:
                0 Vote for this issue
                Watchers:
                3 Start watching this issue

                  Dates

                  Created:
                  Updated:
                  Resolved:

                    TestRail: Runs

                      TestRail: Cases