10

How can I achieve that there aren't 2 same records in db with values of these 3 columns combined being the same?

  @Column()
  sector: string;

  @Column()
  row: string;

  @Column()
  number: string;
brand marke
  • 369
  • 2
  • 16

2 Answers2

11

Updated Answer:

While creating a unique @Index like @Johannes suggested works for you, semantically the better solution would be creating a composite unique key.

@Unique(["sector", "row", "number"])

Based on https://stackoverflow.com/a/23665806/4343332, Postgres seems to handles both Unique Index and Unique Constraint in the same way internally.

Hence there will be no performance benefit in choosing either one.

Thank you for the insight @Johannes H.

Eranga Heshan
  • 5,133
  • 4
  • 25
  • 48
  • 1
    Most DBMS will create an index structure for unique-constraints as well. Somehow, they need to determine if a value already exists, and this can only be done efficiently if some sort of index exists (otherwise it would require scanning the entire table). For PostgreSQL also see this answer: https://stackoverflow.com/questions/23542794/postgres-unique-constraint-vs-index#:~:text=Uniqueness%20is%20a%20constraint.,be%20associated%20only%20with%20constraints. – Johannes H. Apr 28 '21 at 04:26
  • Wow. Didn't know about that. Thank you! – Eranga Heshan Apr 28 '21 at 04:33
  • 1
    Sure thing. In general however, your answer is absolutely correct. Postgres using an index either way is an implementation detail. One should always use the semantically better suitable way, and in this case I agree with you that this would be a constraint, not an index. – Johannes H. Apr 28 '21 at 04:35
  • is the the same as PRIMARY KEY('sector', 'row', 'number') – Aeternus Mar 17 '23 at 22:26
2

You can annotate the entity with an @Index as described in the documentation about "Indices with multiple columns":

@Index(["sector", "row", "number"], { unique: true })
Johannes H.
  • 5,875
  • 1
  • 20
  • 40