Drizzle TeamDT
Drizzle Team•2y ago
Tobias

nullsNotDistinct on composite unique

I have the following table
export const customersTable = pgTable("customers", {
    id: serial('id').primaryKey(),
    first_name: text('first_name'),
    last_name: text('last_name'),
    email: text('email'),
    tenant_id: integer('tenant_id').references(() => tenantsTable.id, { onDelete: 'cascade' }).notNull()
}, (t) => ({
    unq: unique().on(t.email, t.tenant_id).nullsNotDistinct()
}));


My wish is to be able to add how many customers as possible to a specific tenant_id with null email and no constraint. But only one customer per email and per tenant should be allowed.

The above solution doesn't work and I want to be able to specifically target "nullsNotDisticint" on the email column only and not the whole composite unique key.

How can I solve this?
Was this page helpful?