[ORACLE] 테이블 생성과 관리를 위한 DDL 작성 2

in Study on Oracle
[ORACLE] 테이블 생성과 관리를 위한 DDL 작성 2

테이블 생성과 관리를 위한 DDL 작성 2

제약조건 (Constraints)

  • 제약조건은 테이블 레벨에서 규칙을 강제 적용
  • 제약조건은 테이블에 종속 관계가 있는 경우 삭제를 방지
  • 결론적으로 제약조건은 부적합한(규칙에 어긋난) 데이터의 입력, 수정, 삭제를 방지하기 위한 것이고, 이것은 업무적 규칙 설정을 의미한다
  • 바로 데이터 무결성을 보장하기 위한 것이다
  • 유효한 제약 조건 유형은 다음과 같다
    • NOT NULL
    • UNIQUE
    • PRIMARY KEY
    • FOREIGN KEY
    • CHECK

제약조건 가이드

  • 유저가 제약 조건의 이름을 지정하거나 Oracle 서버가 SYS_Cn 형식을 사용하여 이름을 생성할 수 있다
  • 다음 시점 중 하나에서 제약 조건을 생성:
    • At the same time as the creation of the table
    • After the creation of the table
  • 열 또는 테이블 레벨에서 제약 조건을 정의
  • 데이터 딕셔너리에서 제약 조건을 확인
SELECT. *
  FROM. user_constraints
 WHERE. table_name = 'EMPLOYEES';
 OWNERCONSTRAINT_NAME…
1HR2EMP DEPT FK…
2HR2JOB FK…
3HR2MANAGER FK…

위 쿼리문을 통해 ‘EMPLOYEES’ 테이블의 유저 제약조건을 확인할 수 있다

  FROM. user_cons_columns

FROM 절에 위 구문을 대신 넣으면 어떤 칼럼에 제약조건이 붙어있는지 확인할 수 있다.

제약조건 정의

  • 문법
CREATE  TABLE [schema.]table
        (column datatype [DEFAULT expr]
        [column_constraint], -- 칼럼 레벨 제약조건
        ...
        [table_constraint][,...]); -- 테이블 레벨 제약조건
  • 칼럼 레벨 제약조건 :
column [CONSTRAINT constraint_name] constraint_type,
  • 테이블 레벨 제약조건 :
column,...
   [CONSTRAINT constraint_name] constraint_type
   (column, ...),
  • 칼럼 레벨 제약조건 예 :
CREATE. TABLE employees(
   employee_id NUMBER(6) CONSTRAINT emp_emp_id_pk PRIMARY KEY,
   frist_name  VARCHAR2(20),
   ...);

-- 'emp_emp_id_pk'는 제약조건의 이름이다

PRIMARY KEY 제약조건은 기본적으로 다음의 제약조건을 지닌다

  1. table 당 1개
  2. NOT NULL
  3. UNIQUE
  4. 다른 table의 FK와 관계 설정 가능
  5. 해당 열에 자동 unique index 생성
    • 테이블 레벨 제약조건 예 :
CREATE. TABLE employees(
   employee_id  NUMBER(6),
   frist_name.  VARCHAR2(20),
   ...
   job_id.      VARCHAR2(10) NOT NULL,
   CONSTRAINT emp_emp_id_pk PRIMARY KEY (EMPLOYEE_ID));

-- PRIMARY KEY 옆에 해당 제약조건을 지닐 칼럼 명(EMPLOYEE_ID)가 온다.

NOT NULL 제약조건

  1. 열에 NULL 값이 허용되지 않도록 보장.
  2. 테이블 레벨에서는 설정이 불가능하고, 칼럼 레벨에서만 설정 가능하다.

UNIQUE 제약조건

  1. 중복을 배제한다.

  2. PRIMARY KEY 와 마찬가지로 자동 unique index가 생성된다.

    PRIMARY KEY 와 같이 생성되는게 아니고 UNIQUE 제약조건이 있기 떄문에 PRIMARY KEY 에도 자동으로 생성되는 것 같다.

  3. NULL 값을 허용한다.

    NULL 값은 알 수 없는 값이기 때문에, 중복되지 않는 값이다.

    따라서 만약에 해당 칼럼에 NULL 을 배제하고 싶다면, NOT NULL 을 선언해줘야 한다.

  4. 테이블 레벨 또는 열 레벨에서 정의
