Drizzle Team•11mo ago

Foreign keys default to NOT NULL with PostgreSQL

I currently have these two tables. I don't understand why Drizzle keeps generating foreign keys with the NOT NULL constraints. I'd really appreciate any help regarding this, been bashing my head against the keyboard for so many hours now. Thanks a lot.

export const assetTable = pgTable(
    'asset',
    {
        id: serial('id').primaryKey(),
    },
);

export const trackTable = pgTable(
    'track',
    {
        id: serial('id').primaryKey(),
        // TODO: investigate why foreign keys default to NOT NULL
        originalAssetId: serial('original_asset_id').references(
            () => assetTable.id,
            {
                onDelete: 'cascade',
            },
        ),
    },
);

export const assetTable = pgTable(
    'asset',
    {
        id: serial('id').primaryKey(),
    },
);

export const trackTable = pgTable(
    'track',
    {
        id: serial('id').primaryKey(),
        // TODO: investigate why foreign keys default to NOT NULL
        originalAssetId: serial('original_asset_id').references(
            () => assetTable.id,
            {
                onDelete: 'cascade',
            },
        ),
    },
);

Below is the generated migration file

CREATE TABLE IF NOT EXISTS "asset" (
    "id" serial PRIMARY KEY NOT NULL,
);

CREATE TABLE IF NOT EXISTS "track" (
    "id" serial PRIMARY KEY NOT NULL,
    "original_asset_id" serial NOT NULL,
);

CREATE TABLE IF NOT EXISTS "asset" (
    "id" serial PRIMARY KEY NOT NULL,
);

CREATE TABLE IF NOT EXISTS "track" (
    "id" serial PRIMARY KEY NOT NULL,
    "original_asset_id" serial NOT NULL,
);

4 Replies

Angelelz•11mo ago

Serial is not null by default. That not null might not be doing anything actually

Angelelz•11mo ago

See here https://www.postgresql.org/docs/current/datatype-numeric.html#DATATYPE-SERIAL

PostgreSQL Documentation

8.1. Numeric Types

8.1. Numeric Types # 8.1.1. Integer Types 8.1.2. Arbitrary Precision Numbers 8.1.3. Floating-Point Types 8.1.4. Serial Types Numeric types consist of …

FelixNgOP•11mo ago

I understand. I want the DB to auto-generate PKs and have 0-1 to 1 relationship with those PKs from other tables. I saw this PR (https://github.com/drizzle-team/drizzle-orm/pull/1471) is still open so should I expect this feature to land in the next release? For the meantime I would just use nanoid in javascript to insert records.

GitHub

Feat: Drizzle-ORM support for Generated columns by Angelelz · Pull ...

This PR will close #261, will close #579 and will close #1464. The API for generated columns will look like this: const users = sqliteTable( 'users', { id: int('id').primary...

Angelelz•11mo ago

I'm not sure about next release but it's in the pipeline

Gaming

Programming

Foreign keys default to NOT NULL with PostgreSQL