TypeORM unique indices with multiple fields

#typescript #database #sql #programming

I recently run into an issue that I wanted to create a database unique rule that will throw an error where there will be a duplicate record based on certain fields in the database table.

The complexity was that one of these fields was a relation to another table and I was not sure how to implement that.

To give a bit more context, let's take a look at this following example.

Let's assume that we have a school class that consists of students and in our class we want to avoid situations where there will be two students with the same name (two students can appear in our table but only if they are in a different classes).

Class (1) => Student (N)

Code

Usually, if we want to achieve uniqueness in our table we can do so in TypeORM (and in regular SQL as well) by using the UNIQUE keyword. In TypeORM we would use the @Unique(param) decorators and as a param we would pass the name of the field that we would like to be unique (i.e. @Unique('name'))

In our case however, we have to use a different SQL keyword and TypeORM decorator.

Let's take a look at our Student entity:

import {
  Entity,
  Column,
  PrimaryGeneratedColumn,
  ManyToOne,
} from 'typeorm';
import { SchoolClass } from '../school-class.entity';

@Entity()
export class StudentEntity {
  @PrimaryGeneratedColumn()
  id: number;

  @ManyToOne(() => SchoolClass, (schoolClass) => schoolClass.students)
  schoolClass: SchoolClass;

  @Column()
  name: string;
}

Our Table has three columns:

id which is a unique ID for the certain record.
schoolClass which is a ManyToOne relation with Class table
name which is just a name of the student.

To solve our issue with unique names in the school class we will use the @Index decorator (that is then transformed to the INDEX in SQL).

import {
  Entity,
  Column,
  PrimaryGeneratedColumn,
  ManyToOne,
  Index,
} from 'typeorm';
import { SchoolClass } from '../school-class.entity';

@Entity()
@Index(['name', 'schoolClass'], { unique: true }) // Here
export class StudentEntity {
  @PrimaryGeneratedColumn()
  id: number;

  @ManyToOne(() => SchoolClass, (schoolClass) => schoolClass.students)
  schoolClass: SchoolClass;

  @Column()
  name: string;
}

We will pass as an array the values we want to use (in our case name and schoolClass). We are also passing an options object with the property unique set to true. This will assure that inserting a new record in our database with the same name in the school class will result in an DUPLICATE_ENTRY error.

Summary

Nicely done! You have just learned how to create a unique rules for inserting new data into the database. Hopefully, it will help you solve the similar issue I had recently :)

Top comments (4)

rrakso • Jul 18 • Edited

I like to do it this way:

@Entity('my_data')
@Unique(
  SQL_CONSTRAINTS.UNIQUE_NAME_AND_DATE, // enum as contraint name
  (entity: Entity) => [ entity.name, entity.date ]
)
export class Entity extends Base {
  @Column()
  name: string;

  @Column()
  date: string;