본문 바로가기

.주제별/SQL,DB

SQL)강의- 제 9 장 테이블 생성

출처 : http://naver.kaist.ac.kr/oracle_sql/s09.htm

1. 테이블 생성

    데이터 구조

    구   조

    설    명

    Table

    데이터 저장

    View

    하나 이상의 테이블에 있는 데이터 부분집합에 대한 논리적인 표현

    Index

    일부 Query에 대한 성능 향상을 위한 색인 파일

    Sequence

    Primary Key 를 일정한 값으로 생성

     

Oracle 테이블 구조의 요약

    ◈데이터베이스 설계단계에서 구조를 정의한다.
    ◈어떤 시점에서도 작성할 수 있다.
    ◈테이블의 크기를 명시할 필요가 없다.  
       크기는 데이터베이스에 할당된 공간 전체로 한정된다.
       하지만 얼마만한 공간을 테이블이 사용할 것인지 예측하는 것이 중요하다.
    ◈테이블 구조는 수정할 수 있다.

      CREATE  TABLE  [스키마.] 테이블

                ({column_name datatype[DEFAULT 식]  [column [제약조건],

                 ….

                 [테이블 제약조건]);
       

    테이블명과 column명 정의에 대한 Naming Rule

        ① 1문자이상 30문자이하의 길이를 가지며 첫번째 문자는 영문자이어야 한다
        ② 이름은 A-z, a-z, 0-9, _, $, #들로만 구성된 문자를 사용할수 있다
        ③ 테이블명은 한 USER에 같은 Object가 있어서는 안된다
        ④ 이름은 대문자와 소문자가 같은 문자로 취급된다(데이터는 구분함)
     

    스키마.테이블(다른 사용자의 테이블 참조)

        - 소유자의 이름(user name)으로써, 데이터베이스의 논리적 구조이다. 
        - 제약조건에서 참조되는 테이블은 동일한 데이터베이스에 있어야 한다.
        - 참조되는 테이블이 제약조건을 만드는 user name이 아니면 user name이 제약조건에서
           참조되는 테이블 이름 앞에 붙여져야 한다.

     

 다른 사용자의 테이블 참조

    ◈스키마는 객체의 집합이다.
    ◈스키마 데이터베이스의 논리적 구조이다.
    ◈스키마 객체에는 테이블, 뷰, synonym, stored procedure, 인덱스, 클러스터,
        데이터베이스 링크 등으로 구성된다.
    ◈제약조건에서 참조되는 테이블은 동일한 데이터베이스에 있어야 한다.
    ◈참조되는 테이블이 제약조건을 만드는 사용자의 소유가 아니라면 소유자의 이름(스키마)이
       제약조건에서 참조되는 테이블 이름 앞에 붙어져야 한다.
     

DEFAULT 옵션

    ◈insert에 사용될 COLUMN에 대한 Default 값을 명시한다.

      CREATE TABLE s_TestTable  start_date DATE DEFAULT SYSDATE, …

    ◈ default값으로는 리터럴, 식, 또는 SYSDATE와 USER와 같은 SQL함수가 될 수 있다.
    ◈ 다른 column명, 또는 NEXTVAL이나 CURRVAL과 같은 pseudo column을 가질 수 없다.
    ◈ default값은 반드시 column의 datatype과 일치해야 한다.
     

명명법(Naming Rule)

    Oracle7 데이터 객체의 표준 명명법에 따라 데이터베이스 테이블과 column명을 짓는다.
    ◈테이블명과 column명은 반드시 문자로 시작하고 최대 30자까지만 허용된다.
    ◈이름은 문자 A-Z, a-z, 0-9, _, $, #만으로 이루어져야 한다.
    ◈동일한 사용자가 소유한 다른 객체의 이름과 중복되지 않도록 해야 한다.
    ◈ Oracle7 Server의 Reserved word는 쓸 수 없다.

     

Guidelines

    ◈테이블과 다른 데이터베이스 객체를 의미할 수 있는 적절한 이름을 사용한다.
    ◈엔티티에 대해서는 다른 테이블과 일관성 있는 이름을 사용한다.
    ※ Note : 이름은 대소문자를 구분하지 않는다.
                 예를 들어, EMP는 eMP나 eMp와 동일한 것으로 취급된다.
     

오라클7 데이터 타입

    Datatype

    설              명

    VARCHAR2(s)

    1에서 2000까지의 길이를 가지는 가변길이 문자형태(s : 최대길이임.)

    CHAR(s)

    1에서 255까지의 길이를 가지는 고정길이 문자형태(s : total size 표시)

    NUMBER(p,s)

    1에서 38까지의 총길이와 소수점이하의 자리수를 표현하는 숫자형태(p는 총길이, s는 소수점이하의 길이를 표시)

    DATE

    일자와 시간을 표시할수있는 형태

    LONG

    2GB까지의 가변길이 문자값으로 테이블당 한 개의 LONG column만 허용

    RAW와 LONG RAW

    각각 VARCHAR2, LONG과 같지만 2진 데이터를 저장하는데 사용된다.

    ※주 : Column의 size는 column 값에 대한 최대 문자수를 결정한다.
             VARCHAR2는 column에 대한 크기를 지정해야 한다.
             NUMBER와 CHAR column도 크기를 지정할 수 있는데 Default값을 쓸 수 있다.

     

2. 제약조건(constraints)

    ◈테이블에 대한 행 추가, 갱신, 삭제 때마다 제약조건을 사용하고자 할 때
    ◈다른 테이블에 의해 참조되어지고 있는 경우 테이블 삭제 방지를 위해
    ◈모든 제약조건은 Data Dictionary에 저장된다.
    ◈제약조건의 이름을 생략했다면 Oracle Server는 SYS_Cn형식으로된 이름을 만들어낸다.
    ◈제약조건은 보통 테이블을 만들 때마다 함께 만든다.
    ◈제약조건은 만든 후에 테이블에 부가되고 또한 임시적으로 사용할 수 없게 할 수도 있다.
     

제약조건 정의

    ◈Column 레벨의 제약조건

        : Column별로 정의. 무결성 제약조건의 어떤 유형도 정의 가능하다.

      Column [CONSTRAINT 제약조건이름] 제약조건유형,
       

    ◈테이블 레벨의 제약조건

        : NULL 값을 column에 사용할 수 없게 한다.

      Column,….

        [CONSTRAINT 제약조건이름] 제약조건유형

         (column,…),

         

데이터 무결성 제약조건

    제약 조건

    설             명

    NOT NULL

    Column이 NULL 값을 가질 수 없음을 명시
    column 레벨에서만 정의할 수 있다.
    예> CREATE TABLE  friend…
           phone   VARCHAR2(15)  NOT NULL,…

    UNIQUE

    테이블의 모든행에서 고유값을 가져야만 하는 COLUMN이나 COLUMN의 조합을 명시. column 레벨이나 테이블 레벨에서 정의할 수 있으며, 조합된 경우에는 테이블 레벨에서 정의하여야 한다.
    예>  CREATE TABLE  friend…
         phone   VARCHAR2(15) CONSTRAINT friend_phone_nn UNIQUE,….

    PRIMARY KEY

    테이블의 각 행을 유일하게 식별할 수 있는 COLUMN이나 COLUMN의 조합을 명시한다. 테이블에 대한 Prima key를 생성한다.
    NOT NULL값을 허용하지 않는다.
    column 레벨이나 테이블 레벨에서 정의할 수 있으며, 조합된 경우에는 테이블 레벨에서 정의하여야 한다.
    예>  CREATE TABLE  friend…
         phone VARCHAR2(15)

    CONSTRAINT friend_phone_pk PRIMARY KEY,….

    REFERENCES

    column과 관련된 테이블의 PRIMARY KEY사이에 FOREIGN KEY 관계를 설정
    예>  CREATE TABLE  friend…
         name  varchar2(10)
    CONSTRAINT friend_name_fk REFERENCES,….

    CHECK

    각 행이 만족해야 하는 조건을 정의
    예>CREATE TABLE  friend…
       code varchar2(2)
    CONSTRAINT code_ck CHECK (code in ('10','20')),… .

     

3. 테이블 챠트를 기초로 한 테이블 생성

    ◈CREATE TABLE 문법을 이용하여 DB 설계의 테이블 챠트를 기초로 테이블을 만든다.
    ◈문서화와 사용상의 편이 때문에 스크립트 파일을 이용하면 작성하는 데에 유용하다.
    1. 스크립트 파일을 작성한다. CREATE TABLE 문법으로 시작하고 테이블 이름을 쓴다.
    2. 테이블 챠트의 column명, datatype, 길이를 스크립트 파일에 기입한다.
    3. column 레벨 NOT NULL 제약조건을 적되 PRIMARY KEY 제약조건 column은 제외한다.
    4. 제약조건이 한 개의 column로 되어 있으면 PRIMARY KEY 제약조건을 column 제약조건으로 한다.
    5. 제약조건이 여러 개의 column으로 되어 있으면 PRIMARY KEY 제약조건을 테이블 제약조건으로 한다.
    6. UNIQUE, CHECK, FOREIGN 제약조건을 쓴다.
    7. 스크립트 파일을 저장하고 실행한다.
     

테이블 생성 : TEST1

    테이블 챠트 : TEST1

    열 이름

    Datatype

    키유형

    NULL

    UNIQUE

    FK테이블

    FK컬럼

    Id

    Number(7)

    PK

    NN

     

     

     

    Name

    Varchar2(25)

     

     

     

     

     

    Userid

    Varchar2(8)

     

    NN

    UK

     

     

    Sdate

    Date

     

     

     

     

     

    Comments

    Varchar2(25)

     

     

     

     

     

    Mgrid

    Number(7)

     

     

     

     

     

    Title

    Varchar2(25)

     

     

     

     

     

    Deptid

    Number(7)

    FK

     

     

    S_DEPT

    ID

    Salary

    Number(11,2)

     

     

     

     

     

    comm

    Number(4,2)

     

     

     

     

     

     

    제약조건의 정의

    제약 조건

    설             명

    TEST1_ID_PK

    ID column을 테이블의 P.K로 설정. 이 제약조건은 고유한 값이 입력되도록 한다

    TEST1_NAME_NN

    테이블의 LAST-NAME column이 널값을 갖지 못하도록 한다.

    TEST1_USERID_NN

    USERID column이 널값을 갖지 못하도록 한다.

    TEST1_USERID_UK

    USERID column이 값이 중복되지 않도록 한다.

    TEST1_DEPTID_FK

    S_DEPT 테이블에 없는 부서 번호가 S_EMP 테이블에 저장되지 않게 한다.

    TEST1_COMM_CK

    Commission_pct column의 허용값을 제한한다.

     

    실습

    SQL> CREATE TABLE test1
          (id       NUMBER(7)    CONSTRAINT test1_id_pk PRIMARY KEY,
           name     VARCHAR2(25) CONSTRAINT test1_name_nn NOT NULL,
           userid   VARCHAR2(8)  CONSTRAINT test1_userid_nn NOT NULL
                                 CONSTRAINT test1_userid_uk UNIQUE,
           sdate    DATE DEFAULT SYSDATE,
           comments VARCHAR2(25),
           mgr_id   NUMBER(7),
           title    VARCHAR2(25),
           deptid   NUMBER(7)    CONSTRAINT test1_deptid_fk REFERENCES s_dept(id),
           salary   NUMBER(11,2),
           comm     NUMBER(4,2)  CONSTRAINT test1_comm_ck CHECK
                                      (comm IN(10,12.5,15,17.5,20))
          ); [Enter]

     

    테이블 생성확인

    ◈SQL*Plus DESCRIBE명령을 이용하면 데이터베이스 테이블이 있는지 확인하고 column정보를
       확인할 수 있다.
     

    실습 : s_test1 테이블이 만들어졌는지 확인하라.

    SQL> DESC(RIBE)  test1

    ◈NOTE : DESCRIBE 명령으로는 NOT NULL제약조건만을 식별할 수 있다. 그 외의 다른 제약조건은
                  Data dictionary에 있다.


    실습 : 위 test1테이블과 동일한 test2 테이블 생성하시오.

    SQL> CREATE TABLE test2 AS
             (SELECT *             /* 1과 2는 같지 않으므로 조건에 맞는
               FROM test1             Row가 하나도 없으므로 구조가 *
               WHERE 1 = 2);           Create된다 */

     

    실습 : 테이블 생성확인

    SQL> DESC(RIBE)  test2