무결성 제약 조건: NotNull, Unique, PrimaryKey, ForeginKey, CASCADE

1. 무결성 제약 조건

테이블에 부적절한 자료가 입력되는 것을 방지하기 위해서 테이블을 생성할 때 각 컬럼에 대해서 정의하는 여러가지 규칙

NOT NULL : NULL을 허용하지 않는다.

UNIQUE : 중복된 값을 허용하지 않고 항상 유일한 값을 갖도록 한다.

PRIMARY KEY : NULL을 허용하지 않고 중복값 허용하지 않는다. (NOT NULL + UNIQUE 조건)

FOREIGN KEY : 참조되는 테이블의 컬럼 값이 존재하면 허용. 2개 테이블의 관계

CHECK : 저장 가능한 데이터 값의 범위나 조건을 지정하여 설정한 값만을 허용

SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE

FROM USER_CONSTRAINTS

WHERE TABLE_NAME = 'EMP';

-- 딕셔너리에서 EMP 테이블의 CONTSRAINT를 확인

CONSTRAINT_TYPE

P : PRIMARY KEY

R : FOREIGN KEY

U : UNIQUE

C : CHECK. NOT NULL

SELECT CONSTRAINT_NAME, COLUMN_NAME

FROM USER_CONS_COLUMNS

WHERE TABLE_NAME = 'EMP';

2. 무결성 제약 조건 넣기 (컬럼 레벨)

[CONSTRAINT constraint_name] constraint_type(column_name)

CONSTRAINT 지침

- 제약조건에 이름을 지정하지 않으면 Oracle server가 SYS_Cn의 형식으로 자동으로 이름을 생성.

- 제약조건은 크게 테이블 레벨과 열 레벨(컬럼 레벨)로 정의

CREATE TABLE EXAMPLE(

ID NUMBER(6),

NAME VARCHAR2(20) [CONSTRAINT NAME_CTR] NOT NULL,     

----> 컬럼 레벨(제약조건 이름을 생략하면 시스템이 자동으로 이름 생성

CONSTRAINT EXAMPLE_ID_PK PRIMARY KEY(ID));                           

----> 테이블 레벨(제약조건 이름이 EXAMPLE_ID_PK로 생성)

1) NOT NULL 지정

CREATE TABLE EMP02(

EMPNO NUMBER(4) NOT NULL, -- 시스템이 자동으로 제약조건 이름 생성

ENAME VARCHAR2(10) CONSTRAINT EMP04_ENAME_NN NOT NULL, -- 제약조건 이름 지정

JOB VARCHAR2(9),

DEPTNO NUMBER(2)

);

-- INSERT 할 때 EMPNO, ENAME을 입력하지 않으면 에러가 난다. (NULL값을 넣을 수 없다.)

-- CONSTRAINT EMP04_ENAME_NN 은 제약조건의 이름을 직접 넣은 것

-- 제약조건의 이름을 쓰지 않으면 시스템이 자동으로 SYS_Cn 형식으로 자동으로 이름을 생성한다.

2) UNIQUE 지정 (중복값 불가)

CREATE TABLE EMP04(

EMPNO NUMBER(4) CONSTRAINT EMP04_EMPNO_UK UNIQUE,

ENAME VARCHAR2(10) CONSTRAINT EMP04_ENAME_NN NOT NULL,

JOB VARCHAR2(9),

DEPTNO NUMBER(2)

);

-- INSERT시 EMPNO가 똑같은 것을 또 넣을 수 없게 된다.

3) PRIMARY KEY 지정 (NOT NULL + 중복값 불가)

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시 EMPNO에 NULL값도 못넣고, 중복값도 넣을 수 없다.

4) FOREIGN KEY 지정 (2개 테이블의 관계)

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)

);

-- DEPTNO를 DEPT 테이블의 DEPTNO를 레퍼런스(참조)로 삼는 FOREIGN KEY로 설정.

INSERT INTO EMP06

VALUES (7499, 'ALLEN', 'SALESMAN', 30);

-- 부모테이블인 DEPT 테이블에 30이라는 DEPTNO가 있으므로 INSERT 가능

INSERT INTO EMP06

VALUES (7499, 'ALLEN', 'SALESMAN', 50);

-- 부모테이블인 DEPT 테이블에 50이라는 DEPTNO가 없으므로 에러가 난다.

5) CHECK

CREATE TABLE EMP07(

SAL NUMBER(7, 2) CONSTRAINT EMP07_SAL_CH CHECK(SAL BETWEEN 500 AND 5000);

GENDER VARCHAR2(1) CONSTRAINT EMP07_GENDER_CH CHECK(GENDER IN('M', 'F'));

-- SAL에는 500에서 5000사이의 값만 넣도록 지정

-- GENDER에는 M이나 F 중 하나의 문자만 넣도록 지정

6) DEFAULT

CRETE TABLE EMP07(

LOC VARCHAR2(13) DEFAULT 'SEOUL'

-- 값을 별도로 지정하지 않으면 NULL이 아니라 DEFAULT로 설정된 값(SEOUL)이 들어감.

3. 테이블 레벨 제약 조건 넣기

테이블 레벨 제약 조건 지정은 컬럼을 모두 정의 후 테이블 정의를 마무리 짓기 전에 따로 생성된 컬럼들에 대한 제약 조건을 한꺼번에 지정하는 것을 말한다.

- 제약 조건 하단에 넣기 (테이블 레벨 제약 조건)

CREATE TABLE EMP01(

EMPNO NUMBER(4),

DEPTNO NUMBER(4),

...

FOREIGN KEY(DEPTNO REFERENCES DPT(DEPTNO)

);

-- 제약조건을 아래에 따로 넣어줄 수도 있다.

4. 테이블 레벨 제약 조건 추가 (ADD)

ALTER TABLE table_name

ADD [CONSTRAINT constraint_name] constraint_type(column_name);

ALTER TABLE EMP01 ADD CONSTRAINT EMP01_EMPNO_PK PRIMARY KEY(EMPNO)

5. 테이블 레벨 제약 조건 삭제 (DROP)

ALTER TABLE table_name

DROP [CONSTRAINT constraint_name];

ALTER TABLE EMP01 

DROP CONSTRAINT EMP01_EMPNO_PK;

6. 제약조건 비활성화 / 활성화 (DISABLE / ENABLE)

DISABLE CONSTRAINT : 제약 조건의 일시 비활성화

ENABLE CONSTRAINT : 비활성화된 제약 조건을 해제하여 다시 활성화

ALTER TABLE table_name

DISABLE [CONSTRAINT constraint_name];

ALTER TABLE table_name

ENABLE [CONSTRAINT constraint_name];

disable된 제약조건을 enable시키기 위해서는 PRIMARY KEY를 enable시킨 후, FOREIGN KEY를 enable 시켜야 한다.

7. CASCADE 옵션

부모 테이블과 자식 테이블 간에 참조 설정이 되어있을 때 부모 테이블의 제약 조건을 비활성화 시키면서 이를 참조하고 있는 자식 테이블의 제약조건까지 함께 비활성화 시킬 수 있다.

ALTER TABLE EMP01 

DROP PRIMARY KEY CASCADE;

-- CASCADE 옵션을 붙이면 삭제가 가능해진다.