SQL - Tuple as Primary Key

Question

How would you create a primary key out of a tuple from a table?

Example -

table a: [id|name]
table b: [id|name]
table axb:[a_id|b_id]

Now we want to add a primary key to axb so that the tuple {alpha, beta} is unique. In other words, the table can't contain the rows (alpha, beta) and (beta, alpha). This would extend to ntuples but I'm asking about couples to start to keep this simple.

I'm working with MySql 14.14/5.5.50 - but I'm hoping that doesn't matter.

Note that a composite key doesn't solve this as a composite key on a_id/b_id would still allow the row with inverse tuple order as a new row

Possible duplicate of [How to properly create composite primary keys - MYSQL](http://stackoverflow.com/questions/5835978/how-to-properly-create-composite-primary-keys-mysql) — PM 77-1, Jan 09 '17 at 23:31
I don't think that it is a duplicate; I think OP wants to avoid that an entry like `(3,1)` is inserted into `axb`, if an entry `(1,3)` already exists. — Stephan Lechner, Jan 09 '17 at 23:33

score 2 · Accepted Answer · answered Jan 09 '17 at 23:32

Unfortunately, you need to use a trigger to do this in MySQL. Many databases would support a syntax such as:

create unique index unq_t_alpha_beta on (least(alpha, beta), greatest(alpha, beta));

Although the syntax might be different, indexes on expressions or computed columns solve this problem.

Another method is to require that alpha < beta (using a check constraint) and then build a unique index on (alpha, beta).

But, MySQL supports none of the following:

check constraints
indexes on expressions
computed columns

So, that leaves triggers.

Thank you @Gordon Linoff . How disappointing. In this specific instance as, I have no self references, your "check" solution would have been great, but not worth moving to a new DBMS for. — fieranmason, Jan 09 '17 at 23:41

score 1 · Answer 2 · answered Jan 10 '17 at 00:17

Just throwing it out there: could you use a generated column which combines the columns always in order (so alpha, beta = alphabeta and beta, alpha = alphabeta), then create a unique index on that generated column? MySql isnt my bag but this says it should be possible: https://dev.mysql.com/doc/refman/5.7/en/create-table-secondary-indexes.html

SQL - Tuple as Primary Key

2 Answers2