CREATE  TABLE employees(
   employee_id.    NUMBER(6);
   last_name.      VARCHAR2(25) NOT NULL,
   email           VARCHAR2(25),
   salary          NUMBER(8, 2),
   commission_pct  NUMBER(2, 2),
   hire_date       DATE NOT NULL,
...
   CONSTRAINT emp_email_uk UNIQUE(email));

PRIMARY KEY 제약조건

보통 첫 번째 칼럼을 PRIMARY KEY 로 많이 사용한다.

관계 설정이 주 목표이다.

다른 테이블의 FOREIGN KEY 와 관계를 맺을 수 있다.

PK 는 부모, FK 는 자식의 관계라고 할 수 있다.

그 특징은 다음과 같다.

  • 부모(PK)는 자식이 사용중인 데이터는 삭제할 수 없고,
  • 자식(FK)은 부모가 보유한 데이터 or NULL 값만 보유할 수 있다.
DELETE  FROM. departments WHERE. department_id=90;

위 쿼리를 실행하면 다음과 같은 결과가 출력된다.

ORA-02292: 무결성 제약조건(HR2.EMP_DEPT_FK)이 위배되었습니다 - 자식 레코드가 발견되었습니다.

즉 1번의 특징에 위배되는 요청이기 때문에 거부되었음을 알 수 있다.

UPDATE. employees
   SET. department_id = 99
 WHERE. employee_id = 200;

위 쿼리를 실행하면 다음과 같은 결과가 출력된다.

ORA-02291: 무결성 제약조건(HR2.EMP_DEPT_FK)이 위배되었습니다 - 부모 키가 없습니다.

99번이라는 부서 번호는 존재하지 않는다.

즉 2번의 특징에 위배되는 요청이기 때문에 마찬가지로 거부되었음을 알 수 있다.

FOREIGN KEY 제약조건

테이블 또는 열 레벨에서 정의

CREATE  TABLE employees( 
   employee_id  NUMBER(6),
   last_name    VARCHAR2(25) NOT NULL,
   email.       VARCHAR2(25),
   salary       NUMBER(8, 2),
   commission_pct  NUMBER(2, 2),
   hire_date       DATE NOT NULL,
...
   department_id.  NUMBER(4),
   CONSTRAINT emp_dept_fk FOREIGN KEY (department_id)
      REFERENCES departments(department_id), 
   CONSTRAINT emp_email_uk UNIQUE(email));

-- department_id가 departments 테이블의 PK임이 사전설정 되어있어야 한다.

추가 기능

  • FOREIGN KEY
    • 테이블 제약 조건 레벨에서 하위 테이블의 열의 정의
  • REFERENCES
    • 테이블 및 상위 테이블의 열을 식별
  • ON DELETE CASCADE
    • 상위 테이블의 행이 삭제될 때 하위 테이블의 종속 행을 삭제
  • ON DELETE SET NULL
    • 종속 Foreign key 값을 null로 변환

앞서 PK 의 설명에서 “부모(PK)는 자식이 사용중인 데이터는 삭제할 수 없고” 라고 했는데, 위 옵션을 통해서 가능하게 할 수 있다.

ON DELETE CASCADE 를 사용할 경우, 부모 테이블에서 행을 삭제할 때 해당 행에 연관된 자식 테이블의 종속 행이 함께 삭제된다.

ON DELETE SET NULL 을 사용할 경우, 자식 테이블의 연관된 행이 NULL 값으로 바뀐다.

ON DELETE 절

  • 상위 키가 삭제될 때 하위 행을 삭제하려면 ON DELETE CASCADE 절을 사용 :
 ALTER  TABLE emp2 CONSTRAINT emp_dt_fk FOREIGN KEY (department_id)
        REFERENCES departments(department_id) ON DELETE CASCADE;
  • 상위 키가 삭제될 때 하위 행을 NULL로 설정하려면 ON DELETE SET NULL 절을 사용 :
 ALTER  TABLE emp2 CONSTRAINT emp_dt_fk FOREIGN KEY (department_id)
        REFERENCES departments(department_id) ON DELETE SET NULL;

