13_ 데이터 무결성을 위한 제약 조건

[ 데이터 무결성을 위한 제약조건 목차 ]

1. 무결성 제약조건의 개념과 종류

   1) 데이터 무결성 제약조건 정의

   2) 무결한 데이터의 5가지 제약조건

   3) 제약조건 확인하기

   4) 제약조건 살피기

2. 필수 입력을 위한 NOT NULL 제약조건

3. 유일한 값만 허용하는 UNIQUE 제약조건

4. 칼럼 레벨로 제약조건명을 명시하여 제약조건 설정하기

5. 데이터 구분을 위한 PRIMARY KEY 제약조건

6. 참조 무결성을 위한 FOREIGN KEY 제약조건

7. CHECK와 DEFAULT의 제약조건

8. 테이블 레벨 방식으로 제약조건 지정하기

9. 제약조건 변경하기

   1) 제약조건 추가

   2) MODIFY로 NOT NULL 제약조건 추가

   3) 제약조건 제거

10. 제약조건의 비활성화와 CASCADE

   1) 제약조건에 의해 작업이 어려운 경우

   2) 제약조건의 비활성화

   3) 제약조건의 활성화

   4) CASCADE 옵션

 

 

 

[ 1 ] 무결성 제약 조건의 개념과 종류

1. 데이터 무결성 제약 조건 정의

- 데이터 무결성 제약 조건 (Data Integrity Constraint Rule)

  : 테이블에 부적절한 자료가 입력되는 것을 방지하기 위해서

    테이블을 생성할 때 각 칼럼에 대해서 정의하는 여러 가지 규칙

 

 

2. 무결한 데이터의 5가지 제약 조건

무결성 제약 조건	역할
NOT NULL	NULL을 허용하지 않는다.
UNIQUE	중복된 값을 허용하지 않는다.  항상 유일한 값을 갖도록 한다.
PRIMARY KEY ( 기본 키 )	NULL을 허용하지 않고 중복된 값을 허용하지 않는다. NOT NULL 조건과 UNIQUE 조건을 결합한 형태
FOREIGN KEY ( 외래 키 )	참조되는 테이블의 컬럼의 값이 존재하면 허용한다.
CHECK	저장 가능한 데이터 값의 범위나 조건을 지정하여 설정한 값만을 허용한다.

 

* PRIMARY KEY (기본 키) 의 용도 : 테이블에서 ROW 행, 데이터 값을 식별하기 위함

* FOREIGN KEY (외래 키) 의 용도 : 다른 테이블의 칼럼 데이터를 참조하기 위함.

  ex) DEPTNO 컬럼이 FOREIGN KEY 제약조건이 되어있다.

 

[ EMP DEPTNO은 DEPT의 DEPTNO의 컬럼값을 참조해서 만들어지는 것,,! EMP의 DEPTNO은 FOREIGN KEY 이라는 것을 의미 ]

 

SELECT * FROM EMP;
DESC EMP; -- EMPNO 컬럼이 NOT NULL 제약조건을 가지고 있다. --> EMPNO 컬럼에는 데이터가 없을 수 가 없다. NULL이 있을 수가 없다.
-- 그리고 EMPNO 컬럼은 NOT NULL 이면서 UNIQUE 한 PRIMARY KEY 제약조건이 있다.

 

 

3. 제약 조건 확인하기

- CONSTRAINT_NAME

  : 제약 조건 명

- CONSTRINT_TYPE

  : 제약 조건 유형을 저장하는 칼럼

 

CONSTRAINT_TYPE	의미
P	PRIMARY KEY
R	FOREIGN KEY
U	UNIQUE
C	CHECK, NOT NULL

 

INSERT INTO DEPT
VALUES(10, 'TEST', 'SEOUL'); 
-- 오류발생: 무결성 제약 조건에 위배됩니다.

DESC DEPT
SELECT * FROM DEPT;

DESC USER_CONSTRAINTS;
SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME
FROM USER_CONSTRAINTS
WHERE TABLE_NAME='DEPT';

-- 아래 쉅 내용 코드 --
-- 3. 제약조건 확인하기
SELECT * FROM DEPT;

INSERT INTO DEPT VALUES(10, 'TEST', 'SEOUL'); -- unique constraint (SCOTT.PK_DEPT) violated 오류발생
-- DEPTNO 의 PRIMARY KEY 제약조건을 위반해서 오류가 발생한것
-- PRIMARY KEY 제약조건이란 NULL 허용 X + 중복된 값 허용 X

DESC DEPT;

