How to create a primary key where order of similar elements is ignored

Question

I'm creating my first SQLite database in a C# program. The program is related to poker/playing cards. I have a table called Flop defined as follows:

CREATE TABLE Flop 
(
     id SMALLINT UNIQUE NOT NULL, 
     card1 TINYINT NOT NULL,
     card2 TINYINT NOT NULL,
     card3 TINYINT NOT NULL,

     CONSTRAINT Flop_pk PRIMARY KEY (card1, card2, card3)
);

Flop contains the id of 3 playing cards from a 52 card deck and I want to avoid duplicate Flop rows where the 3 cards match but are in a different order. If my card ids are 1,2,3 I wish to prevent adding an entry for a Flop of 2,3,1 or 1,3,2 etc. How do I set up my key constraints so that the card order is ignored?

Also, how can I select a flop given that my 3 cards passed into the SELECT statement may be in any order? I assume there must be a more simple way than checking all 6 combos like:

SELECT id FROM Flop WHERE (card1 = 1 AND card2 = 2 AND card3 = 3) 
                       OR (card1 = 3 AND card2 = 1 AND card3 = 2) 
                       OR (card1 = 2 AND card2 = 3 AND card3 = 1) 
                       OR (card1 = 1 AND card2 = 3 AND card3 = 2)
                       OR (card1 = 2 AND card2 = 1 AND card3 = 3)
                       OR (card1 = 3 AND card2 = 2 AND card3 = 1);

The primary key is id, not that triplet... else useless and duplicates: [How do you like your primary keys](https://stackoverflow.com/questions/404040/) • [Generate unique Integers based on GUIDs](https://stackoverflow.com/questions/2920696/) • [Can using Ticks of DateTime.Now can generate duplicates unique identifiers](https://stackoverflow.com/questions/65550289/) • [Guid vs INT - Which is better as a primary key](https://dba.stackexchange.com/questions/264/guid-vs-int-which-is-better-as-a-primary-key) • [Universally unique ID](https://en.wikipedia.org/wiki/Universally_unique_identifier) — , Aug 22 '21 at 17:06
Ah, so if I implement a get hashcode method for Flops I could use that code as both an ID and a way to retrieve entries. Thanks for the idea! — Steve W, Aug 22 '21 at 17:13
My actual DB entries will be in the form: KEY:Flop,Hand1,Hand2 VALUES: Hand1Wins, Draws. I'm using the values to calculate equity having presolved data for all possible subsequent cards. I tried calculating in realtime but it took around 20secs which is far too slow for me. So I'm working on finding an efficient way to store and retrieve the data. — Steve W, Aug 22 '21 at 17:22
On a given flop there are 990 possible outcomes with 2 cards still to be dealt. I've calculated the possible outcomes to determine the equity of 1 hand vs another on a given flop. In my program I calculate the equity of a range of hands vs another range of hands so there can be a large number of combinations. — Steve W, Aug 22 '21 at 17:31

score 0 · Answer 1 · answered Aug 22 '21 at 17:39

If your version of SQLite is 3.31.0+ you can add to the table a generated column which will be a string that consists of the values of the 3 columns in ascending order with a , as separator.
So, if the values of the columns are 5, 7 and 3 the value of the generated column will be '3,5,7'.
This way you can define a UNIQUE constraint on this generated column.

The CREATE statement of the table should be:

CREATE TABLE Flop (
     id INTEGER PRIMARY KEY, 
     card1 INTEGER NOT NULL,
     card2 INTEGER NOT NULL,
     card3 INTEGER NOT NULL,
     cards AS (
       MIN(card1, card2, card3) || ',' ||
       (card1 + card2 + card3 - MIN(card1, card2, card3) - MAX(card1, card2, card3)) || ',' ||
       MAX(card1, card2, card3)
     ) UNIQUE,
     CHECK(card1 <> card2 AND card1 <> card3 AND card2 <> card3)
);

Note that I have defined the column id to be the primary key, although you don't actually need it, since every SQLite table has the column rowid to serve the same purpose.

I added a constraint that I think you need, that not any 2 of the 3 values are equal.

Also, there is no TINYINT data type in SQLite (although you can use it and SQLite will not complain), so I changed them all to INTEGER (you can find more in Datatypes In SQLite Version 3).

Finally, if you want to query the table for 3 specific values, if you want to use the generated column you must provide the 3 values in a string in ascending order as a comma separated list:

SELECT * 
FROM Flop 
WHERE cards = '3,5,7';

Or:

SELECT * 
FROM Flop 
WHERE card1 IN (3,5,7) AND card2 IN (3,5,7) AND card3 IN (3,5,7);

The order of the values in the IN list is not important.

score 0 · Answer 2 · answered Aug 22 '21 at 19:03

The best way to enforce this is to enforce that all cards be in ascending order

CHECK (card1 < card2 AND card2 < card3)

Then all you need to do when selecting is to select the cards in ascending order.

I would recommend at this point to remove the primary key, and make a compound primary key instead (card1, card2, card3)

score -1 · Accepted Answer · answered Aug 29 '21 at 15:22

I resolved this by implementing a GetHashCode method in my C# code that sorts the cards prior to calculating a hashcode for the Flop object. This resulted in a consistent code being generated irrespective of the order of cards input to the method and also gave me a fast way to retrieve a row from the database using the ID as primary key

How to create a primary key where order of similar elements is ignored

3 Answers3