CREATE_SQL.sql

#사용자     : USERS
##ROWID     : ID
##사용자ID  : USERID
##성명      : NAME

CREATE TABLE USERS(
  ID bigint PRIMARY KEY AUTO_INCREMENT,
  USERID VARCHAR(20) NOT NULL, 
  NAME VARCHAR(100) NOT NULL,
  CONSTRAINT UNIQUE INDEX IDX_USERS_USERID (USERID)
);

#계좌       : ACCOUNTS
##ROWID     : ID
##사용자FK  : USERS(ID)
##계좌번호  : ACCOUNT_NUMBER
##잔액      : AMOUNT
CREATE TABLE ACCOUNTS(
  ID bigint PRIMARY KEY AUTO_INCREMENT,
  USERS_ID BIGINT,
  ACCOUNT_NUMBER VARCHAR(20) NOT NULL,
  AMOUNT INT DEFAULT 0,
  CONSTRAINT FOREIGN KEY FK_ACCOUNTS_USERS_ID (USERS_ID) 
             REFERENCES USERS(ID)
);

#계좌상세   : ACCOUNT_DETAILS
##ROWID     : ID
##계좌FK    : ACCOUNTS(ID)
##입/출/계  : TYPE
##금액      : AMOUNT
CREATE TABLE ACCOUNT_DETAILS(
  ID bigint PRIMARY KEY AUTO_INCREMENT,
  ACCOUNTS_ID BIGINT,
  TYPE TINYINT NOT NULL,
  AMOUNT INT DEFAULT 0,
  CONSTRAINT FOREIGN KEY FK_ACCOUNTS_ACCOUNTS_ID (ACCOUNTS_ID) 
             REFERENCES ACCOUNTS(ID)
);

INSERT INTO USERS(USERID, NAME) VALUES('boojongmin', '부종민');
select * from users;
#계좌번호 생성규칙
#10자리 (왼쪽 5 000000 - USERS_ID, 오른쪽5 000000 - 생성순서)

INSERT INTO ACCOUNTS(USERS_ID, ACCOUNT_NUMBER) VALUES(1, '0000100001');
;
#TYPE : 1(입금), 2(출금), 3(계좌이체)
INSERT INTO ACCOUNT_DETAILS(ACCOUNTS_ID, TYPE, AMOUNT)
VALUES(1, 1, 1000000);
;
SELECT * FROM ACCOUNT_DETAILS
;