DESC USER_CONSTRAINTS; -- 현 계정 SCOTT 계정이 가지고 있는 제약조건들 보여주는 코드

SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME
FROM USER_CONSTRAINTS
WHERE TABLE_NAME='DEPT';
-- 특정 제약조건을 찾기 위한 코드

 

 

[ 현 계정인 SCOTT 계정이 가지고 있는 제약조건들을 보여주는 코드와 그 결과 ]

 

 

[ 테이블 이름이 DEPT이고 그 테이블에 있는 제약조건의 이름과 종류를 보여주는 코드 / 특정 제약조건 보여주는 코드 ]

 

 

 

 

 

4. 제약 조건 살피기

COLUMN CONSTRAINT_TYPE FORMAT A18
SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME 
FROM USER_CONSTRAINTS;
COLUMN OWNER FORMAT A10
COLUMN TABLE_NAME FORMAT A15
COLUMN COLUMN_NAME FORMAT A15
SELECT * 
FROM USER_CONS_COLUMNS;

 

 

 

 

 

2. 필수 입력을 위한 NOT NULL 제약 조건

- NOT NULL 제한 조건은 해당 칼럼에 데이터를 추가하거나 수정할 때

  NULL 값이 저장되지 않게 제약을 걸어주는 것으로서

  사원번호와 사원명과 같이 자료가 꼭 입력되게 하고 싶을 때 사용한다.

 

-- NOT NULL 제약 조건을 설정하지 않고 테이블 생성하기
DROP TABLE EMP01;

CREATE TABLE EMP01(
    EMPNO NUMBER(4),
    ENAME VARCHAR2(10),
    JOB VARCHAR2(9),
    DEPTNO NUMBER(2)
);

SELECT * FROM EMP01;

INSERT INTO EMP01 VALUES(NULL, NULL, 'SALESMAN', 30); -- 데이터 추가가 된다.
-- EMPNO와 ENAME은 값이 무조건 있어야하는데 NOT NULL 제약조건이 없어서 NULL값이 그냥 들어가버림,,


-- NOT NULL 제약조건을 설정하여 테이블 생성하기
DROP TABLE EMP02;

CREATE TABLE EMP02(
    EMPNO NUMBER(4) NOT NULL,
    ENAME VARCHAR2(10) NOT NULL,
    JOB VARCHAR2(9),
    DEPTNO NUMBER(2)
);

INSERT INTO EMP02 VALUES(NULL, NULL, 'SALESMAN', 30); -- cannot insert NULL into ("SCOTT"."EMP02"."EMPNO") 오류 발생
-- NOT NULL 제약 조건을 추가함으로써 EMPNO와 ENAME 에 우리가 원하는 것처럼 NULL값이 추가가 안된다.

INSERT INTO EMP02 VALUES(1, NULL, 'SALESMAN', 30); -- cannot insert NULL into ("SCOTT"."EMP02"."ENAME") 오류 발생

INSERT INTO EMP02 VALUES(1, '홍길동', 'SALESMAN', 30); -- 다 값이 있게 데이터 추가하니깐 추가가 된다.

SELECT * FROM EMP02;

 

[ NOT NULL 제약조건 없이 테이블 생성했을 때 발생하는 문제점,, ]

 

 

 

 

 

 

 

---

 

3. 유일한 값만 허용하는 UNIQUE 제약 조건

- UNIQUE 제약 조건

  : 특정 칼럼에 대해 자료가 중복되지 않게 하는 것.

    즉, 지정된 칼럼에는 유일한 값이 수록되도록 하는 것이다.

- 새로운 사원이 입사하여 이 사원의 정보를 입력했는데, 이미 존재하는

  사원의 번호와 동일한 사원번호를 입력하였더니 성공적으로 추가된다면

  사원번호로는 사원을 구분할 수 없게되므로 큰 문제가 발생한다.

 

[ 동일한 사원번호를 가지는 사원들이 생기는 상황,, ]

 

-- UNIQUE 제약 조건을 설정하여 테이블 생성하기
DROP TABLE EMP03;
CREATE TABLE EMP03(
EMPNO NUMBER(4) UNIQUE,
ENAME VARCHAR2(10) NOT NULL,
JOB VARCHAR2(9),
DEPTNO NUMBER(2)
);

INSERT INTO EMP03
VALUES(7499, 'ALLEN', 'SALESMAN', 30);
SELECT * FROM EMP03;

