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

Question

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.

Answer 1

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

Answer 2

This question isn't related to EAV (Entity Attribute Value) - use your search engine of choice to look for "Celko EAV"

Not sure about exact syntax, but

CREATE TABLE table_b
(
  user1 VARCHAR(25),  -- or INTEGER, ideally of same types, otherwise comparisons are difficult
  user2 VARCHAR(25),  -- or INTEGER
  --  
  -- other fields,
  --
  CONSTRAINT table_b_pk PRIMARY KEY (user_1, ...), 
  -- maybe other fields in the PK - not clear from question
  CONSTRAINT table_b_uq UNIQUE (user_1, user2), 
  -- so that the combination (user_1, user_2) can't be duplicated 
  CONSTRAINT user_1_ne_user_2  CHECK (user1 != user2)  
  -- this will enforce user_1 not equal to user_2 regardless of the sorting of the strings 
  -- you could have CHECK (user1 < user2) if that is key to your requirements
  -- it doesn't appear to be critical from the question.
);

should work - it does in Firebird embedded.

AIUI (As I Understand It), SQLite doesn't support subqueries in CHECK constraints (* red herring - see below - my original sentence was ambiguous and confusing). This situation doesn't require a subquery in the CHECK constraint which only Firebird supports anyway.

The constraint will take care of your (alice, bob) - (bob, alice) issue.