CREATE TABLE member
(
    mnum   NUMBER(3)    CONSTRAINT member_mnum_pk   PRIMARY KEY,
    id    VARCHAR2(25)  CONSTRAINT member_id_uk     UNIQUE,
    pw    VARCHAR2(25)  CONSTRAINT member_pw_nn     NOT NULL,
    name  VARCHAR2(25)  CONSTRAINT member_name_nn   NOT NULL,
    state NUMBER(1)     CONSTRAINT member_state_ck  CHECK(state in(0,1))
);

create sequence member_no start with 1 increment by 1;

create table reservation
(
    mnum           NUMBER(3) CONSTRAINT  reservation_mnum_fk REFERENCES member(mnum) ON DELETE CASCADE ,
    start_date     date     ,
    end_date       date    ,
    tnum           number(3) CONSTRAINT  reservation_tnum_fk REFERENCES travel(tnum) ON DELETE CASCADE
);

drop table reservation;

create table member_grade
(
    mnum     number(3) ,
    mpoint   number(10) ,
    grade    varchar2(25)
);

alter table member_grade
add constraint member_grade_fk foreign key(mnum) references member(mnum);

-- 여행 테이블
create table travel
(
    tnum        number(3)       constraint travel_tnum_pk   primary key,
    t_name      varchar(25)     constraint travel_t_name_nn not null ,
    t_location  varchar(25)     constraint travel_t_location_nn not null,
    t_price     number(10)      constraint travel_t_price_nn not null     
);

-- 여행 일정 테이블
create table travel_plan
(
    tnum    number(3) constraint travel_plan_tnum_fk REFERENCES travel(tnum),
    tplan   date
);

-- 테마 테이블
create table theme
(
    thm_num number(3)   constraint theme_thm_num_pk primary key,
    thm_name varchar(25)
);

-- 테마의 여행지 테이블
create table theme_travel
(
    thm_num number(3)   constraint theme_travel_thm_num_fk references theme(thm_num),
    tnum    number(3)   constraint theme_travel_tnum_fk     references travel(tnum)
);

이 글과 관련있는 글을 자동검색한 결과입니다 [?]

• Oracle PK 변경하기 by 방마루
• table UPDATE with ANALYTIC function by kang
• SQL 기본 구문 형식 by CHOCOLATE
• ModelRight 3 단축키 by 해피포터
• Oracle - ALTER TABLE by overdose