INSERT INTO EMP03
VALUES(7499, 'JONES', 'MANAGER', 20);
INSERT INTO EMP03
VALUES(NULL, 'JONES', 'MANAGER', 20); -- 오류발생: unique constraint (SCOTT.SYS_C0011406) violated

INSERT INTO EMP03
VALUES(NULL, 'JONES', 'SALESMAN', 10); -- UNIQUE는 NULL값은 예외로 간주한다.
SELECT * FROM EMP03; -- 만약 NULL값도 입력되지 않게 제한하려면 UNIQUE NOT NULL처럼 기술하면 된다.

-- 아래 쉅 내용 코드 --
-- UNIQUE 제약 조건을 설정하여 테이블 생성하기
DROP TABLE EMP03;

CREATE TABLE EMP03(
    EMPNO NUMBER(4) UNIQUE,
    ENAME VARCHAR2(10) NOT NULL,
    JOB VARCHAR2(9),
    DEPTNO NUMBER(2)
);

INSERT INTO EMP03 VALUES(1499, 'ALLEN', 'SALESMAN', 30);

SELECT * FROM EMP03;

INSERT INTO EMP03 VALUES(1499, 'JONES', 'MANAGER', 20); -- unique constraint (SCOTT.SYS_C007006) violated 오류 발생으로 데이터 추가 안됨
-- EMPNO의 UNIQUE 조건을 위배했으므로 오류발생함

INSERT INTO EMP03 VALUES(NULL, 'JONES', 'MANAGER', 20); -- 데이터 추가 O
INSERT INTO EMP03 VALUES(NULL, 'JONES2', 'MANAGER', 20); -- 데이터 추가 O
INSERT INTO EMP03 VALUES(NULL, 'JONES3', 'MANAGER', 20); -- 데이터 추가 O
-- UNIQUE 조건에서는 NULL은 여러개 추가가 가능하다.
-- NULL은 UNIQUE한지 안한지 알 수 없기 때문이다.

 

 

 

 

 

---

 

4. 칼럼 레벨로 제약 조건명을 명시하여 제약 조건 설정하기    VS    테이블 레벨 방식

- 사용자가 제약조건을 주면 오라클은 SYS_ 다음에 숫자를 나열하여

  제약 조건 명을 자동 부여한다.

* 칼럼 레벨로 제약 조건명 명시하는 방법

  : 칼럼 옆에 제약조건의 이름을 적어주는 것

* SYS_ 뒤에 나오는 숫자 C009867 이 제약조건의 명 즉, 제약조건의 이름이다.

 

 

 

- 사용자가 직접 제약 조건의 이름을 설정하려면 CONSTRINT라는 키워드를 사용한다.

 

-- 형식
column_name data_type CONSTRAINT constraint_name constraint_type

-- 예: 컬럼 레벨로 제약 조건명 명시하기
DROP TABLE EMP04;
CREATE TABLE EMP04(
EMPNO NUMBER(4) CONSTRAINT EMP04_EMPNO_UK UNIQUE,
ENAME VARCHAR2(10));

SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME
FROM USER_CONSTRAINTS
WHERE TABLE_NAME='EMP04';

-- 아래 쉅 내용 코드 --
--- 4. 칼럼 레벨로 제약 조건명을 명시하여 제약조건 설정   VS  테이블 레벨로 제약조건명을 명시하여 제약조건 설정

DROP TABLE EMP03;

CREATE TABLE EMP03(
    EMPNO NUMBER(4) CONSTRAINT EMP03_EMPNO_UQ UNIQUE, -- 칼럼 레벨의 방식
    ENAME VARCHAR2(10) NOT NULL,
    JOB VARCHAR2(9),
    DEPTNO NUMBER(2)
);

INSERT INTO EMP03 VALUES(1499, 'ALLEN', 'SALESMAN', 30);

SELECT * FROM EMP03;

INSERT INTO EMP03 VALUES(1499, 'JONES', 'MANAGER', 20); -- unique constraint (SCOTT.EMP03_EMPNO_UQ) violated
-- 다음과 같이 오류 발생 시 출력될 때 내가 지정한 제약조건의 이름으로 출력된다

INSERT INTO EMP03 VALUES(NULL, 'JONES', 'MANAGER', 20); -- 데이터 추가 O
INSERT INTO EMP03 VALUES(NULL, 'JONES2', 'MANAGER', 20); -- 데이터 추가 O
INSERT INTO EMP03 VALUES(NULL, 'JONES3', 'MANAGER', 20); -- 데이터 추가 O


SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME
FROM USER_CONSTRAINTS
WHERE TABLE_NAME='EMP03';

 

 

