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;
Add a comment
|
2 Answers
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. 🍻
4 Comments
Johannes H.
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: stackoverflow.com/questions/23542794/….
Eranga Heshan
Wow. Didn't know about that. Thank you!
Johannes H.
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.
Aeternus
is the the same as PRIMARY KEY('sector', 'row', 'number')
You can annotate the entity with an @Index as described in the documentation about "Indices with multiple columns":
@Index(["sector", "row", "number"], { unique: true })