Drizzle Team•3mo ago

How do you define two tables that have foreign keys to each other? drizzle-kit introspect fails

Drizzle-kit now generates a schema file with type errors - it was previously working prior to 0.24.0. In addition, the generated foreign key entry for multicolumn foreign keys has the columns in the incorrect order. (code below due to length limit)

2 Replies

francisOP•3mo ago

sql source to generate tables:

create schema drizzle_test;

create table drizzle_test.child (
    id uuid primary key default uuid_generate_v4(),
    other_id uuid not null
);

create table drizzle_test.parent (
    id uuid primary key default uuid_generate_v4(),
    other_id uuid not null,
    child_id uuid unique references drizzle_test.child (id) on delete restrict,
    unique (other_id, child_id)
);

alter table drizzle_test.child add constraint test_key
foreign key (other_id, id)
references drizzle_test.parent (other_id, child_id)
on delete cascade deferrable initially deferred;

create schema drizzle_test;

create table drizzle_test.child (
    id uuid primary key default uuid_generate_v4(),
    other_id uuid not null
);

create table drizzle_test.parent (
    id uuid primary key default uuid_generate_v4(),
    other_id uuid not null,
    child_id uuid unique references drizzle_test.child (id) on delete restrict,
    unique (other_id, child_id)
);

alter table drizzle_test.child add constraint test_key
foreign key (other_id, id)
references drizzle_test.parent (other_id, child_id)
on delete cascade deferrable initially deferred;

generated schema that fails:

import { sql } from "drizzle-orm";
import { foreignKey, pgSchema, pgTable, unique, uuid, type AnyPgColumn } from "drizzle-orm/pg-core";

export const drizzleTest = pgSchema("drizzle_test");

export const childInDrizzleTest = drizzleTest.table(
  "child",
  {
    id: uuid("id")
      .default(sql`uuid_generate_v4()`)
      .primaryKey()
      .notNull(),
    otherId: uuid("other_id").notNull(),
  },
  (table) => {
    return {
      testKey: foreignKey({
        columns: [table.id, table.otherId],
        foreignColumns: [parentInDrizzleTest.otherId, parentInDrizzleTest.childId],
        name: "test_key",
      }).onDelete("cascade"),
    };
  },
);

export const parentInDrizzleTest = drizzleTest.table(
  "parent",
  {
    id: uuid("id")
      .default(sql`uuid_generate_v4()`)
      .primaryKey()
      .notNull(),
    otherId: uuid("other_id").notNull(),
    childId: uuid("child_id"),
  },
  (table) => {
    return {
      parentChildIdFkey: foreignKey({
        columns: [table.childId],
        foreignColumns: [childInDrizzleTest.id],
        name: "parent_child_id_fkey",
      }).onDelete("restrict"),
      parentOtherIdChildIdKey: unique("parent_other_id_child_id_key").on(
        table.otherId,
        table.childId,
      ),
      parentChildIdKey: unique("parent_child_id_key").on(table.childId),
    };
  },
);

import { sql } from "drizzle-orm";
import { foreignKey, pgSchema, pgTable, unique, uuid, type AnyPgColumn } from "drizzle-orm/pg-core";

export const drizzleTest = pgSchema("drizzle_test");

export const childInDrizzleTest = drizzleTest.table(
  "child",
  {
    id: uuid("id")
      .default(sql`uuid_generate_v4()`)
      .primaryKey()
      .notNull(),
    otherId: uuid("other_id").notNull(),
  },
  (table) => {
    return {
      testKey: foreignKey({
        columns: [table.id, table.otherId],
        foreignColumns: [parentInDrizzleTest.otherId, parentInDrizzleTest.childId],
        name: "test_key",
      }).onDelete("cascade"),
    };
  },
);

export const parentInDrizzleTest = drizzleTest.table(
  "parent",
  {
    id: uuid("id")
      .default(sql`uuid_generate_v4()`)
      .primaryKey()
      .notNull(),
    otherId: uuid("other_id").notNull(),
    childId: uuid("child_id"),
  },
  (table) => {
    return {
      parentChildIdFkey: foreignKey({
        columns: [table.childId],
        foreignColumns: [childInDrizzleTest.id],
        name: "parent_child_id_fkey",
      }).onDelete("restrict"),
      parentOtherIdChildIdKey: unique("parent_other_id_child_id_key").on(
        table.otherId,
        table.childId,
      ),
      parentChildIdKey: unique("parent_child_id_key").on(table.childId),
    };
  },
);

note that: - this doesn't compile due to the mutual references causing type errors in inference - in addition, the generated testKey on child has the columns in inverse order

francisOP•3mo ago

created issue for mutual foreign keys: https://github.com/drizzle-team/drizzle-orm/issues/2993 created issue for introspect reversing column order: https://github.com/drizzle-team/drizzle-kit-mirror/issues/574

GitHub

[BUG]: unclear how to correctly define mutual foreign keys (introsp...

What version of drizzle-orm are you using? 0.33.0 What version of drizzle-kit are you using? 0.24.2 Describe the Bug Defining tables with foreign keys to each other causes drizzle-kit to output sch...

GitHub

postgres: drizzle-kit introspect generates multi column foreign key...

The following SQL: create schema drizzle_test; create table drizzle_test.parent ( id uuid primary key default uuid_generate_v4(), other_id uuid not null, unique (id, other_id) ); create table drizz...

Gaming

Programming

How do you define two tables that have foreign keys to each other? drizzle-kit introspect fails