conditional unique constraint

I want each person to have only one primary phone number, but also have as many as they want non-primary phone numbers

export const personPhone = pgTable(
  'person_phone',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    isPrimary: boolean('is_primary').default(false),
    personID: uuid('person_id')
      .references(() => person.id, { onDelete: 'cascade' })
      .notNull(),
    phoneID: uuid('phone_id')
      .references(() => phone.id, { onDelete: 'cascade' })
      .notNull(),
  },
  (table) => ({
    // pk: primaryKey({ columns: [table.personID, table.phoneID] }),
    // uniquePrimary: unique().on(table.isPrimary, table.personID),
  })
);

export const personPhone = pgTable(
  'person_phone',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    isPrimary: boolean('is_primary').default(false),
    personID: uuid('person_id')
      .references(() => person.id, { onDelete: 'cascade' })
      .notNull(),
    phoneID: uuid('phone_id')
      .references(() => phone.id, { onDelete: 'cascade' })
      .notNull(),
  },
  (table) => ({
    // pk: primaryKey({ columns: [table.personID, table.phoneID] }),
    // uniquePrimary: unique().on(table.isPrimary, table.personID),
  })
);

14 Replies

Singodimejo•2mo ago

I am not sure if that's possible to restrict on the database. unless you create a separate relation table for person to primary phone and make userID unique and the phone ID not null. why not just add personID to phone table? seems like person to phone should be a one-to-many relationship. Then you can just add isPrimary column to the phone table. though that still does not restrict it to being able to having multiple primary phone number Maybe have two relation tables? one for person to primary which has a unique constraint on the personID and the person to nonprimary without that constraint on the personID but on both personID and phoneID column? if you do it this way you'd need to do a join statement with an extra isPrimary column.

Anas Badran•2mo ago

In the system I'm building there may be two persons that needs to have the same phone number, like two brothers referring to their dad's phone

Singodimejo•2mo ago

oh but they can't share primary number, right?

Anas Badran•2mo ago

they can

Singodimejo•2mo ago

huh

Anas Badran•2mo ago

If you mean they can have the same number as their primary phone number, then yes they can

Singodimejo•2mo ago

I meant like can two users share a primary phone number?

Anas Badran•2mo ago

the isPrimary exists on the person_phone table not on the phone table

export const phone = pgTable('phones', {
  id: uuid('id').primaryKey().defaultRandom(),
  number: varchar('number', { length: 255 }).unique(),
});

export const personPhone = pgTable('person_phone', {
  id: uuid('id').primaryKey().defaultRandom(),
  isPrimary: boolean('is_primary').default(false),
  personID: uuid('person_id')
    .references(() => person.id, { onDelete: 'cascade' })
    .notNull(),
  phoneID: uuid('phone_id')
    .references(() => phone.id, { onDelete: 'cascade' })
    .notNull(),
});

export const phone = pgTable('phones', {
  id: uuid('id').primaryKey().defaultRandom(),
  number: varchar('number', { length: 255 }).unique(),
});

export const personPhone = pgTable('person_phone', {
  id: uuid('id').primaryKey().defaultRandom(),
  isPrimary: boolean('is_primary').default(false),
  personID: uuid('person_id')
    .references(() => person.id, { onDelete: 'cascade' })
    .notNull(),
  phoneID: uuid('phone_id')
    .references(() => phone.id, { onDelete: 'cascade' })
    .notNull(),
});

Singodimejo•2mo ago

I'm trying to understand your business logic for the table relations. one person can have many phone numbers but only one primary phone number one phone number can belong to multiple people. does this sound correct? and also, can two people have the same primary phone number?

Anas Badran•2mo ago

Yes you are right

Singodimejo•2mo ago

and about the primary phone number?? can two people have the same primary phone number??

Anas Badran•2mo ago

yes, two people can have the same phone number as their primary phone number

Singodimejo•2mo ago

then just put the primaryPhone ID as a column on the person table then. so the person to phone table should only hold non primary phone numbers since person to primary is a one-to-one relationship, you should handle it differently to the many-to-many relationship of non primary phone number.

Anas Badran•2mo ago

ok, thank you

Gaming

Programming

conditional unique constraint