Drizzle Team•8mo ago

How to reference composite key in foreign key

I'm trying to create a foreign key which points to another tables composite primary key. I cannot seem to find anywhere how this is meant to be achieved. I have attached a simplified version of the code I am trying to get to work. As you can see in the moves table I am creating a composite primary key. How do I then reference that in the moveTimestamps table. Also, what should I put in place of varchar in the moveTimestamps table move column, typically I would just match the data type as what it is referencing, but in thise case it should reference both an interger and a varchar.

const moves = pgTable(
  "moves",
  {
    gameID: varchar("game_id")
      .notNull()
      .references(() => games.id, { onDelete: "cascade" }),
    turn: integer("turn").notNull().unique(),  },
  (table) => {
    return {
      pk: primaryKey({ name: "id", columns: [table.gameID, table.turn] }),
    };
  },
);

const moves = pgTable(
  "moves",
  {
    gameID: varchar("game_id")
      .notNull()
      .references(() => games.id, { onDelete: "cascade" }),
    turn: integer("turn").notNull().unique(),  },
  (table) => {
    return {
      pk: primaryKey({ name: "id", columns: [table.gameID, table.turn] }),
    };
  },
);

export const moveTimestamps = pgTable("moveTimestamps", {
  move: varchar("move")
    .notNull()
    .references(() => //!What do i put here!)
    .primaryKey(),
});

export const moveTimestamps = pgTable("moveTimestamps", {
  move: varchar("move")
    .notNull()
    .references(() => //!What do i put here!)
    .primaryKey(),
});

5 Replies

clxxiii•8mo ago

I'm not totally sure, (ie dont copy and paste this in) but hopefully something like this points you in the right direction

export const moveTimestamps = pgTable("moveTimestamps", {
  move_game_id: varchar("move").notNull(),
  move_turn: integer("turn").notNull.unique()
}, (t) => ({
    pk: primaryKey({ columns: [t.move_game_id, t.move_turn]}),
    fk: foreignKey({
      columns: [t.move_game_id, t.move_turn],
      foreignColumns: [moves.gameID, moves.turn]
    })
}))

export const moveTimestamps = pgTable("moveTimestamps", {
  move_game_id: varchar("move").notNull(),
  move_turn: integer("turn").notNull.unique()
}, (t) => ({
    pk: primaryKey({ columns: [t.move_game_id, t.move_turn]}),
    fk: foreignKey({
      columns: [t.move_game_id, t.move_turn],
      foreignColumns: [moves.gameID, moves.turn]
    })
}))

have not tested this snippet either

kalOP•8mo ago

I stumbled across this fk tag on the docs also. I've just implemented the example you suggested. It doesn't seem to work, I get the following error.

error: there is no unique constraint matching given keys for referenced table "moves"

error: there is no unique constraint matching given keys for referenced table "moves"

kalOP•8mo ago

From the docs https://orm.drizzle.team/docs/indexes-constraints#foreign-key it does seem that this is the correct way to do this however. I wonder why this is happening.

Drizzle ORM - Indexes & Constraints

Drizzle ORM is a lightweight and performant TypeScript ORM with developer experience in mind.

clxxiii•8mo ago

oh yea i knew I forgot something: Just like with a single foreign key, you have to make sure that only one row in the table can have that value (unique) With a composite foreign key, you need to make sure there is no row that could share, in your case, both the game id and the turn. (composite unique) Look to the docs for more information:

...
export const composite = pgTable('composite_example', {
  id: integer('id'),
  name: text('name'),
}, (t) => ({
  unq: unique().on(t.id, t.name), // Let drizzle decide the name
  unq2: unique('custom_name').on(t.id, t.name) // Specify a name
}));
...

...
export const composite = pgTable('composite_example', {
  id: integer('id'),
  name: text('name'),
}, (t) => ({
  unq: unique().on(t.id, t.name), // Let drizzle decide the name
  unq2: unique('custom_name').on(t.id, t.name) // Specify a name
}));
...

Drizzle ORM - Indexes & Constraints

Drizzle ORM is a lightweight and performant TypeScript ORM with developer experience in mind.

kalOP•8mo ago

Thank you bud. Sorry for the delayed reply my discord account got disabled for some reason. This is exactly what i was after. Thank you !

Gaming

Programming

How to reference composite key in foreign key