Drizzle TeamDT
Drizzle Team•13mo ago
N

Self referencing composite primary key table

I'm trying to define a table where each organization can define a parent-child set of locations. Each location has a composite primary key (org, id) but i can't seem to get the self referencing foreign keys to work. Is there a way to accomplish what i'm trying to do? here's my code. Thanks!
export const locations = pgTable(
  'locations',
  {
    id: varchar('id', { length: 60 }).notNull(),
    orgId: varchar('org_id', { length: 60 }).notNull(),
    name: varchar('name', { length: 255 }).notNull(),
    isLeaf: boolean('is_leaf').default(false).notNull(),
    parentId: varchar('parent_id', { length: 60 }),
    parentOrgId: varchar('parent_org_id', { length: 60 }),
  },
  (table) => ({
    pk: primaryKey({
      columns: [table.id, table.orgId],
      name: 'locations_pkey'
    }),
  }),
);

export const locationsRelations = relations(locations, ({ many, one }) => ({
  parent: one(locations, {
    fields: [locations.parentId, locations.parentOrgId],
    references: [locations.id, locations.orgId],
    relationName: 'parent_child',
  }),
  children: many(locations, {
    relationName: 'parent_child',
  }),
}));
Was this page helpful?