Generated Column Example

generated_column.sql

-- master table
create table transactions (
    id int auto_increment primary key,
    code varchar(60) not null unique,
    date integer not null,
    value decimal(15,2) default 0,
    remarks text
);

-- details table without generated column
create table transaction_details1 (
    id int auto_increment primary key,
    transaction_id int not null,
    item_id int not null,
    quantity decimal(15,2) default 0,
    unit_price decimal (15,2) default 0,
    remarks text,
    constraint fk_transaction_details1_transaction
        foreign key (transaction_id) references transactions (id)
            on update cascade on delete cascade
);

-- details table with generated column
create table transaction_details2 (
    id int auto_increment primary key,
    transaction_id int not null,
    item_id int not null,
    quantity decimal(15,2) default 0,
    unit_price decimal (15,2) default 0,
    amount decimal(15,2) generated always as (quantity*unit_price),
    remarks text,
    constraint fk_transaction_details2_transaction
        foreign key (transaction_id) references transactions (id)
            on update cascade on delete cascade
);

-- sample data for master table
insert into transactions(id, code, date) values (1, 'PO-01/2020', unix_timestamp('2020-05-01'));
insert into transactions(id, code, date) values (2, 'PO-02/2020', unix_timestamp('2020-05-05'));
insert into transactions(id, code, date) values (3, 'PO-03/2020', unix_timestamp('2020-05-08'));

-- sample data for details1 table
insert into transaction_details1(transaction_id, item_id, quantity, unit_price) values (1, 0, 15, 10000);
insert into transaction_details1(transaction_id, item_id, quantity, unit_price) values (1, 0, 25, 10000);
insert into transaction_details1(transaction_id, item_id, quantity, unit_price) values (2, 0, 35, 10000);
insert into transaction_details1(transaction_id, item_id, quantity, unit_price) values (3, 0, 45, 10000);
insert into transaction_details1(transaction_id, item_id, quantity, unit_price) values (3, 0, 55, 10000);

-- sample data for details2 table
insert into transaction_details2(transaction_id, item_id, quantity, unit_price) values (1, 0, 15, 10000);
insert into transaction_details2(transaction_id, item_id, quantity, unit_price) values (1, 0, 25, 10000);
insert into transaction_details2(transaction_id, item_id, quantity, unit_price) values (2, 0, 35, 10000);
insert into transaction_details2(transaction_id, item_id, quantity, unit_price) values (3, 0, 45, 10000);
insert into transaction_details2(transaction_id, item_id, quantity, unit_price) values (3, 0, 55, 10000);

-- no amount
select *
from transaction_details1;

-- calculate amount
select *
     , quantity*unit_price as amount
from transaction_details1;

-- amount calculated already
select *
from transaction_details2;