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).
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;
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