[ EMP03 테이블에 있는 제약조건을 출력해봤을 때, 내가 지정한 제약조건의 이름의 EMPNO_UQ 제약조건과 자동적으로 정해준 제약조건의 이름 C007007 이 출력된다. ]

 

 

 

5. 데이터 구분을 위한 PRIMARY KEY 제약조건

- 기본 키(PRIMARY KEY) 제약조건

  : UNIQUE 제약 조건과 NOT NULL 제약 조건을 모두 갖는 것

- UNIQUE 제약 조건을 지정한 칼럼은 중복된 데이터를 저장하지는 못하지만

  NULL 값을 저장하는 것은 허용한다. 이와 같이 동명이인이 입사를 했다면

  이를 구분할 수 있는 유일한 키가 있어야 하는데 사원번호에 NULL 값이

  저장되는 바람에 이들을 구분할 수 없게 된다.

 

* PRIMARY KEY 용도 : 데이터(ROW)를 식별하기 위한 목적

 

 

 

 

 

-- PRIMARY KEY(기본키) 제약조건 설정하기
DROP TABLE EMP05;

CREATE TABLE EMP05(
    EMPNO NUMBER(4) CONSTRAINT EMP05_EMPNO_PK PRIMARY KEY,
    ENAME VARCHAR2(10) CONSTRAINT EMP05_ENAME_NN NOT NULL,
    JOB VARCHAR2(9),
    DEPTNO NUMBER(2)
);

INSERT INTO EMP05 VALUES(7499, 'ALLEN', 'SALESMAN', 30);
SELECT * FROM EMP05; 
INSERT INTO EMP05 VALUES(7499, 'JONES', 'MANAGER', 20);
-- SQL Error: ORA-00001: unique constraint (SCOTT.EMP05_EMPNO_PK) violated
-- 제약조건 EMPNO_PK 에 위배되어 데이터 추가가 안된다.
INSERT INTO EMP05 VALUES(NULL, 'JONES', 'MANAGER', 20);
-- SQL Error: ORA-01400: cannot insert NULL into ("SCOTT"."EMP05"."EMPNO")

 

 

 

 

[ SQL Error: ORA-00001: unique constraint (SCOTT.EMP05_EMPNO_PK) violated, 제약조건 EMPNO_PK 에 위배되어 데이터 추가가 안된다. ]

 

 

[ SQL Error: ORA-01400: cannot insert NULL into ("SCOTT"."EMP05"."EMPNO") , EMPNO 에 NULL 넣을 수 없는 제약조건이 위배되어 데이터 추가가 안된다. ]

 

 

 

---

 

 

6. 참조 무결성을 위한 FOREIGN KEY 제약 조건

- 참조의 무결성은 테이블 사이의 관계에서 발생하는 개념이므로

  사원 테이블과 부서 테이블의 관계를 예를 들어 설명하면 ...

  사원은 회사 내에 존재하는 부서에 소속되어 있어야 한다!!

  이러한 요구사항이 있다고 하자. 이걸 아래의 사진과 같은 테이블로 만들어야 한다.

- 테이블을 생성하기에 앞서 데이터베이스 모델링 과정에서 업무를 분석한 후

  얻어낸 테이블의 관계(개체와 관계)를 다이어그램으로 나타낸 것이다.

  (ERD, Entity Relationship Diagram)

 

[ 사원은 회사 내에 존재하는 부서에 소속되어 있어야 한다 라는 제약조건을 테이블(?)로 만든 것 ]

 

 

 

- 외래 키(FOREIGN KEY) 제약조건

  : 사원 테이블의 부서 번호는 반드시 부서 테이블에 존재하는

    부서 번호를 참조 가능하도록 하는 것이다.

    (참조의 무결성)

 

 

* 사원의 DEPTNO은 부서의 DEPTNO을 참조하여 만든다는 제약조건이 있으므로

  

 

 

--  EMP, DEPT 테이블의 제약조건 확인 (FOREIGN KEY 제약 조건)

-- EMP, DEPT 테이블의 제약조건 확인
SELECT TABLE_NAME, CONSTRAINT_TYPE, 
CONSTRAINT_NAME, R_CONSTRAINT_NAME
FROM USER_CONSTRAINTS
WHERE TABLE_NAME IN ('DEPT', 'EMP');

