Transform composite primary key into ID

Question

I have a table that has a composite primary key which consists of three columns.

+--------------+--------------+------+-----+
|    field     |     type     | Null | Key |
+--------------+--------------+------+-----+
| composed_at  | TIMESTAMP    | NO   | PRI |
| index_at_day | INT          | NO   | PRI |
| panel_name   | VARCHAR(20)  | NO   | PRI |
| meta         | VARCHAR(255) | NO   |     |
| amount       | DECIMAL(5,2) | NO   |     |
+--------------+--------------+------+-----+

This was quite ok for some time, but now I need to make a foreign key reference to this table. Which means that I need to add all three columns into table that will store the reference.

I don't really like the idea of adding all three columns and looking for an elegant way to replace them with something simpler. My initial approach is to generate a hashcode from these three columns in the application and use the hashcode as a surrogate key (e.g. ID).

Is there some sort of common approach for such situations, am I inventing another wheel?

@Strawberry nothing particular, except for management of three columns instead of one. Imagine that composite key can be of 10 columns. Wouldn't it will be easier to manage one column instead of 10 ? — JackHammer, Jun 15 '20 at 11:46
@Strawberry Indeed. I am basically searching for a common way of transforming composite key into surrogate key. — JackHammer, Jun 15 '20 at 11:51
But there's no transformation. A surrogate key is (normally) just an autoincrementing id. — Strawberry, Jun 15 '20 at 11:52
@Strawberry I think I got it. The solution would be to make PK as auto-increment column, and to preserve there three columns as a unique key! Thank you! — JackHammer, Jun 15 '20 at 12:09

Transform composite primary key into ID

0 Answers0