Specific field parameter when creating a table MySQL

user_12007562 0
Viewed 75

I'm using MySQL Workbench.

I would like to create a table named courseInfo and I want to put a column named moduleCode in it, but I want it to always be similar in format: CFSM H0000 where the four zeros are a number that increases starting with 0000.

For example:

CFSM H0001
CFSM H0002
[..]
mysql sql
1 Answers

You cannot auto-increment character type columns in MySQL, as auto-increment is only possible on integer type columns. One (alphanumeric) auto-incrementing moduleCode column would therefore not be possible. However, you could try splitting up the moduleCode into two columns, for example like so:

CREATE TABLE `courseInfo` (
 `prefix` CHAR(6) NOT NULL DEFAULT 'CFSM H',
 `id` SMALLINT(4) UNSIGNED ZEROFILL NOT NULL AUTO_INCREMENT,
  KEY (`id`)
) AUTO_INCREMENT = 0;

Where prefix could for example be "CFSM H" and id could be 0001

Then, upon executing SELECT statements, you could merge the prefix column with the id column into a moduleCode column with CONCAT, e.g.:

SELECT CONCAT(`prefix`, `id`) as `moduleCode` FROM `courseInfo`;

An alternative approach (from MySQL version 5.7 and up) seems to be the use of a generated column, for example:

CREATE TABLE `courseInfo` (
 `prefix` CHAR(6) NOT NULL DEFAULT 'CFSM H',
 `id` SMALLINT(4) UNSIGNED ZEROFILL NOT NULL AUTO_INCREMENT,
 `moduleCode` CHAR(10) AS (CONCAT(`prefix`, `id`)),
  KEY (`id`)
) AUTO_INCREMENT = 0;

However, the above example of a generated column would not work, because moduleCode is dependent on an auto-increment column, and the auto-increment is not executed yet at the time the generated column is computed. See also: https://dev.mysql.com/doc/refman/5.7/en/create-table-generated-columns.html. It would throw an ER_GENERATED_COLUMN_REF_AUTO_INC error.

You could therefore use the first solution, or try to add moduleCode as a column and use an AFTER INSERT trigger to update its value:

CREATE TABLE `courseInfo` (
 `prefix` CHAR(6) NOT NULL DEFAULT 'CFSM H',
 `id` SMALLINT(4) UNSIGNED ZEROFILL NOT NULL AUTO_INCREMENT,
 `moduleCode` CHAR(10),
  KEY (`id`),
  UNIQUE KEY `unique_index` (`prefix`,`id`)
) AUTO_INCREMENT = 0;

DELIMITER //
CREATE TRIGGER `addModuleCode` AFTER INSERT ON `courseInfo`
FOR EACH ROW
BEGIN
  UPDATE `courseInfo` SET `moduleCode` = CONCAT(NEW.`prefix`, NEW.`id`) WHERE `prefix` = NEW.`prefix` AND `id` = NEW.`id`;
END;//
DELIMITER ;
user_28492340
Related