CODEWITHSUNDEEP
mysqlsql
melly307
melly307

Reputation: 11

Can you create a Primary Key Value that uses data from other columns?

I am new to MySQL and databases. My database is a movie theater management. One of my tables, SALE, has a PK of sale_id. The value stored in sale_id is VARCHAR(19) which is a combination of the columns customer_id(FK - VARCHAR(7)) and sale_datetime(DATETIME). Is it possible to create a primary key value in MySQL workbench that is based off other columns in the table?

The SALE table is created as such:

CREATE TABLE `sale` (
  `sale_id` varchar(19) NOT NULL,
  `customer_id` varchar(7) NOT NULL,
  `sale_datetime` datetime NOT NULL,
  `payment_type` varchar(4) NOT NULL,
  `ticket_quantity` int NOT NULL,
  `sale_total` decimal(5,2) NOT NULL,
  PRIMARY KEY (`sale_id`),
  KEY `fk_sale_customer_id_idx` (`customer_id`),
  CONSTRAINT `fk_sale_customer_id` FOREIGN KEY (`customer_id`) REFERENCES `customer`(`customer_id`))

Is there some "constraint" that I can set that will automatically create the sale_id based off the customer_id and sale_datetime when adding data to this table? For example: If I'm adding a row to this table where my customer_id is 1111111 and sale_date is 2021-08-13 12:00:00 the sale_id will automatically be set to 1111111202108131200?

Upvotes: 0

Views: 446

Answers (1)

lefred
lefred

Reputation: 129

Having a primary key as a VARCHAR like you do is not recommended for MySQL's storage engine: InnoDB.

For more info check this: https://lefred.be/content/mysql-invisible-column-part-ii/

I would suggest to have an auto increment PK as sale_id. What would be the benefit of that non incremental PK ? I would also suggest the application to generate that field, the one with the customer and the parsed date, and you could index (Unique) it too. That field could be of course generated by a trigger too is you prefer, see https://dev.mysql.com/doc/refman/8.0/en/trigger-syntax.html.

Upvotes: 1

Related Questions