Issues with foreign keys and entering null values
I have 2 schemas:
export const items = createTable(
"items",
{
id: bigserial("id", { mode: 'bigint' }).primaryKey(),
model: bigserial("model", { mode: 'bigint' }).references(() => models.id, { onDelete: 'cascade' }),
},
);
export const models = createTable(
"models",
{
id: bigserial("id", { mode: 'bigint' }).primaryKey(),
containerid: bigserial("containerid", { mode: 'bigint' }).references(() => containers.id, { onDelete: 'cascade' }).notNull(),
name: varchar("name", { length: 256 }).notNull(),
},
);
when I try to create an item in the db I get this error: "PostgresError: insert or update on table 'trackr_items' violates foreign key constraint 'trackr_items_model_trackr_models_id_fk'" and when I use drizzle studio, I cant put NULL in as it gives the following error there as well:
PostgresError: insert or update on table "trackr_items" violates foreign key constraint "trackr_items_model_trackr_models_id_fk"
at ErrorResponse (C:\dev\17robots\trackr\node_modules.pnpm\[email protected]\node_modules\drizzle-kit\bin.cjs:79677:27)
at handle (C:\dev\17robots\trackr\node_modules.pnpm\[email protected]\node_modules\drizzle-kit\bin.cjs:79454:7)
at TLSSocket.data (C:\dev\17robots\trackr\node_modules.pnpm\[email protected]\node_modules\drizzle-kit\bin.cjs:79277:9)
at TLSSocket.emit (node:events:520:28)
at addChunk (node:internal/streams/readable:559:12)
at readableAddChunkPushByteMode (node:internal/streams/readable:510:3)
at Readable.push (node:internal/streams/readable:390:5)
at TLSWrap.onStreamRead (node:internal/stream_base_commons:191:23)
at TLSWrap.callbackTrampoline (node:internal/async_hooks:130:17) {
severity_local: 'ERROR',
severity: 'ERROR',
code: '23503',
detail: 'Key (model)=(0) is not present in table "trackr_models".',
schema_name: 'public',
table_name: 'trackr_items',
constraint_name: 'trackr_items_model_trackr_models_id_fk',
file: 'ri_triggers.c',
line: '2608',
routine: 'ri_ReportViolation'
}
this is what the insert looks like in drizzle tables:
!image
When I enter the sql manually with this query:
INSERT INTO
trackr_items (containerid, NAME, ordering,model,containersource)
VALUES
(14, 'hello', 1,null,null);
it works, but when I enter in with this query:
INSERT INTO
trackr_items (containerid, NAME, ordering)
VALUES
(14, 'hello', 1);
I get:
PostgresError: insert or update on table "trackr_items" violates foreign key constraint "trackr_items_model_trackr_models_id_fk"
at ErrorResponse (C:\dev\17robots\trackr\node_modules.pnpm\[email protected]\node_modules\drizzle-kit\bin.cjs:79677:27)
at handle (C:\dev\17robots\trackr\node_modules.pnpm\[email protected]\node_modules\drizzle-kit\bin.cjs:79454:7)
at TLSSocket.data (C:\dev\17robots\trackr\node_modules.pnpm\[email protected]\node_modules\drizzle-kit\bin.cjs:79277:9)
at TLSSocket.emit (node:events:520:28)
at addChunk (node:internal/streams/readable:559:12)
at readableAddChunkPushByteMode (node:internal/streams/readable:510:3)
at Readable.push (node:internal/streams/readable:390:5)
at TLSWrap.onStreamRead (node:internal/stream_base_commons:191:23)
at TLSWrap.callbackTrampoline (node:internal/async_hooks:130:17) {
severity_local: 'ERROR',
severity: 'ERROR',
code: '23503',
detail: 'Key (model)=(20) is not present in table "trackr_models".',
schema_name: 'public',
table_name: 'trackr_items',
constraint_name: 'trackr_items_model_trackr_models_id_fk',
file: 'ri_triggers.c',
line: '2608',
routine: 'ri_ReportViolation'
}
it wont let me set the default to be null either, it expects the bigint and Im not seeing a function regarding null aside from notNull()
is there something Im doing wrong? Im using vercel postgres also
1 Reply
Just responding to this for visibility and to see if anyone has any solutions