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

Upgrade creates duplicate foreign key constraints

    XMLWordPrintable

Details

    • CP: sprint 89, CP: sprint 90, CP: sprint 91
    • 2
    • 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

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

                Dates

                  Created:
                  Updated:
                  Resolved:

                  TestRail: Runs

                    TestRail: Cases