Back
[relationships] unique index on two relationships
Hey folks. Consider the code below. It represents an explicit many to many relationship. The UserCommunicationChannelLink contains attributes which do not belong neither in User nor in CommunicationChannel. Consequently, I create a Link table, with manyToOne relations, meaning
the foreignKey are on the many side (the link table).
TypeScript
const collectionId = 'UserCommunicationChannelLink'
await db.createRelationshipAttribute(
db.id,
collectionID,
'User',
'manyToOne',
false,
'user',
undefined,
'restrict',
);
await db.createRelationshipAttribute(
db.id,
collectionID,
'CommunicationChannel',
'manyToOne',
false,
'channel',
undefined,
'restrict',
);
The idea is that a User can have many CommunicationChannels and, each CommunicationChannel may exist in more than one user. However, each pair of <userId, communicationChannelid> must be unique in the Link table.
When I execute this migration, I get a failure on createRelationshipAttribute;
TypeScript
Error: Cannot create an index for a relationship attribute: user
Is it impossible to create a uniqueness index on two foreign keys?
TL;DR
Title: Unique index on two relationships
Hey developers! I need to create a unique index on two foreign keys in my `Link` table for a many-to-many relationship. However, when I execute the migration, I receive an error saying that I cannot create an index for a relationship attribute. Is it impossible to create a uniqueness index on two foreign keys?
Solution: Unfortunately, you cannot create a uniqueness index directly on relationship attributes. However, you can work around this by creating a composite unique index on the two foreign keys in the `Link` table using a combination of `userId` and `communicationChannelId`. This will enforce uniqueness