--         File: type_safety_1.sql
--      Version: 1.1
-- Last Changed: 2017-02-06
--           by: Damir;  https://www.damirsystems.com
--      Project: Types and Typos in SQL
--  Description: Part 1
--           DB: Generic, PostgreSQL


-- users
create table users (
      id        integer      not null
    , user_name varchar(64)  not null

    , constraint pk_users primary key (id)
    , constraint ak_users unique (user_name)
) ;


-- transactions
create table trans (
      id       integer       not null
    , amt      decimal(19,2) not null
    , cur      char(3)       not null
    , tx_time  timestamp     not null
    , user_id  integer       not null

    , constraint pk_trans primary key (id)

    , constraint fk_trans foreign key (user_id)
                     references users (id)
) ;


-- add few users
insert into users (id, user_name)
values (1, 'john')
     , (2, 'jane')
     , (3, 'jack') ;


-- add few transactions
insert into trans (id, amt, cur, tx_time, user_id)
values (1, 10.0, 'USD', '2017-01-01 10:00', 1)
     , (2, 15.0, 'CAD', '2017-01-01 11:15', 2)
     , (3, 20.0, 'USD', '2017-01-01 12:00', 1) ;