Skip to content

Instantly share code, notes, and snippets.

@ngekoding
Forked from hidayat365/generated_column.sql
Created May 9, 2020 13:10
Show Gist options
  • Save ngekoding/c386681216426d118b90b93894547d91 to your computer and use it in GitHub Desktop.
Save ngekoding/c386681216426d118b90b93894547d91 to your computer and use it in GitHub Desktop.
Generated Column Example
-- 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;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment