[SQL/ORACLE] 제약조건 설명 및 사용법 (NOT NULL, UNIQUE, PK, FK, CHECK)

: 무결성 제약조건

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

1. NOT NULL
   • 해당 컬럼값으로 NULL을 허용하지 않음
   • (행 단위)입력시 데이터를 무조건 받음
   • 유일하지 않고 반드시 입력 또는 수정해야 할 컬럼에 설정
2. UNIQUE
   • 테이블내에서 해당 컬럼값은 항상 유일무이한 값을 가질 것
   • 중복을 허용하지 않음
   • 중복데이터는 방지, NULL의 중복은 방지하지 못함
   • ADD NOT NULL은 불가능
     =>테이블 내의 모든 컬럼은 특별한 설정(NOT NULL또는 PRIMARY KEY)을 하지 않았을 경우 NULL값이 기본으로 설정되어짐
3. PRIMARY KEY (기본키, 주키)
   • 해당 컬럼값은 반드시 존재해야 하고 유일해야 한다는 조건
   • NOT NULL과 UNIQUE 조건을 동시에 만족함
   • 테이블 내에서 서로 다른 행을 구분하기 위해서 사용
   • 한 테이블 내에 단 한개의 primary key만 존재
4. FOREIGN KEY (외래키, 참조키)
   • 해당 컬럼의 값이 타컬럼의 값을 참조해야만 함
   • 즉, 참조되는 컬럼에 존재하지 않는 값은 입력 불가
   • 참조하는 테이블에서 설정 (예: 사원테이블의 deptno에 참조키 설정)
   • 참조되는 쪽 테이블(부서테이블)
   • NULL값 허용(단, NULL이 아닐경우 참조테이블에 있는 데이터가 입력되어야함)
   • 참조되는 컬럼은 반드시 UNIQUE 또는 PRIMARY KEY 설정
5. CHECK
   • 해당 컬럼에 저장 가능한 데이터 값의 범위나 사용자 조건을 지정
   • 특정 범위 내의 데이터만 입력
   • 정해진 데이터만 입력

• DEPT(자식테이블) ⇒ EMP(부모테이블)
  deptno는 DEPT 테이블의 PK이며 EMP 테이블 안에서 FK의 역할을 함

INSERT INTO emp (empno, ename, deptno)  
VALUES(7787,'길동',30); -- 실행. 30번 존재.

INSERT INTO emp (empno, ename, deptno)  
VALUES(7787,'길동',50); -- 에러발생. 50번 미존재

---

제약조건 설정

1. 컬럼레벨 정의방법

• 컬럼레벨: 제약조건의 명시를 테이블 생성시 컬럼정의와 함께함

    CREATE TABLE 테이블명(
        컬럼명 자료형 CONSTRAINT_TYPE
    );


    ------------ [ NOT NULL ] -----------
        empno number(4) NOT NULL

    INSERT INTO emp2 (ename,sal) VALUES ('라임', 2000); 
    --에러발생. COLUMN에 empno을 지정하지않으면 자동으로 NULL값 입력


    ------------ [ UNIQUE ] -------------
        empno number(4) UNIQUE

    INSERT INTO emp2 VALUES (7000,'길동');
    INSERT INTO emp2 VALUES (7002,'길동');
    INSERT INTO emp2 VALUES (7002,'길동');
    --에러발생. empno는 UNIQUE 제약조건이 있기에 7002 중복 불가


    ------------ [ PRIMARY KEY ] -------------
        empno number(4) PRIMARY KEY

    CREATE TABLE dept2(
        deptno NUMBER(2) primary key,
    --dname VARCHAR2(15) primary key, 에러발생. PK는 한개만 적용가능
        location VARCHAR2(15)
    );

    ALTER TABLE dept2 ADD CONSTRAINT dept_pk PRIMARY KEY (deptno, dname);
    -->TABLE ALTERED (deptno와 dname을 조합한 한개의 PK 설정)

    INSERT INTO dept2 VALUES (10,'영업부','서울');
    -- INSERT INTO dept2 VALUES (10,'기획부','서울'); 에러발생. deptno는 PK
    -- INSERT INTO dept2 VALUES (null,'재무부','서울'); 에러발생
    INSERT INTO dept2 VALUES (20,'영업부','서울');


    ------------ [ FOREIGN KEY ] -------------
    --> 컬럼레벨
    CREATE TABLE 테이블(
        컬럼명 자료형 [CONSTRAINT 제약명] REFERENCES 참조테이블명(참조컬럼명);
        deptno NUMBER(2) REFERENCES dept(deptno);
    );

    --> 테이블레벨
    CREATE TABLE 테이블명(
        컬럼명1 자료형,
        컬럼명2 자료형,
        컬럼명3 자료형,
        CONSTRAINT 제약명 FOREIGN KEY (컬럼명2) REFERENCES 참조테이블명 (참조컬럼명)
    --> FOREIGTN KEY (deptno) REFERENCES dept (deptno) 
    );

    --> 테이블 생성 끝난 후 추가
    ALTER TABLE 테이블명
    ADD [CONSTRAINT 제약명] FOREIGN KEY (컬럼명) REFERENCES 참조테이블명(참조컬럼명);


    ------------ [ CHECK ] -------------
    CHECK (조건식)

