Home / user-201590

Jordi NC's questions

Martin Hope
Jordi NC
Asked: 2021-11-17 06:49:27 +0800 CST
  • 1

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