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

Answer 1

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.

Answer 2

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