전체 DB구조 / 회원테이블 DB 생성

고윤박커피의 전체 DB설계는 이렇습니다.

기본적인 회원가입 테이블은 이렇게 생성했습니다.

CREATE TABLE member(
                       memberNum NUMBER(6),
                       memberId VARCHAR2(50),
                       memberPw VARCHAR2(1000) NOT NULL,
                       memberName VARCHAR2(20),
                       memberBday DATE not null,
                       memberAddress VARCHAR2(300),
                       memberTel VARCHAR2(20),
                       memberPhone VARCHAR2(20) NOT NULL,
                       memberEmail VARCHAR2(200),
                       memberEmailYn NUMBER(1),
                       memberMileage NUMBER(6) DEFAULT 0,
                       CONSTRAINT memberNum_pk primary key (memberNum),
                       CONSTRAINT memberId_uq UNIQUE(memberId),
                       CONSTRAINT memberEmail_uq UNIQUE(memberEmail)
                       );

CREATE TABLE member_email(
    memberEmail VARCHAR2(200),
    memberEmailYn NUMBER(1),
    CONSTRAINT member_email FOREIGN KEY(memberEmail)
    REFERENCES member(memberEmail)
    on delete cascade);

CREATE TABLE member_info(
                            memberNum NUMBER(6),
                            memberType NUMBER(5) not null,
                            CONSTRAINT memberInfo_pk FOREIGN KEY(memberNum)
                                REFERENCES member(memberNum)
                                on delete cascade);

"on delete cascade"는 멤버테이블에서 데이터를 삭제하면 자동으로 member_info의 데이터도 같이 삭제하기위해 넣었습니다.

CREATE SEQUENCE member_seq
NOCACHE;

자동으로 번호부여를위해 시퀀스를 생성하고,

insert into member values (member_seq.nextval,'admin','admin','관리자','2022/01/01','서울시 강남구 도곡동','02-123-456','010-1111-1111',
                           'admin@koyunpark.com','1','10000','0');
insert into member values (member_seq.nextval,'yunparkko','koyunpark1234','윤박고','1990/10/03','서울시 강남구 청담동','02-123-456','010-1111-1111',
                           'yunparkko@gmail.com','1','10000','1');

이렇게 관리자와 테스트용 데이터를 넣었습니다.