-- 왜래 키(FOREIGN KEY) 제약조건 설정하기
DROP TABLE EMP06;
CREATE TABLE EMP06( 
EMPNO NUMBER(4) CONSTRAINT EMP06_EMPNO_PK PRIMARY KEY ,
ENAME VARCHAR2(10) CONSTRAINT EMP06_ENAME_NN NOT NULL, 
JOB VARCHAR2(9),
DEPTNO NUMBER(2) CONSTRAINT EMP06_DEPTNO_FK REFERENCES DEPT(DEPTNO)
);

INSERT INTO EMP06 VALUES(7499, 'ALLEN','SALESMAN', 30);
SELECT * FROM EMP06;
INSERT INTO EMP06 VALUES(7498, 'JONES', 'MANAGER', 50); -- ORA-02291: integrity constraint (SCOTT.EMP06_DEPTNO_FK) violated - parent key not found
-- EMP의 DEPTNO 은 DEPT 테이블의 DEPTNO을 참조하는데
-- EMP 테이블 DEPTNO을 지금 DEPT 테이블에는 없는 DEPTNO 50을 추가하려고 해서
-- DEPT 테이블의 DEPTNO 칼럼에 50 값이 있는지 봤더니 50이라는 값이 없어서
-- 참조해야 하는 제약조건이 위배되어서 오류가 난 것이다.

SELECT * FROM DEPT;

SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME, R_CONSTRAINT_NAME
FROM USER_CONSTRAINTS
WHERE TABLE_NAME='EMP06';

 

 

 

[ EMP 테이블과 DEPT 테이블에 있는 제약조건 확인하는 코드와 그 결과 출력 ]

 

* DEPT 테이블의 DEPTNO 칼럼 -> P 제약조건

  EMP 테이블의 EMPNO 칼럼  -> P 제약조건

  EMP 테이블의 DEPTNO 칼럼  -> R 제약조건

 

 

 

 

[ DEPT 테이블 출력 ]

 

 

 

 

 

 

 

* 출판사는 구독자를 관리해야한다. 라는 제약조건이 있다면

 

 

 

 

 

 

 

 

 

---

 

 

7. CHECK와 DEFAULT의 제약 조건

- CHECK 제약 조건

  : 입력되는 값을 체크하여 설정된 값 이외의 값이 들어오면

    오류 메시지와 함께 명령이 수행되지 못하게 하는 것이다.

 

* CHECK 제약조건

  : 칼럼에 들어가는 값이 어떤 조건으로 들어가면 좋을지 제한을 주는 제약조건

 

-- CHECK 제약 조건 설정하기
DROP TABLE EMP07;

CREATE TABLE EMP07(
    EMPNO NUMBER(4) CONSTRAINT EMP07_EMPNO_PK PRIMARY KEY,
    ENAME VARCHAR2(10) CONSTRAINT EMP07_ENAME_NN NOT NULL,
    SAL NUMBER(7, 2) CONSTRAINT EMP07_SAL_CK CHECK(SAL BETWEEN 500 AND 5000), -- SAL 은 값이 500에서 5000인지 확인해주는 CHECK 제약조건
    GENDER VARCHAR2(1) CONSTRAINT EMP07_GENDER_CK CHECK(GENDER IN('M', 'F')) -- GENDER 의 값이 F 또는 M 만 들어왔는지 확인해주는 CHECK 제약조건 
);

INSERT INTO EMP07 VALUES(7499,'ALLEN',500,'M');
INSERT INTO EMP07 VALUES(7499,'ALLEN',7000,'A'); -- ORA-02290: check constraint (SCOTT.EMP07_GENDER_CK) violated

SELECT TABLE_NAME, CONSTRAINT_TYPE, CONSTRAINT_NAME, SEARCH_CONDITION
FROM USER_CONSTRAINTS
WHERE TABLE_NAME='EMP07';

 

* SEARCH_CONDITION : CHECK 제약조건이 어떤 조건인지 설명해주는 칼럼

 

 

 

 

 

- DEFAULT 제약 조건

  : 디폴트는 아무런 값을 입력 하지 않았을 때 디폴트제약의 값이 입력이 된다

 

-- DEFAULT 제약 조건 설정하기
DROP TABLE DEPT01;

CREATE TABLE DEPT01(
    DEPTNO NUMBER(2) PRIMARY KEY,
    DNAME VARCHAR2(14),
    LOC VARCHAR2(13) DEFAULT 'SEOUL' -- 원래는 값을 따로 지정해서 값을 추가해주지 않으면 NULL 값이 들어간다. 그런데 DEFAULT 제약조건을 해주면 LOC 칼럼에 값을 따로 지정해서 값을 추가해주지 않으면 SEOUL 값이 들어간다.  
);

