I have two very simple tables, User and Ecosystem:
User - ID [PK] - loginName # ... Ecosystem - ID [PK] # ...
I want a many to many relationship, so I created another table, UserEcosystem:
UserEcosystem - UserID - EcosystemID UNIQUE(UserID, EcosystemID)
All pretty standard stuff.
Now, the tricky part.
I want to make loginName unique, but only within a particular ecosystem. So, if a user doesn’t belong to a certain ecosystem, that loginName can be reused by a different user. However, if that login name is already used in that ecosystem, it can’t be.
Anyone have a way to do this at the database level? (I can handle the code level easy enough.)
Would just adding another foreign key to UserEcosystem be appropriate for it, then make a unique index between the three values?
There are actually at least four values like loginName that I need to account for, so I don’t know if that is a smart idea (that would be 6 columns in that table with 6 foreign keys and 4 multi-column UNIQUE indexes… doesn’t sound high performance =p).