Home / Questions / 407180
Accepted
Jordi NC
Asked: 2021-11-17 06:49:27 +0800 CST 2021-11-17 06:49:27 +0800 CST

Calculations on DEFAULT fields in MariaDB

  • 772

Hello and thank you very much in advance for your help,

I'm trying something that I'm sure should be easy, but after searching for a few days, I've decided to ask this community. Although with some experience in programming, I'm a novice when it comes to databases...

I am using MariaDB 10.3.21 , and I have the following table:

  CREATE TABLE `Inventory` (
  `InventoryId` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'InternalIdentifier',
  `ProductId` int(10) unsigned NOT NULL COMMENT 'Related Product Identifier',
  `DeviceSN` varchar(40) DEFAULT concat(year(current_timestamp()),month(current_timestamp()),dayofmonth(current_timestamp())),
  `ExternalManufactured` tinyint(1) DEFAULT 1 COMMENT 'if =1 No Needs generate My own serial Number',
  PRIMARY KEY (`InventoryId`),
  KEY `Inventory_FK` (`ProductId`),
  CONSTRAINT `Inventory_FK` FOREIGN KEY (`ProductId`) REFERENCES `Products` (`ProductId`)
) ENGINE=InnoDB AUTO_INCREMENT=22 DEFAULT CHARSET=utf8 COMMENT='Device Inventory table'

I would like to insert an automatic value for DeviceSN. This value is the current date and a counter. This counter would be the amount of equipment NOT manufactured externally ( that is, the number of columns whereExternalManufactured=0 )

I can create a helper table with this counter, which is added via a trigger when there is data inserted into the table Inventory. What I don't know is how to concatenate this value (counter) to the current date and make it the default value in DeviceSN.

I've tried with triggers, and with generated columns , but I don't know how to add this counter to be able to concatenate it with the current date...

In fact, with a counter, even if the condition is not met, ExternalManufactured=0it would be enough, but I have tried with an auto-increment column:

ALTER TABLE DeviceManagement.Inventory MODIFY COLUMN DeviceSN varchar(40) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT concat(year(current_timestamp()),month(current_timestamp()),dayofmonth(current_timestamp()),CAST(InventoryId AS CHAR)) NULL;

And I get the error:

Function or expression 'AUTO_INCREMENT' cannot be used in the DEFAULT clause of `InventoryId

Any advice will be welcome...

Thank you very much!

mariadb

2 Answers

  1. When you create the table you can use CURRENT_DATE and CURRENT_TIME:

    CREATE TABLE birthday(
name VARCHAR(20) NOT NULL,
date DATE DEFAULT CURRENT_DATE,
time TIME DEFAULT CURRENT_TIME
);

    And for the field that has the equipment not manufactured externally, I would use another field which I would fill in by previously performing a SELECT query to know how many there are.

    So when you insert or modify a new record you make sure that everything is up to date and I would forget about triggers and additional tables.

    Cheers

    • 0
  2. Best Answer

    I don't remember seeing any documentation or example of custom defaults. At least not with the subqueries you use.

    However, one option you can take is to use a trigger . These triggers are executed when a certain operation is performed on the table.

    In your case, it seems to be inserting into it, so your trigger could have the option BEFORE INSERT(before inserting), and, in it, modify the value to insert:

    CREATE TRIGGER trigger_devicesn BEFORE INSERT ON Inventory
FOR EACH ROW
BEGIN
    SET NEW.DeviceSN = (concat(year(current_timestamp()),
                              month(current_timestamp()),
                              dayofmonth(current_timestamp())));
END;

    After you save this trigger, you can insert values ​​into the table like this:

    INSERT INTO Inventory (ProductId, DeviceSN, ExternalManufactured)
VALUES (1,NULL,1);

    That is, with the values <numero>, NULL, <numero>​​. So, inside the table Inventory, you will see columns of the form:

    > SELECT * FROM Inventory;
InventoryId  ProductId  DeviceSN  ExternalManufactured
23           1          20201116  1
24           2          20201116  2
    • 0