INSERT INTO DEPT01(DEPTNO,DNAME) VALUES(10, 'ACCOUNTING');

SELECT * FROM DEPT01;

 

 

* LOC 칼럼에 따로 값을 지정해서 추가해주지 않았다!

  근데 LOC은 DEFAULT 제약조건이 주어졌기 때문에 NULL값이 아닌

  SEOUL 값이 들어갔다!

 

 

 

 

 

---

 

8. 테이블 레벨 방식으로 제약 조건 지정하기

- 컬럼 레벨 제약 조건

  - CREATE TABLE로 테이블을 생성하면서 컬럼을 정의하게 되는데

    하나의 컬럼 정의가 다 마 무리되기 전에 컬럼 명 다음에 타입을 지정하고

    그 뒤에 연이어서 제약 조건을 지정하는 방식이다.

 

- 테이블 레벨의 제약 조건

  - 칼럼을 모두 정의하고 나서 테이블 정의를 마무리 짓기 전에

    따로 생성된 칼럼들에 대한 제약 조건을 한꺼번에 지정하는 것이다.

 

- 복합키로 기본키를 지정할 경우

  - 지금까지는 한 개의 컬럼으로 기본키를 지정했다.

    하지만, 경우에 따라서는 2개 이상의 컬럼이 하나의 기본키를 구성하는 경우가 있는데

    이를 복합키라고 한다. 복합키 형태로 제약조건을 지정할 경우에는

    컬럼 레벨 형식으로는 불가능하고 반드시 테이블 레벨 방식 을 사용해야 한다.

 

* 복합키 지정은 테이블 레벨 방식으로 밖에 못한다?!

 

- ALTER TABLE로 제약 조건을 추가할 때

  - 테이블의 정의가 완료되어서 이미 테이블의 구조가 결정된 후에

    나중에 테이블에 제약 조건을 추가하고 할 때에는

    테이블 레벨 방식으로 제약 조건을 지정해야 한다.

 

 

 

[ 실습 : 칼럼레벨 제약조건과 테이블레벨 제약조건 설정하기 ]

 

-- 컬럼 레벨로 제약 조건을 지정하는 방법
DROP TABLE EMP01;

CREATE TABLE EMP01(
    EMPNO NUMBER(4) PRIMARY KEY,
    ENAME VARCHAR2(10) NOT NULL,
    JOB VARCHAR2(9) UNIQUE,
    DEPTNO NUMBER(4) REFERENCES DEPT(DEPTNO)
);

-- 테이블 레벨로 제약 조건을 지정하는 방법
DROP TABLE EMP02;

CREATE TABLE EMP02(
    EMPNO NUMBER(4),
    ENAME VARCHAR2(10) NOT NULL,
    JOB VARCHAR2(9),
    DEPTNO NUMBER(4),
    PRIMARY KEY(EMPNO),
    UNIQUE(JOB),
    FOREIGN KEY(DEPTNO) REFERENCES DEPT(DEPTNO)
);

 

 

 

 

 

---

 

9. 제약 조건 변경하기

( 강사님이 한번 그냥 읽고 넘어가심,,)

 

1. 제약 조건 추가하기

- 테이블 구조를 결정하는 DDL을 학습하면서 테이블이 이미 생성된 이후에

  테이블의 구조를 변경하기 위한 명령어로 ALTER TABLE을 사용한다는 것을

  이미 학습하였다.

 

- 제약조건 역시 이미 테이블을 생성하면서 지정해주는 것이었기에

  테이블 생성이 끝난 후에 제약 조건을 추가하기 위해서는

  ALTER TABLE로 추가해 주어야 한다.

 

 

-- 형식
ALTER TABLE table_name 
ADD [CONSTRAINT constraint_name] 
constraint_type (column_name);

 

 

2. MODIFY로 NOT NULL 제약 조건 추가하기

- 이미 존재하는 테이블에 무결성 제약 조건을 추가로 생성하기 위해서

  ALTER TABLE . . . ADD . . . 명령문을 사용하였다.

 

- 하지만 NOT NULL 제약 조건은 ADD 대신 MODIFY 명령문을

  사용하므로 사용에 주의해야 한다. 이는 'NULL을 허용하는 상태'에서

  'NULL을 허용하지 않는 상태'로 변경하겠다는 의미로 이해 하면 된다. 

 

 

 

3. 제약 조건 제거하기

- 제약 조건을 제거하기 위해서 DROP CONSTRAINT 다음에

  제거하고자 하는 제약 조건 명을 명시해야 한다.

 

