The Problem
Overview
So I'm trying to create records that have:
id (primary key, auto-increment)
4 columns that are each a different UUID generated using the MySQL functions:
REPLACE(UUID(), '-', '')
. These 'obfuscated keys' are used for external lookup in other tables. REPLACE() is used to strip the dashes (-) from the UUID generated by MySQL, which is required to later transform the UUID into a binary format. Please don't worry about that!!!
The Table structure is as follows:
+-----------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| info | varchar(32) | YES | UNI | NULL | |
| note | varchar(32) | YES | UNI | NULL | |
| auth | varchar(32) | YES | UNI | NULL | |
| test | varchar(32) | YES | UNI | NULL | |
+-----------------+-------------+------+-----+---------+-------+
Environment
OS : CentOS 7
Database : MariaDB/MySQL
DB Version : 5.5
Partial Solutions Found
I have found a few articles that seem to point "a direction", however I have been unable to solve this.
Trigger before insert : Partially works, but only on the first column. The MySQL error is:
Error Code: 1235. This version of MariaDB doesn't yet support 'multiple triggers with the same action time and event for one table'
Also requires the columns to allownull
defaults. Seems like too much of a band-aid hack.CREATE TRIGGER before_insert_infoid BEFORE INSERT ON user FOR EACH ROW SET new.info = replace(uuid(),'-',''); CREATE TRIGGER before_insert_noteid BEFORE INSERT ON user FOR EACH ROW SET new.note = replace(uuid(),'-',''); CREATE TRIGGER before_insert_authid BEFORE INSERT ON user FOR EACH ROW SET new.auth = replace(uuid(),'-',''); CREATE TRIGGER before_insert_testid BEFORE INSERT ON user FOR EACH ROW SET new.test = replace(uuid(),'-','');
Direct using insert : Works, but means that the logic is not contained in the database. Also, I get a MySQL warning
warning(s): 1364 Field 'id' doesn't have a default value
.INSERT INTO `meanlearn`.`students` (`info`, `note`, `auth`, `test`) VALUES (REPLACE(UUID(),'-',''), REPLACE(UUID(),'-',''), REPLACE(UUID(),'-',''), REPLACE(UUID(),'-',''));
Preliminary Conclusion / Request for Solutions
I'm sure I'm missing something. Any and all suggestions are greatly appreciated.