foreign keys in different tables with the same name couple on update and on delete behavior

[bjornbyte]

given the schema

schema "public" {
  comment = "The default schema"
}

table "table_a" {
  schema = schema.public
  column "id" {
    type = text
  }
  primary_key {
    columns = [column.id]
  }
}

table "table_b" {
  schema = schema.public
  column "id" {
    type = text
  }
  column "table_a_id" {
    type = text
  }
  primary_key {
    columns = [column.id]
  }
  foreign_key "table_a_fk" {
    columns     = [column.table_a_id]
    ref_columns = [table.table_a.column.id]
    on_delete   = "CASCADE"
    on_update   = "CASCADE"
  }
}

table "table_c" {
  schema = schema.public
  column "id" {
    type = text
  }
  column "table_a_id" {
    type = text
  }
  primary_key {
    columns = [column.id]
  }
  foreign_key "table_a_fk" {
    columns     = [column.table_a_id]
    ref_columns = [table.table_a.column.id]
  }
}

atlas will generate

-- create "table_a" table
CREATE TABLE "public"."table_a" ("id" text NOT NULL, PRIMARY KEY ("id"));
-- create "table_b" table
CREATE TABLE "public"."table_b" ("id" text NOT NULL, "table_a_id" text NOT NULL, PRIMARY KEY ("id"), CONSTRAINT "table_a_fk" FOREIGN KEY ("table_a_id") REFERENCES "public"."table_a" ("id") ON UPDATE CASCADE ON DELETE CASCADE);
-- create "table_c" table
CREATE TABLE "public"."table_c" ("id" text NOT NULL, "table_a_id" text NOT NULL, PRIMARY KEY ("id"), CONSTRAINT "table_a_fk" FOREIGN KEY ("table_a_id") REFERENCES "public"."table_a" ("id") ON UPDATE CASCADE ON DELETE CASCADE);

even though the table_c does not specify CASCADE on update or delete.

however naming the foreign_keys with different names (e.g. table_ab_fk and table_ac_fk) produces the expected result:

-- create "table_a" table
CREATE TABLE "public"."table_a" ("id" text NOT NULL, PRIMARY KEY ("id"));
-- create "table_b" table
CREATE TABLE "public"."table_b" ("id" text NOT NULL, "table_a_id" text NOT NULL, PRIMARY KEY ("id"), CONSTRAINT "table_ab_fk" FOREIGN KEY ("table_a_id") REFERENCES "public"."table_a" ("id") ON UPDATE CASCADE ON DELETE CASCADE);
-- create "table_c" table
CREATE TABLE "public"."table_c" ("id" text NOT NULL, "table_a_id" text NOT NULL, PRIMARY KEY ("id"), CONSTRAINT "table_ac_fk" FOREIGN KEY ("table_a_id") REFERENCES "public"."table_a" ("id") ON UPDATE NO ACTION ON DELETE NO ACTION);

it seems strange not to get an error in the first case, but to get unexpected coupling of the table foreign key behavior.

@a8m tagging you in this as requested in discord discussion

[remen]

I looked into this, and it looks like it's because the view referential_constraints does not contain the table name, leading to duplicate entries. I have a draft PR in #2025 which looks like it might fix the issue.

[a8m]

Thanks for the contribution, @remen. Issue was addressed with #2029.