1_2. 제약 이름지정

CREATE TABLE 테이블명(
    컬럼명 자료형 [CONSTRAINT 제약명] 제약타입 
);

CREATE TABLE dept2(
    deptno NUMBER(2) CONSTRAINT dept2_pk primary key,
    dname VARCHAR2(15),
    location VARCHAR2(15)
);

2. 테이블 레벨 정의 방법

: 전체 컬럼선언이 끝나고 난 후

CREATE TABLE emp2(
    empno NUMBER(4),
    ename VARCHAR2(15),
    CONSTRAINT emp2_uk UNIQUE (empno)
); 

3. 제약 추가

: 이미 테이블 객체가 만들어진 상태에서 제약 추가

ALTER TABLE 테이블명 ADD [CONSTRAINT 제약명] PRIMARY KEY (컬럼명);

4. 제약 삭제

--1
ALTER TABLE 테이블명 DROP CONSTRAINT 제약명;

--2
ALTER TABLE 테이블명 DROP PRIMARY KEY;

5. 제약 수정

ALTER TABLE 테이블명 MODIFY 컬럼명 [CONSTRAINT 제약명] NOT NULL | NULL;

* 문제풀이

    --문제1 emp3테이블을 새롭게 생성(empno, ename, sal, deptno) 
    --      dept3테이블을 dept테이블 참조하는 서브쿼리를 통해 생성(전체구조, 데이터)하시오.
    CREATE TABLE emp3 (
    empno NUMBER(4), 
    ename VARCHAR2(15), 
    sal NUMBER(7,2), 
    deptno NUMBER(4) PRIMARY KEY
    ); 
    CREATE TABLE dept3 AS SELECT * FROM dept;

    --문제2 emp3테이블의 deptno가 dept3테이블의 deptno를 참조하도록 (unique사용)
    ALTER TABLE dept3 ADD CONSTRAINT dept3_un Unique (deptno);
    ALTER TABLE emp3 ADD CONSTRAINT emp3_fk FOREIGN KEY (deptno) REFERENCES dept3(deptno);
    -- emp가 자식, dept가 부모가됨

    -- 문제3 dept3테이블의 unique제약을 삭제하고 deptno에 primary key 제약 설정
    ALTER TABLE emp3 DROP CONSTRAINT emp3_fk;
    ALTER TABLE dept3 DROP CONSTRAINT dept3_un;
    ALTER TABLE dept3 ADD CONSTRAINT dept3_pk PRIMARY KEY (deptno); 
    ALTER TABLE emp3 ADD CONSTRAINT emp3_fk FOREIGN KEY (deptno) REFERENCES dept3(deptno);

    -> CASCADE 사용
    ALTER TABLE dept3 DROP CONSTRAINT dept3_uk CASCADE;

    -- 문제4 제약조건 설정
    CREATE TABLE emp4 (
        empno NUMBER(4) PRIMARY KEY, 
        ename VARCHAR2(15) DEFAULT '이름', 
        sal NUMBER(7,2) CHECK (sal BETWEEN 500 AND 1000),
        mgr VARCHAR2(5) NOT NULL,
        deptno NUMBER(4) CONSTRAINT emp4_fk REFERENCES dept3(deptno),
        gender VARCHAR2(5) CHECK (gender IN('M','F')) -- (gender = 'M' OR gender='F')
    );