-- 형식
ALTER TABLE table_name 
DROP [CONSTRAINT constraint_name];

 

 

 

 

---

 

10. 제약 조건의 비활성화와 CASCADE

1. 제약 조건에 의해 작업이 어려운 경우

- 제약 조건이 설정되면 항상 그 규칙에 따라 데이터 무결성이 보장된다

- 특별한 업무를 수행하는 과정에서 이러한 제약 조건 때문에

  작업이 진행되지 못하는 경우가 생긴다.

- 그렇다고 제약 조건을 삭제해 버리면 데이터 무결성을 보장받지 못하게 된다.

- 그렇기 때문에 오라클에서는 제약 조건을 비활성화시킴으로서

  제약 조건을 삭제하지 않고도 제약 조건 사용을 잠시 보류할 수 있는 방법을 제공해준다.

- 이렇게 비활성화 된 제약 조건은 원하는 작업을 한 후에는 다시 활성화 상태로 만들어 주어야 한다.

 

 

 

2. 제약 조건의 비활성화

- 테이블에서 제약 조건을 삭제하지 않고 일시적으로 적용시키지 않도록 하는 방법으로

  제약 조건을 비활성화하는 방법이 있다.

 

-- 형식
ALTER TABLE table_name 
DISABLE [CONSTRAINT constraint_name];

 

 

 

3. 제약 조건의 활성화

- 제약 조건을 비활성화 해 보았으므로 이번에는 제약 조건을 활성화 해보도록 한다.

 

-- 형식
ALTER TABLE table_name 
ENABLE [CONSTRAINT constraint_name];

 

 

 

4. CASCADE 옵션

- CASCADE 옵션

  : 부모 테이블과 자식 테이블 간의 참조 설정이 되어 있을 때

    부모 테이블의 제약 조건을 비활성화하면

    이를 참조하고 있는 자식 테이블의 제약 조건까지

    같이 비활성화 시켜 주는 옵션이다.

 

- 또한 제약 조건의 비활성화뿐만 아니라 제약 조건이 삭제에도 활용되며,

  역시 같은 이치로 부모 테이블의 제약 조건을 삭제하면

  이를 참조하고 있는 자식 테이블의 제약 조건도 같이 삭제된다.

 

 

 

 

 

[ 실습 : CASCADE 옵션으로 제약 조건 연속적으로 비활성화 / 제거하기 ]

 

-- CASCADE 옵션으로 제약 조건 연속적으로 비활성화하기
--DROP TABLE DEPT01 CASCADE CONSTRAINTS;
DROP TABLE DEPT01;

CREATE TABLE DEPT01
AS
SELECT * FROM DEPT; -- 제약조건은 복사가 안된다!! 데이터랑 테이블 틀만 복사가 됨,,
DESC DEPT;
SELECT * FROM DEPT01;
DESC DEPT01;
-- DEPT 테이블에는 있는 NOT NULL 제약조건이 없음. 따라서 따로 제약조건을 추가해줘야함

ALTER TABLE DEPT01
ADD CONSTRAINT DEPT01_DEPTNO_PK PRIMARY KEY (DEPTNO); --  <-- 테이블 레벨 방식으로 제약조건 설정해주는 것


DROP TABLE EMP01;

CREATE TABLE EMP01
AS
SELECT * FROM EMP WHERE 1=0;
-- 테이블 틀만 복사

SELECT * FROM EMP01;
DESC EMP01;
-- 제약조건 없음, 틀만 복사했기 때문

ALTER TABLE EMP01
ADD CONSTRAINT EMP01_DEPTNO_FK FOREIGN KEY (DEPTNO) REFERENCES DEPT01(DEPTNO); -- 제약조건 EMP01 테이블에 추가해줌 

SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME, R_CONSTRAINT_NAME, STATUS
FROM USER_CONSTRAINTS
WHERE TABLE_NAME IN ('DEPT01', 'EMP01');
-- 우리가 만든 DEPT01와 EMP01 테이블에 있는 제약조건 확인하는 코드

SELECT * FROM DEPT01;
SELECT * FROM EMP01; 
-- 부모 DEPT01 테이블의 DEPTNO 을 참조하게된 DEPTNO 칼럼이 추가되었다..?

INSERT INTO EMP01 (DEPTNO) VALUES (10);
-- DEPT01 테이블의 DEPTNO에 10 값이 있기 때문에 EMP01의 DEPTNO 칼럼에 값 10이 추가된다.

