MySQL Create a 4-part primary key (of concatenated entries)

Question

I would like to pull 4 columns into one so that the unique concatenated value can be more easily distributed throughout the database as foreign key restraints. The 4 column info is important identifying info that I would like to have readily accessible throughout the child tables that are connected to it.

The reason I need the data to come in as 4 chunks, is each part is validated by an enum() value or integer length. None are unique on their own, but combined, they are..

Ideally I would still be able access this data separately (e.g. sort on B)

The options I see are this:

Create composite primary keys, from A, B, C, D and then include A, B, C, D as columns in every table that references it (<-- seems to be the way, but feels clunky -- in most child tables the data will only be utilized as ABCD.)
Create a series of triggers that take A, B, C, D and concatenate them into a new column ABCD, and make THAT column the PK (and make sure to keep the updated if one of the component parts changes -- which they shouldn't, but...) Then I can reference ABCD in every child table.
Utilize a BEFORE UPDATE Trigger that validates the data before placing it into the ABCD PK column then also places it in the A, B, C, D columns (this may be a derivation on 2, or the same)

The complicating factor is that ideally the UI needs to request A, B, C, and D separately, but I can't guarantee the UI program will be able to validate the data for me.

Edit: The goal is to make the database as simple and understandable as possible for the next admin. So tricky Triggers are less ideal than a native solution.

I don't suppose you can define a column that is a concat of other columns?

Or you could use a simple auto-increment ID as primary key and put the other columns in a unique index — juergen d, Jun 30 '16 at 10:57
I was doing this, but I'd really like the ABCD information easily accessible in the child tables (ie, not have to create a view just to "join" that info) Updated to clarify - thanks! — Trees4theForest, Jun 30 '16 at 10:59
Encoding in data into a key is a bad idea called "smart" or "intelligent" keys. — philipxy, Jul 02 '16 at 07:32

score 0 · Accepted Answer · edited May 23 '17 at 12:10

Generated Columns to the rescue!

Added in MySQL version 5.7.6, generated columns can accept a number of arguments to calculate their contents, including CONCAT.

CREATE TABLE `testgen` (
  `A` varchar(45) NOT NULL,
  `B` varchar(45) NOT NULL,
  `C` varchar(45) NOT NULL,
  `D` varchar(45) NOT NULL,
  `genID` varchar(45) GENERATED ALWAYS AS (concat(`A`,`B`,`C`,`D`)) STORED NOT NULL,
  PRIMARY KEY (`genID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

When a generated column is set to "STORED" rather than "VIRTUAL" (Default) that column can be used as both a Primary Key and a Foriegn Key for a child table:

CREATE TABLE `placement` (
  `id` int(11) NOT NULL,
  `ABCD` varchar(45) DEFAULT NULL,
  `otherstuff` varchar(45) DEFAULT NULL,
  `etc` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_placement_testgen_idx` (`ABCD`),
  CONSTRAINT `fk_placement_testgen` FOREIGN KEY (`ABCD`) REFERENCES `testgen` (`genID`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

The genID column updates if changes are made to the component parts (column C for example), and if the FK constraint is set up ON UPDATE CASCADE this change perpetuates through the child tables.

This appears to do EXACTLY what I want.

However!!! As it is a new feature, now that I know it does work as expected, I've opened another question to explore whether this is accepted use.

MySQL Create a 4-part primary key (of concatenated entries)

1 Answers1