How to create multiple unique constraint on different columns?

Question

Below is the SQL for creating 'users' table. In this table we want to have both 'userguid' and 'username' as unique fields. In typeorm '@Unique' decorator on entity creates single constraint for both columns - @Unique("USERS_UQ", ["userName", "userGUID"]).

Is there any way to create two different unique constraints on 'userguid' and 'username' columns.

SQL:

create table users (
   id              number
      generated by default as identity,
   userguid        varchar2(256) not null,
   username        varchar2(256) not null,
   employeeid      varchar2(256) not null,
   emailid         varchar2(256) not null,
   firstname       varchar2(256) not null,
   middlename      varchar2(256),
   lastname        varchar2(256) not null,
   managerdn       varchar2(4000),
   status          varchar2(256) default 'INACTIVE' not null,
   createdby       varchar2(256) not null,
   creationdate    timestamp(6) with time zone default cast(systimestamp at time zone 'UTC' as timestamp with time zone) not null
   ,
   sourceip        varchar2(256) default '0.0.0.0' not null,
   lastupdatedby   varchar2(256),
   lastupdateddate timestamp with time zone,
   constraint users_pk primary key ( id ),
   constraint users_uq_userguid unique ( userguid ),
   constraint users_uq_username unique ( username ),
   constraint users_status check ( status in ( 'ACTIVE',
                                               'INACTIVE' ) )
);

import { Column, Entity, OneToMany, PrimaryColumn, Unique } from "typeorm";
import { UserProfileORM } from "./userprofile";

@Entity({ name: "USERS" })
@Unique("USERS_UQ", ["userName"])
export class UserORM {
  @PrimaryColumn({
    name: "ID",
    type: "number",
    primaryKeyConstraintName: "USERS_PK",
  })
  id!: number;

  @Column({
    name: "USERGUID",
    type: "varchar2",
    length: 256,
    nullable: false,
  })
  userGUID!: string;

  @Column({ name: "USERNAME", nullable: false, type: "varchar2", length: 256 })
  userName!: string;

  @Column({ name: "EMPLOYEEID", nullable: false, type: "varchar2", length: 256 })
  employeeID!: string;

  @Column({ name: "EMAILID", nullable: false, type: "varchar2", length: 256 })
  emailID!: string;

  @Column({
    name: "FIRSTNAME",
    nullable: false,
    type: "varchar2",
    length: 256,
  })
  firstName!: string;

  @Column({
    name: "MIDDLENAME",
    nullable: true,
    type: "varchar2",
    length: 256,
  })
  middleName?: string;

  @Column({ name: "LASTNAME", nullable: false, type: "varchar2", length: 256 })
  lastName!: string;

  @Column({
    name: "MANAGERDN",
    nullable: true,
    type: "varchar2",
    length: 4000,
  })
  managerDN?: string;

  @Column({
    default: "INACTIVE",
    name: "STATUS",
    nullable: false,
    type: "varchar2",
    length: 256,
  })
  status!: string;

  @Column({
    name: "CREATEDBY",
    nullable: false,
    type: "varchar2",
    length: 256,
  })
  createdBy!: string;

  @Column({
    default: () =>
      `CAST(systimestamp AT TIME ZONE 'UTC' AS TIMESTAMP WITH TIME ZONE)`,
    name: "CREATIONDATE",
    nullable: false,
    type: "timestamp with time zone",
  })
  creationDate!: Date;

  @Column({
    default: "0.0.0.0",
    name: "SOURCEIP",
    nullable: false,
    type: "varchar2",
    length: 256,
  })
  sourceIP?: string;

  @Column({
    name: "LASTUPDATEDBY",
    nullable: true,
    type: "varchar2",
    length: 256,
  })
  lastUpdatedBy?: string;

  @Column({
    name: "LASTUPDATEDDATE",
    nullable: true,
    type: "timestamp with time zone",
  })
  lastUpdatedDate?: Date;
}

Answer 1

To create separate unique constraints on both userguid and username columns in TypeORM, you don't need to use the @Unique decorator at the class level. Instead, you can apply the @Unique decorator at the property level for each column.

So you can change from this:

@Entity({ name: "USERS" })
@Unique("USERS_UQ", ["userName"])
export class UserORM {...}

To:

@Entity({ name: "USERS" })
export class UserORM {
...
   @Column({
       name: "USERGUID",
       type: "varchar2",
       length: 256,
       nullable: false,
    })
  @Unique("USERS_UQ_USERGUID", ["userGUID"])
  userGUID!: string;

  @Column({
    name: "USERNAME",
    type: "varchar2",
    length: 256,
    nullable: false,
 })
 @Unique("USERS_UQ_USERNAME", ["userName"])
 userName!: string;
...
}

This approach generates two distinct UNIQUE constraints for the userguid and username columns when you synchronize your database schema.