EAV-like table with both primary column pointing to the same foreign column

eavforeign keyprimary-keysqlite

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 (alice,bob) and (bob,alice).

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

Best Answer

A simple CHECK constraint works just fine:

$ sqlite
SQLite version 3.8.4.1 2014-03-11 15:27:36
...
sqlite> CREATE TABLE table_B(
   ...>     user1,
   ...>     user2,
   ...>     [other stuff],
   ...>     CHECK (user2 < user1)
   ...> );
sqlite> INSERT INTO table_B VALUES (1, 0);
sqlite> INSERT INTO table_B VALUES (2, 3);
Error: CHECK constraint failed: table_B

(This has nothing to do with EAV.)

Related Question