DT
Drizzle Team•9mo ago
kal

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
clxxiii•9mo 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
kal
kalOP•9mo 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"
No description
kal
kalOP•9mo 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
clxxiii•9mo 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.
kal
kalOP•9mo 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 !

Did you find this page helpful?