DEV Community

Cover image for TypeORM unique indices with multiple fields
Jakub Andrzejewski
Jakub Andrzejewski

Posted on

TypeORM unique indices with multiple fields

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)
Enter fullscreen mode Exit fullscreen mode

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;
}
Enter fullscreen mode Exit fullscreen mode

Our Table has three columns:

  1. id which is a unique ID for the certain record.
  2. schoolClass which is a ManyToOne relation with Class table
  3. 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;
}
Enter fullscreen mode Exit fullscreen mode

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)

Collapse
 
rrakso profile image
rrakso • 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;
Enter fullscreen mode Exit fullscreen mode
Collapse
 
talenttinaapi profile image
talent

Forward and upward,keep up the good work

Collapse
 
saiteja13427 profile image
Sai Teja

We can actually use @unique['name', 'schoolClass'] itself. Typeorm supports that.

Collapse
 
dinerdas profile image
Diner Das • Edited

Thanks for making your first comment Sai!