--DEPT 테이블에서 DEPTNO이 10인 ROW를 지워보자
DELETE FROM DEPT01
WHERE DEPTNO=10;
-- ORA-02292: integrity constraint (SCOTT.EMP01_DEPTNO_FK) violated - child record found 오류 발생
-- FOREIGN KEY 에 의해 자식 EMP01 테이블이 DEPT01테이블의 DEPTNO=10인 값을 사용하고 있기 때문에
-- 함부로 지울 수 없어서 위와같은 오류 발생한다. DEPT01 테이블에서 DEPTNO=10을 지우고 싶다면 자식을 먼저 지워야한다.

DELETE FROM EMP01; -- EMP01 테이블을 지우고 위의 코드 DELTE FROM DEPT01 WHERE DEPTNO=10 코드를 실행하면 아주 잘된다.
SELECT * FROM DEPT01; -- 지우고 DEPT01 테이블을 보게 되면 DEPTNO=10인 ROW가 사라진것을 확인할 수 있다.

[ CASCAD 옵션 추가하지 않고 제약조건 추가하는 코드 ]

 

 

 

 

ALTER TABLE EMP01
ADD CONSTRAINT EMP01_DEPTNO_FK FOREIGN KEY (DEPTNO) REFERENCES DEPT01(DEPTNO)
ON DELETE CASCADE;
-- ON DELETE CASCAD 이 옵션이 추가되면 어떻게 되느냐?
-- 위의 ON DELETE CASCAD 옵션이 없는 제약조건을 추가해줬을 때는 
-- 부모 테이블에 있는 값을 지우려고 할 때 자식 테이블을 먼저 지우고 그다음에 부모 테이블 값을 
-- 지워줬어야하는데
-- ON DELETE CASCAD 옵션을 추가한 제약조건을 추가해주면
-- 자식 테이블을 먼저 지우지 않고도 그냥 바로 부모 테이블에서 원하는 값을 지우면 지워진다!
-- 설사 자식 테이블이 있더라도 자식 테이블에 영향 주지 않으면서
-- 부모 테이블을 DROP 할수도 부모 테이블에서 원하는 값을 DELETE할 수도 있는 것이다!!

[ CASCADE 옵션 추가하여 제약조건 추가는 코드 ]

 

* ON DELETE CASCAD 이 옵션이 추가되면 어떻게 되느냐?
  위의 ON DELETE CASCAD 옵션이 없는 제약조건을 추가해줬을 때는 
  부모 테이블에 있는 값을 지우려고 할 때 자식 테이블을 먼저 지우고 그다음에 부모 테이블 값을 
  지워줬어야하는데
  ON DELETE CASCAD 옵션을 추가한 제약조건을 추가해주면
  자식 테이블을 먼저 지우지 않고도 그냥 바로 부모 테이블에서 원하는 값을 지우면 지워진다!
  설사 자식 테이블이 있더라도 자식 테이블에 영향 주지 않으면서
  부모 테이블을 DROP 할수도 부모 테이블에서 원하는 값을 DELETE할 수도 있는 것이다!! 

 

 

 

 

 

[ 아래의 코드 : CASCADE 옵션 추가 안한 코드로 출력한 결과물들,, ]

 

[ DEPT 테이블 복사한 DEPT01 테이블 ]

 

 

[ DEPT 테이블에는 DEPTNO 칼럼에 NOT NULL 제약조건이 있음]

 

 

[ DEPT 테이블을 복사한 DEPT01 테이블에는 NOT NULL 제약조건이 없다! ]

* 테이블을 복사할 때 데이터 값이랑 테이블 틀만 복사가 되고

  제약조건은 복사가 안된다! -> 따로 제약조건 추가해줘야 함

 

 

[ DEPT01 테이블의 DEPTNO 칼럼에 PRIMARY KEY 제약조건을 추가해준 후 DEPT01 테이블 출력 ]

 

 

[ EMP 테이블의 틀만 복사한 EMP01 테이블 ]

 

 

[ 테이블 틀만 복사한 것이므로 EMP01 테이블에는 제약조건이 없다. ]

 

[ EMP01 테이블의 DEPTNO 칼럼에 제약조건 추가해주고 EMP01 테이블과 DEPT01 테이블에 있는 제약조건 확인한 출력 결과 ]

 

 

[ 바로 부모 테이블 DEPT01의 값을 삭제하거나 테이블 DEPT01을 DROP 할 수 없어서 자식 테이블 EMP01 테이블을 먼저 지워주고 그 다음에 DEPT01 테이블의 DEPTNO=10의 ROW를 지운 후 DEPT01 테이블을 SELECT 한 결과 출력 ]