CHECK 제약조건

  • 각 행이 충족해야 하는 조건을 정의
  • 다음 표현식은 허용되지 않는다 :
    • CURRVAL, NEXTVAL, LEVEL, ROWNUM 의사 열에 대한 참조
    • SYSDATE, UID, USER, USERNV 함수에 대한 호출
    • 다른 행의 다른 값을 참조하는 query
..., salary  NUMBER(2)
     CONSTRAINT emp_salary_min
        CHECK (salary > 0), ...

우리가 아는 WHERE 절에 들어갈 수 있는 조건은 모두 CHECK 의 조건으로 들어갈 수 있다.

제약조건 위반 사례

UPDATE. employees
   SET. department_id = 55
 WHERE. department_id = 110;

부모 테이블에 55번이라는 부서번호가 없어서 위반

DELETE. FROM departments
 WHERE. department_id = 60;

자식 테이블에서 해당 부서번호 60번이라는 값을 참조하고 있기 때문에 제약조건 위반

제약조건 추가

  • ALTER TABLE 구문을 통해 추가 가능
    • 제약 조건 추가 또는 삭제, 제약 조건의 구조는 수정하지 않음
    • 제약 조건 활성화 또는 비활성화
    • MODIFY 절을 사용하여 NOT NULL 제약 조건 추가
ALTER. TABLE <table_name>
  ADD. [CONSTRAINT <constraint_name>]
 type  (<conlumn_name>);

특정 제약조건 자체를 수정할 수는 없다.

제약조건을 수정하고 싶다면 해당 제약조건을 삭제하고 추가하도록 하자.

EMP2 테이블에 FORIEGN KEY 제약 조건을 추가하면 관리자가 이미 EMP2 테이블에 유효한 사원으로 존재해야 함을 나타낸다.

 ALTER. TABLE emp2
MODIFY. employee_id PRIMARY KEY;

ALTER TABLE emp2 succeded.

ALTER  TABLE emp2
  ADD. CONSTRAINT emp_mgr_fk
       FOREIGN KEY(manager_id)
       REFERENCES.emp2(employee_id);

ALTER TABLE succeeded.

제약조건 삭제

  • EMP2 테이블에서 관리자 제약 조건을 제거
ALTER  TABLE emp2
 DROP. CONSTRAINT emp_mgr_fk;
  • DEPT2 테이블에서 PRIMARY KEY 제약 조건을 제거하고 EMP2.DEPARTMENT_ID 열에서 연관된 FOREIGN KEY 제약조건을 삭제 :
ALTER  TABLE dept2
 DROP. PRIMARY KEY CASCADE;

제약조건 비활성화

  • ALTER TABLE 문의 DISABLE 절을 실행하여 무결성 제약 조건을 비활성화
  • CASCADE 옵션을 적용하여 종속 무결성 제약 조건을 비활성화 할 수 있음
ALTER. TABLE emp2
DISABLE.CONSTRAINT emp_dt_fk;

제약조건 비활성화는 해당 제약조건을 일시적으로 무효화하기 위해서 사용한다.

제약조건 활성화

  • ENABLE 절을 사용하여 테이블 정의에서 현재 비활성화된 무결성 제약조건을 활성화 할 수 있음
ALTER. TABLE emp2
ENABLE CONSTRAINT emp_dt_fk;
  • UNIQUE KEY 또는 PRIMARY KEY 제약조건을 활성화하면 UNIQUE 인덱스가 자동으로 생성

읽기 전용 테이블

ALTER TABLE 명령을 통해 :

  • 테이블을 읽기 전용 모드로 설정하여 테이블을 유지 관리하는 동안 DDL 문 또는 DML 문에 의한 변경을 방지
  • 테이블을 다시 읽기/쓰기 모드로 설정
ALTER. TABLE employees READ ONLY; --읽기 전용
ALTER. TABLE employees READ WRITE; -- 읽기/쓰기 모드

© 2022. All rights reserved. 신동민의 블로그