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()
}));

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?

0 Replies

No replies yetBe the first to reply to this messageJoin

Gaming

Programming

nullsNotDistinct on composite unique