foreign key mismatch

how do i create a unique constraint on product_num, Store.store_num, Store.chainName

export const Store = sqliteTable(
    "Store",
    {
        id: text("id").primaryKey().unique(),
        store_num: text("store_num").notNull(),
        chain_name: text("chain_name").notNull(),
                ...
    },

    (t) => ({
        unique: unique().on(t.store_num, t.chain_name),
    })
);

export const Product = sqliteTable(
    "Product",
    {
        id: text("id").primaryKey().unique(),
        product_num: text("product_num").notNull(),
        store_num: text("store_num")
            .notNull()
            .references(() => Store.store_num),
        chainName: text("chainName")
            .notNull()
            .references(() => Store.chain_name),
                 ...
    },

    (t) => ({
        unique: unique().on(t.product_num, t.store_num, t.chainName), // how do i create a unique contraint on product_num, Store.store_num, Store.chainName
    })
);

export const Store = sqliteTable(
    "Store",
    {
        id: text("id").primaryKey().unique(),
        store_num: text("store_num").notNull(),
        chain_name: text("chain_name").notNull(),
                ...
    },

    (t) => ({
        unique: unique().on(t.store_num, t.chain_name),
    })
);

export const Product = sqliteTable(
    "Product",
    {
        id: text("id").primaryKey().unique(),
        product_num: text("product_num").notNull(),
        store_num: text("store_num")
            .notNull()
            .references(() => Store.store_num),
        chainName: text("chainName")
            .notNull()
            .references(() => Store.chain_name),
                 ...
    },

    (t) => ({
        unique: unique().on(t.product_num, t.store_num, t.chainName), // how do i create a unique contraint on product_num, Store.store_num, Store.chainName
    })
);

When i do drizzle-kit push:sqlite

LibsqlError: SQLITE_UNKNOWN: SQLite error: foreign key mismatch - "Product" referencing "Store"
    at mapHranaError (file:///D:/../../node_modules/@libsql/client/lib-esm/hrana.js:257:16)
    at HttpClient.execute (file:///D:/../../node_modules/@libsql/client/lib-esm/http.js:56:19)
    at process.processTicksAndRejections (node:internal/process/task_queues:95:5)
    at async TursoSqlite.run (D:\..\\node_modules\drizzle-kit\bin.cjs:25414:9)
    at async Command.<anonymous> (D:\gitpersonal\sg-backend\node_modules\drizzle-kit\bin.cjs:63360:9) {
  code: 'SQLITE_UNKNOWN',
  [cause]: [ResponseError: SQLite error: foreign key mismatch - "Product" referencing "Store"] {
      message: 'SQLite error: foreign key mismatch - "Product" referencing "Store"',

LibsqlError: SQLITE_UNKNOWN: SQLite error: foreign key mismatch - "Product" referencing "Store"
    at mapHranaError (file:///D:/../../node_modules/@libsql/client/lib-esm/hrana.js:257:16)
    at HttpClient.execute (file:///D:/../../node_modules/@libsql/client/lib-esm/http.js:56:19)
    at process.processTicksAndRejections (node:internal/process/task_queues:95:5)
    at async TursoSqlite.run (D:\..\\node_modules\drizzle-kit\bin.cjs:25414:9)
    at async Command.<anonymous> (D:\gitpersonal\sg-backend\node_modules\drizzle-kit\bin.cjs:63360:9) {
  code: 'SQLITE_UNKNOWN',
  [cause]: [ResponseError: SQLite error: foreign key mismatch - "Product" referencing "Store"] {
      message: 'SQLite error: foreign key mismatch - "Product" referencing "Store"',

4 Replies

mariojuanaaaOP•9mo ago

instead of unique: unique().on(t.product_num, t.store_num, t.chainName) if i use unique: unique().on(t.product_num, Store.store_num, Store.chain_name) i get

LibsqlError: SQL_INPUT_ERROR: SQL input error: no such column: chain_name (at offset 112)

LibsqlError: SQL_INPUT_ERROR: SQL input error: no such column: chain_name (at offset 112)

LibsqlError: SQL_INPUT_ERROR: SQL input error: no such column: store_num(at offset 99)

LibsqlError: SQL_INPUT_ERROR: SQL input error: no such column: store_num(at offset 99)

Aaroned•9mo ago

@mariojuanaaa any reason not to replace the store_num and chainName columns in the Product table with just a storeId foreign key?

storeId: text("storeId").notNull().references(() => Store.id)

storeId: text("storeId").notNull().references(() => Store.id)

mariojuanaaaOP•9mo ago

The goal is to have a unique constraint on the Product.product_num, Store.store_num and Store.chain_name. Basically i want a product to be unique in a given store_num and chain_name. ie, there can only be 1 product abc123 at walmart #5123 Your response from my understanding is adding a foreign key, which i can do, but i want the unique contraint to be added

Aaroned•9mo ago

@mariojuanaaa if you use the primary key of store (id) as a foreign key on product (storeId), then you just need a unique on the product table of storeId and product_num That way a product num is unique for a given store, which already has the unique store_num and chainName defined on the store table.

export const Store = sqliteTable(
  "Store",
  {
      id: text("id").primaryKey().unique(),
      store_num: text("store_num").notNull(),
      chain_name: text("chain_name").notNull(),
      ...
  },
  (t) => ({
      unique: unique().on(t.store_num, t.chain_name),
  })
);

export const Product = sqliteTable(
  "Product",
  {
      id: text("id").primaryKey().unique(),
      product_num: text("product_num").notNull(),
      storeId: text("storeId")
          .notNull()
          .references(() => Store.id),      
      ...
  },
  (t) => ({
      unique: unique().on(t.product_num, t.storeId), 
  })
);

export const Store = sqliteTable(
  "Store",
  {
      id: text("id").primaryKey().unique(),
      store_num: text("store_num").notNull(),
      chain_name: text("chain_name").notNull(),
      ...
  },
  (t) => ({
      unique: unique().on(t.store_num, t.chain_name),
  })
);

export const Product = sqliteTable(
  "Product",
  {
      id: text("id").primaryKey().unique(),
      product_num: text("product_num").notNull(),
      storeId: text("storeId")
          .notNull()
          .references(() => Store.id),      
      ...
  },
  (t) => ({
      unique: unique().on(t.product_num, t.storeId), 
  })
);

Gaming

Programming

foreign key mismatch