[Oracle/기본]15. SQL 제약조건 별 CREATE TABLE 예제.

@ LIME 이라는 스키마에 작성한 것입니다.

 

1. 아래 테이블의 인스턴스 차트를 기초로 테이블을 생성한다.

 

a. 테이블 이름 : MEMBER (PK, NN 제약조건과 Default값 적용하여 테이블 생성)

Column_Name	MEMBER_ID	LAST_NAME	FIRST_NAME	ADDRESS	CITY	PHONE	JOIN_DATE
Key Type	PK
Null/Unique	NN,U	NN					NN
Default Value							System Date
Data Type	Number	Varchar2	Varchar2	Varchar2	Varchar2	Varchar2	Date
Length	10	25	25	100	30	15

 

CREATE TABLE MEMBER
(
   member_id    NUMBER (10)

                     CONSTRAINT member_member_id_pk PRIMARY KEY,
   last_name    VARCHAR2 (25)

                    CONSTRAINT member_last_name_nn NOT NULL,
   first_name   VARCHAR2 (25),
   address      VARCHAR2 (100),
   city           VARCHAR2 (30),
   phone        VARCHAR2 (15),
   join_date    DATE

                   DEFAULT SYSDATE

                   CONSTRAINT member_join_date_nn NOT NULL
);

 

b. 테이블 이름 : TITLE (CHECK 제약조건이 있는 경우)

Column_Name	TITLE_ID	TITLE	DESCRIPTION	RATING	CATEGORY	RELEASE_DATE
Key Type	PK
Null/Unique	NN,U	NN	NN
Check				G, PG, R, NC17,  NR	DRAMA,    COMEDY,  ACTION,  CHILD,  SCIFI,  DOCUMEN,  TARY
Data Type	Number	Varchar2	Varchar2	Varchar2	Varchar2	Date
Length	10	60	400	4	20

 

CREATE TABLE title
(
   title_id       NUMBER (10) CONSTRAINT title_title_id_pk PRIMARY KEY,
   title          VARCHAR2 (60) CONSTRAINT title_title_nn NOT NULL,
   description    VARCHAR2 (400) CONSTRAINT title_description_nn NOT NULL,
   rating         VARCHAR2 (4)
                     CONSTRAINT title_rating_ck CHECK
                        (rating IN ('G',
                                    'PG',
                                    'R',
                                    'NC17',
                                    'NR')),
   category       VARCHAR2 (20)
                     CONSTRAINT title_category_ck CHECK
                        (category IN ('DRAMA',
                                      'COMEDY',
                                      'ACTION',
                                      'CHILD',
                                      'SCIFI',
                                      'DOCUMEN',
                                      'TARY')),
   release_date   DATE
)

 

c. 테이블 이름 : TITLE_COPY (기본키 2개 있는경우/ 외래키 1개 있는경우.)

 

Column_Name	COPY_ID	TITLE_ID	STATUS
Key Type	PK	PK,FK
Null/Unique	NN,U	NN,U	NN
Check			AVAILABLE,  DSTROYED,  RENTED,  RESERVED
Data Type	Number	Number	Varchar2
Length	10	10	15

 

CREATE TABLE title_copy
(
 copy_id  NUMBER (10)
 -- title 테이블에 대한 외래키이다. CREATE에서 제약조건 추가시에는
 -- FOREIGN KEY(자식테이블컬럼명)이 생략된다.
,title_id NUMBER(10)
          CONSTRAINT title_copy_title_if_fk REFERENCES title(title_id)
,status   VARCHAR2(15)
          CONSTRAINT title_copy_status_nn NOT NULL
          CONSTRAINT title_copy_stats_ck CHECK
          (status IN ('AVAILABLE', 'DESTROYED', 'RENTED', 'RESERVED'))

  --PK가 2개이상이면 하나의 CONSTRAINT로 작성.
,CONSTRAINT title_copy_copy_id_title_id_pk PRIMARY KEY (copy_id, title_id)
)

 

d. 테이블 이름 : RENTAL (FK가 3개, 2개가 각각 다른 컬럼에 해당하는 경우.)

 

Column_Name	BOOK_DATE	MEMBER_ID	COPY_ID	ACT_RET_DATE	EXP_RET_DATE	TITLE_ID
Key Type	PK	PK,FK2	PK,FK			PK,FK2
Default Value	System Date				2 days after   book  date
FK Ref Table		member	title_copy			title_copy
FK Ref Col		member_id	copy_id			title_id
Data Type	Date	Number	Number	Date	Date	Number
Length		10	10			10

 

CREATE TABLE rental
(
  book_date    DATE DEFAULT SYSDATE
 ,member_id    NUMBER(10)
               CONSTRAINT rental_member_id_fk
               REFERENCES member(member_id)
 ,copy_id      NUMBER(10)
 ,act_ret_date DATE
 ,exp_ret_date DATE DEFAULT SYSDATE + 2
 ,title_id     NUMBER(10)
 ,             CONSTRAINT rental_book_date_copy_title_pk
               PRIMARY KEY(book_date, member_id, copy_id, title_id)
 ,             CONSTRAINT rental_copy_id_title_id_fk
               FOREIGN KEY(copy_id, title_id)
               REFERENCES title_copy(copy_id, title_id)
);

 

e. 테이블 이름 : RESERVATION (다른 테이블을 참조하는 외래키가 각각 1개씩 있을 때,)

 

Column_Name	RES_DATE	MEMBER_ID	TITLE_ID
Key Type	PK	PK,FK1	PK,FK2
Null/Unique	NN,U	NN,U	NN
FK Ref Table		MEMBER	TITLE
FK Ref Col		member_id	title_id
Data Type	Date	Number	Number
Length		10	10

 

CREATE TABLE reservation
(
  res_date DATE
 ,member_id NUMBER(10)
            CONSTRAINT reservation_member_id_fk
            REFERENCES member(member_id)
 ,title_id  NUMBER(10)
            CONSTRAINT reservation_title_fk
            REFERENCES title(title_id)
 ,CONSTRAINT reservation_res_mem_tit_pk PRIMARY KEY(res_date, member_id, title_id)
)

 

2. 데이터 사전을 확인하여 테이블과 제약조건이 적정하게 생성되었는지를 검증한다.

 

    SELECT table_name

      FROM user_tables

    WHERE table_name IN ('MEMBER', 'TITLE', 'TITLE_COPY', 'RENTAL', 'RESERVATION');

 

3. MEMBER 테이블과 TITLE 테이블에서 각 행을 고유하게 식별하기 위한 시퀀스를 생성한다.

 

a. MEMBER 테이블에 대한 회원 번호 : 101로 시작한다. 값의 캐쉬를 허용하지 않는다. member_id_seq로 이름을 지정한다.

 

   CREATE SEQUENCE member_id_seq

   START WITH 101

   NOCACHE;

 

b. TITLE 테이블에 대한 타이틀 번호:92로 시작한다. 캐쉬 없다.

  

    CREATE SEQUENCE title_id_seq

    STAR WITH 92

    NOCACHE;

 

c. 데이터 사전에서 시퀀스를 검증한다.

 

    SELECT sequence_name, increment_by, last_number

      FROM user_sequences

     WHERE sequence_nme IN ('MEMBER_ID_SEQ', 'TITLE_ID_SEQ');