Background: I'm a programmer who, while had to work with simple databases several times, never had to work with complex ones. I.e. I'm proficient with basic stuff, but clueless about advanced features and designs.
I need to create a table with the results of matches between two users, i.e.:
- table_A: user_id (primary int), other stuff
- table_B: user1 (primary foreign key pointing to table_A user_id), user2 (primary foreign key pointing to table_A user_id), other stuff
Can I enforce
user2<user1? This would be in order to prevent rows from having
user1==user2, and to prevent the creation of rows were the couple would be inverted, i.e. I need not to have both
-OR- I'm doing it all wrong in the first place and there's another, completely different, approach to do that?
Obviously I can (and will) check the values before adding them, but as far as I understand, if I can add a constraint in SQL, I would better add it, just to be sure it's consistent.
Database currently is sqlite, but in case this can't be done in sqlite but can be done with other engines (especially if InnoDB), it could be a useful answer too.
PS: I'm not sure if this still counts as EAV or not, since AFAIK EAV implies two primaries from different tables, and a single extra column while instead I'll have more than one.