I have two entities: Organization and OfficeLocation where one Organization can have many OfficeLocations.
Each OfficeLocation can have its own nickname, that must be unique within the organization.
This makes me think that I should have a @Unique constraint that is composed by nickName and organization.
The question is: how do I declare this in the OfficeLocation entity? Looking at mikro-orm docs I learned that I can define @Unique constraint at entity level or property level.
If I declare in property level, I'll be creating a constraint that will prevent me to include an office location from another organization with the same nickName of an existing one that belongs to a different organization, right?
@Unique()
public nickName!: string;
So, the right way to declare this constraint should be something like this?
@Entity()
@Unique({ properties: ['nickName', 'organization'] })
export class OfficeLocation {
@Property()
@Unique()
public nickName!: string;
// all the other fields...
@ManyToOne(() => Organization)
public organization!: Organization;
}
What's the purpose of having the compound unique constraint over two fields, if you know one of them is unique on its own? In other words, if your nickName is already unique, you know it will be unique even if you add any other column to that constraint. Having both does not make much sense to me. The compound constraint over nick & org id on its own would mean that two locations can have the same nick name.
So if you want to have unique nick names, keep the property level one. If you want to allow duplicities on the table level, but disallow duplicities inside an organization, you should keep just the compound index and remove the property level one.