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

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.

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

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.

• What do you mean with "AIUI, SQLite doesn't support subqueries. This isn't one." It's not clear at all. – ypercubeᵀᴹ Apr 2 '14 at 22:39
• Sorry - assuming all have (near-) perfect English (not referring to you - I accept that my English was not the clearest). As I Understand It (AIUI) - Google gets that no problem. What I meant was, a) the OP's (Original Poster's) question does not require a subquery and b) SQLite does not support them in any case. BTW, Firebird does. Paul... – Vérace Apr 3 '14 at 1:05
• Take a look at @CL's answer. Not sure where all this subquery business came from, saw no mention of it in the OP's question. And if it makes you feel any better, as a native English speaker I had no idea what AIUI meant either (I'm not a fan of such abbreviations) ;) – Steve Pettifer Apr 3 '14 at 11:43
• (also, it's not clear to me what does "celko eav" have to do about this, maybe I googled the wrong thing? I just found a few articles bad-mouthing about EAV, and I can't see how they are relevant...) – o0'. Apr 3 '14 at 11:59
• @StevePettifer my wild guess on what Verace meant (after reading it for the 6th time) is that the CHECK constraint could also be written with a subquery: CHECK ((user1,user2) NOT IN (SELECT user2,user1 FROM table )) but it isn't needed in this case and can be written more simply. (Firebird is one of the few if not the only DBMS that supports subqueries in CHECK constraints.) – ypercubeᵀᴹ Apr 3 '14 at 12:15