Database_54_SQL 제약 조건_Constraint( Not null, Unique, Primary Key, Foreign Key, Check)

-- 제약 조건_Constraint
    /*
    CREATE TABLE [schema.]table (
                column datatype [DEFAULT expr],...,
                [ table_constraint ] );
    */
    -- '테이블 이름'_'컬럼 이름'_'제약 조건'
    -- 테이블 레벨로 규칙을 적용합니다.
    -- 종속된 테이블의 삭제를 방지합니다.
    -- 유효한 제약 조건 유형
        -- NOT NULL
            -- 해당 열에 널 값을 사용할 수 없습니다.
            -- 열 레벨로만 지정할 수 있습니다.
            -- DESC/DESCRIBE에서 확인할 수 있습니다.
            /*
            column datatype NOT NULL    : 시스템이 이름을 지정합니다.
            column datatype CONSTRAINT constraint_name  : 사용자가 이름을 지정합니다. 
            */
        -- UNIQUE
            -- 열 또는 열의 집합의 모든 값이 고유하게 유지되도록 합니다.
                -- 한 테이블에 있는 두 행은 지정된 열 또는 열 집합에서 중복된 값을 가질 수 없습니다.
            -- 널 값이 허용됩니다.
                -- 널 값은 어떠한 값과도 동일한 것으로 취급되지 않습니다.
                -- 여러 개가 존재할 수 있습니다.
            -- 널 값을 허용하지 않기 위해서 NOT NULL 제약 조건을 추가할 수 있습니다.
            -- UNIQUE 인덱스가 자동으로 생성됩니다.
            /*
            CONSTRAINT constraint_name UNIQUE(column) : 테이블 레벨 제약 조건입니다.
            */
        -- PRIMARY KEY 
            -- 테이블 당 하나만 지정이 가능합니다.
            -- 테이블의 각 행을 고유하게 식별합니다.
            -- 열 또는 열의 조합에 고유성을 부여하며 기본 키에 속하는 열이 널 값을 가질 수 없도록 합니다.
            -- PRIMARY KEY 열에 대해서는 UNIQUE 인덱스가 자동으로 생성됩니다.  *****
            -- 열 레벨 또는 테이블 레벨로 정의합니다.
            /*
            CONSTRAINT constraint_name PRIMARY KEY(column) : 테이블 레벨 제약 조건
            */
        -- FOREIGN KEY
            -- 동일한 테이블이나 다른 테이블에 있는 기본 키와의 관계를 설정합니다.
            -- 참조무결성 제약 조건이라고 합니다.
            -- 부모 테이블 : 기본키를 포함한 테이블
            -- 자식 테이블 : 외래키를 포함한 테이블
            -- 외래키 값은 부모 테이블의 기존 값과 일치하거나 NULL 값이어야 합니다.
            -- 키워드
                -- FOREIGN KEY : 테이블 제약 조건 레벨로 자식 테이블의 열을 정의합니다.
                -- REFERENCES : 부모 테이블 및 부모 테이블에서의 해당 열을 식별합니다.
                -- ON DELETE CASCADE : 부모 테이블의 행이 삭제되는 경우 자식 테이블의 종속 행을 삭제합니다.
                -- ON DELETE SET NULL : 종속 외래 키 값을 널로 변환합니다.
                    -- ON DELETE : 부모 키를 지운다면이라는 뜻입니다.
                        -- 작성하지 않는 경우 자식 테이블이 삭제되지 않는 이상 부모 테이블을 삭제할 수 없습니다.
                        -- 작성하는 경우 부모 테이블만 삭제됩니다.
                /*
                -- SET NULL
                    -- 고객 테이블
                        -- 고객 아이디 (PK)
                        
                    -- 주문 테이블
                        -- 주문 고객 (FK)
                        
                    -- 고객 아이디를 외래키로 사용하는 주문 테이블이 존재하므로 고객이 탈퇴할 수 없습니다.
                    -- ON DELETE SET NULL로 작성하여 고객이 탈퇴하는 경우 고객 아이디만 NULL 값으로 수정합니다.

                -- CASCADE                
                    -- 직원 테이블
                        -- 기사 정보
                        -- 기사 차량 정보
                        
                    -- 차량 지원
                        -- 지원 차량 정보 (FK)
                    
                    -- 기사가 퇴사를 하는 경우 지원을 하던 차량 정보에서도 데이터를 삭제해야 합니다.
                    -- ON DELETE CASCADE로 작성하여 기사가 퇴사하는 경우 차량 정보도 삭제합니다.
                */
        -- CHECK
        
    -- 제약 조건 생성 시기  
        -- 테이블이 생성될 때 : CREATE
        -- 테이블이 생성된 후 : ALTER
    -- 지침
        -- 제약 조건에 이름을 지정하지 않는 경우 Oracle Server가 SYS_Cn 형식의 이름을 생성합니다.
        -- 열 레벨 도는 테이블 레벨로 제약 조건을 정의합니다.
        -- 데이터 딕셔너리에서 테이블에 정의된 제약 조건을 볼 수 있습니다.
    -- 정의
        -- 컬럼 레벨 제약 조건 : 컬럼 선언 시 옆에 [ 제약 조건 ]작성합니다.
        -- 테이블 레벨 제약 조건 : 테이블 생성 시 마지막에 [ CONSTARINT + 제약 조건 ]작성합니다.
    
---------------------------------------------------------------------------------------------------------------
 -- 후보키 : UNIQUE + NOT NULL
 -- 외래키 : 어떤 기본 키를 참조하는 키입니다.
---------------------------------------------------------------------------------------------------------------

SELECT table_name, constraint_name, constraint_type, search_condition
FROM user_constraints
WHERE table_name='EMPLOYEES';
    /*
    TABLE_NAME      CON_NAME                      CON_TYPE      SEARCH_CONDITION        
    -----------------------------------------------------------------------------------------------------------------
    EMPLOYEES       EMP_LAST_NAME_NN          C                   "LAST_NAME" IS NOT NULL        
    EMPLOYEES       EMP_EMAIL_NN                     C                   "EMAIL" IS NOT NULL        
    EMPLOYEES       EMP_HIRE_DATE_NN            C                   "HIRE_DATE" IS NOT NULL        
    EMPLOYEES       EMP_JOB_NN                         C                   "JOB_ID" IS NOT NULL        
    EMPLOYEES       EMP_SALARY_MIN                C                   salary > 0        
    EMPLOYEES       EMP_EMP_ID_PK                   P                   ( null )
    EMPLOYEES       EMP_EMAIL_UK                     U                   ( null )                         
    EMPLOYEES       EMP_JOB_FK                         R                   ( null )                         
    EMPLOYEES       EMP_DEPT_FK                       R                   ( null )                         
        -- NOT NULL / CHECK 는 묶어 C로 표기됩니다.
            -- SEARCH_CONDITION을 같이 확인해야 합니다.
    */

-- 데이터 확인
DESC user_cons_columns;
    /*
    이름                        널?                유형             
    ---------------         --------        -------------- 
    OWNER                 NOT NULL    VARCHAR2(30)   
    CONSTRAINT_NAME NOT NULL    VARCHAR2(30)   
    TABLE_NAME          NOT NULL    VARCHAR2(30)   
    COLUMN_NAME                       VARCHAR2(4000) 
    POSITION                                NUMBER         
    */

SELECT table_name, column_name, constraint_name
FROM user_cons_columns
WHERE table_name='EMPLOYEES';
    /*
    TABLE_NAME      COLUMN_NAME      CONSTRAINT_NAME       
    ---------------------------------------------------------------------------------------
    EMPLOYEES        LAST_NAME             EMP_LAST_NAME_NN        
    EMPLOYEES        EMAIL                       EMP_EMAIL_NN        
    EMPLOYEES        HIRE_DATE             EMP_HIRE_DATE_NN        
    EMPLOYEES        JOB_ID                     EMP_JOB_NN        
    EMPLOYEES        SALARY                   EMP_SALARY_MIN        
    EMPLOYEES        EMPLOYEE_ID       EMP_EMP_ID_PK        
    EMPLOYEES        EMAIL                      EMP_EMAIL_UK        
    EMPLOYEES        JOB_ID                    EMP_JOB_FK        
    EMPLOYEES        DEPARTMENT_ID   EMP_DEPT_FK        
    */

-- 조인을 사용하여 확인합니다.
SELECT c.table_name, cc.column_name, c.constraint_name, c.constraint_type, c.search_condition
FROM user_constraints c JOIN user_cons_columns cc
ON (c.constraint_name = cc.constraint_name)
WHERE c.table_name='EMPLOYEES';
/*
TABLE_NAME     COL_NAME       CON_NAME                 CON_TYPE    SEARCH_CONDITION       
 --------------------------------------------------------------------------------------------------------------------------
EMPLOYEES      LAST_NAME      EMP_LAST_NAME_NN        C           "LAST_NAME" IS NOT NULL        
EMPLOYEES      EMAIL                 EMP_EMAIL_NN                  C           "EMAIL" IS NOT NULL        
EMPLOYEES      HIRE_DATE        EMP_HIRE_DATE_NN         C           "HIRE_DATE" IS NOT NULL        
EMPLOYEES      JOB_ID               EMP_JOB_NN                      C           "JOB_ID" IS NOT NULL        
EMPLOYEES      SALARY              EMP_SALARY_MIN             C           salary > 0        
EMPLOYEES      EMPLOYEE_ID  EMP_EMP_ID_PK                 P             ( null )
EMPLOYEES      EMAIL                 EMP_EMAIL_UK                   U             ( null )     
EMPLOYEES      JOB_ID               EMP_JOB_FK                        R            ( null )      
EMPLOYEES      DP_ID                 EMP_DEPT_FK                     R            ( null )      
*/

    
-- 테이블을 생성합니다.
CREATE TABLE dept (
                -- 컬럼 레벨
            deptno NUMBER(4) PRIMARY KEY,
            deptname VARCHAR2(20) NOT NULL UNIQUE,
            create_date DATE CONSTRAINT dept_credate_nn NOT NULL,
            loc VARCHAR2(50),
            
                --  테이블 레벨 : 어떤 컬럼의 제약인지 ( )안에 작성합니다.
            CONSTRAINT dept_loc_uk UNIQUE(loc)
            );
    /*
    Table DEPT이(가) 생성되었습니다.
    */
    
-- 테이블이 생성되었는지 확인합니다.
DESC dept;
    /*
이름              널?              유형           
-----------     --------         ------------ 
DEPTNO        NOT NULL     NUMBER(4)    
DEPTNAME    NOT NULL     VARCHAR2(20) 
CREATE_DATE NOT NULL     DATE         
LOC                                VARCHAR2(50)   
    */

-- 제약 조건이 생성되었는지 확인합니다.
SELECT c.table_name, cc.column_name, c.constraint_name, c.constraint_type, c.search_condition
FROM user_constraints c JOIN user_cons_columns cc
ON (c.constraint_name = cc.constraint_name)
WHERE c.table_name='DEPT';
        -- 이름을 따로 지정하지 않은 컬럼은 'SYS_'로 시작하는 이름을 시스템이 자동으로 지정합니다.

-- 참조 외래키

-- dept 테이블을 참조하는 emp 테이블을 생성합니다.
CREATE TABLE emp (
            empid NUMBER(6) CONSTRAINT emp_id_pk PRIMARY KEY,
            emp_name VARCHAR2(20) NOT NULL,
            email VARCHAR2(25) NOT NULL,
            salary NUMBER(8, 2) NOT NULL,
            hire_date DATE,
            deptno NUMBER(4) REFERENCES dept(deptno),   -- 외래키로 사용할 기본 키를 작성합니다.
                -- 참조할 테이블의 컬럼이 기본키로 정의되어 있지 않는 경우 테이블이 생성되지 않습니다.
                -- dept 테이블은 자식 테이블로 emp가 존재하므로 테이블을 삭제할 수 없습니다.
                    -- 테이블은 참조하는 순으로 생성하고 역순으로 삭제해야 합니다.
            CONSTRAINT emp_id_email_uk UNIQUE (empid, email),   -- 두 개 이상의 열을 묶어 제약 조건을 작성합니다.
                -- 각 하나만 중복되는 것은 상관이 없으나 두 컬럼 값이 모두 일치하면 오류가 발생합니다.
            CONSTRAINT emp_sal_ck CHECK (salary BETWEEN 2500 AND 30000)
            );
    /*
    Table EMP이(가) 생성되었습니다.
    */

-- 제약 조건이 생성되었는지 확인합니다.
SELECT c.table_name, cc.column_name, c.constraint_name, c.constraint_type, c.search_condition
FROM user_constraints c JOIN user_cons_columns cc
ON (c.constraint_name = cc.constraint_name)
WHERE c.table_name='EMP';
        -- 외래키는 R로 표기됩니다.

-- dept 테이블과 emp 테이블을 의 제약조건을 한 번에 확인합니다.
SELECT c.table_name, cc.column_name, c.constraint_name, c.constraint_type, c.search_condition
FROM user_constraints c JOIN user_cons_columns cc
ON (c.constraint_name = cc.constraint_name)
WHERE c.table_name IN ('EMP', 'DEPT');

-- 부서 테이블에 정보를 입력합니다.
INSERT INTO dept
VALUES (10, '총무', sysdate, '서울');
    /*
    1 행 이(가) 삽입되었습니다.
    */
    
INSERT INTO dept
VALUES (20, '영업', sysdate, '부산');
    /*
    1 행 이(가) 삽입되었습니다.
    */

-- 커밋합니다.
COMMIT;
    /*
    커밋 완료.
    */

-- 직원 테이븛에 정보를 입력합니다.
INSERT INTO emp
VALUES (1, 'name01', 'user', 7000, sysdate, 30); 
    /*
        <오류>----------------------------------------------------------------------------------
        오류 보고 -
        ORA-02291: integrity constraint (HR.SYS_C007093) violated - parent key not found
        ------------------------------------------------------------------------------------------
    */

INSERT INTO emp
VALUES (1, 'name02', 'user', 700, sysdate, 10);
    /*
        <오류>-----------------------------------------------------
        오류 보고 -
        ORA-02290: check constraint (HR.EMP_SAL_CK) violated
        -------------------------------------------------------------
    */

INSERT INTO emp
VALUES (1, 'name03', 'user', 7000, sysdate, 10);
    /*
    1 행 이(가) 삽입되었습니다.
    */

INSERT INTO emp
VALUES (2, 'name04', 'user', 24000, sysdate, 20);
    /*
    1 행 이(가) 삽입되었습니다.
    */
        -- name03과 name04는 EMAIL 값이 동일하게 user 이지만 EMPID가 달라 UNIQUE 키에 적합합니다.
        
-- 커밋합니다.
COMMIT;
    /*
    커밋 완료.
    */

-- 부서 테이블을 조회합니다.
SELECT * FROM dept;
    /*
    DEPTNO DEPTNAME  CREATE_DATE    LOC
    --------------------------------------------------------------
    10           총무                23/08/03              서울
    20           영업                23/08/03              부산
    */

    
-- 직원 테이블을 조회합니다.
SELECT * FROM emp;
    /*
    EMPID   EMP_NAME    EMAIL   SALARY    HIRE_DATE   DEPTNO
    ----------------------------------------------------------------------------------------
    1               name03           user        7000       23/08/03            10
    2               name04           user      24000       23/08/03            20
    */

DELETE FROM dept
WHERE deptno=10;
    /*
        <오류>------------------------------------------------------------------------------
        오류 보고 -
        ORA-02292: integrity constraint (HR.SYS_C007093) violated - child record found
        --------------------------------------------------------------------------------------
    */


-- ON DELETE 절을 작성하지 않고 삭제하고 싶은 경우 자식 테이블의 정보부터 수정하거나 삭제해야 합니다.
UPDATE emp
SET deptno=20;
    /*
    2개 행 이(가) 업데이트되었습니다.
    */

DELETE FROM dept
WHERE deptno=10;
    /*
    1 행 이(가) 삭제되었습니다.
    */

-- 롤백합니다.
ROLLBACK;
    /*
    롤백 완료.
    */


-- 테이블 제약 조건 관리
    -- ALTER TABLE 문을 사용합니다.
        -- 추가
            /*
            ALTER TABLE table
            ADD [ CONSTRAINT constraint ] type (column)
            */
        -- 삭제
            /*
            ALTER TABLE table
            DROP [ CONSTRAINT constraint ] type (column)
            */
        -- 활성화
            /*
            ALTER TABLE table
            ENABLE [ CONSTRAINT constraint ] type (column)
            */
        -- 비활성화
            /*
            ALTER TABLE table
            DISABLE [ CONSTRAINT constraint ] type (column)
            */


-- 제약 조건을 확인합니다.
SELECT c.table_name, cc.column_name, c.constraint_name, c.constraint_type, c.search_condition
FROM user_constraints c JOIN user_cons_columns cc
ON (c.constraint_name = cc.constraint_name)
WHERE c.table_name='EMP';

-- 제약 조건의 이름을 지정하여 테이블을 삭제합니다.
ALTER TABLE emp
DROP CONSTRAINT emp_id_email_uk;
    /*
    Table EMP이(가) 변경되었습니다.
        -- 컬럼 명일 지정한 경우 지정한 컬럼명으로 삭제할 수 있습니다.
        -- 자동으로 시스템이 지정한 이름이라면 조회하여 삭제해야 합니다.
    */

-- 제약 조건을 확인합니다.
SELECT c.table_name, cc.column_name, c.constraint_name, c.constraint_type, c.search_condition
FROM user_constraints c JOIN user_cons_columns cc
ON (c.constraint_name = cc.constraint_name)
WHERE c.table_name='EMP';

-- 시스템 지정 이름 컬럼 삭제
ALTER TABLE emp
DROP CONSTRAINT SYS_C007373;
    /*
    Table EMP이(가) 변경되었습니다.
    */

-- 제약 조건을 확인합니다.
SELECT c.table_name, cc.column_name, c.constraint_name, c.constraint_type, c.search_condition
FROM user_constraints c JOIN user_cons_columns cc
ON (c.constraint_name = cc.constraint_name)
WHERE c.table_name='EMP';
    /*
    TABLE_NAME  COLUMN_NAME   CON_NAME      CON_TYPE   SEARCH_CONDITION        
    --------------------------------------------------------------------------------------------------------------------------------
        EMP              EMAIL                     SYS_C007368         C          "EMAIL" IS NOT NULL
        EMP              SALARY                  SYS_C007369         C         "SALARY" IS NOT NULL
        EMP              SALARY                  EMP_SAL_CK         C         salary BETWEEN 2500 AND 30000
        EMP              EMPID                     EMP_ID_PK            P          ( null )
    */

-- 부서를 삭제합니다.
DELETE FROM dept
WHERE deptno=10;
    /*
    1 행 이(가) 삭제되었습니다.
    */

-- 직원의 부서를 변경합니다.
UPDATE emp
SET deptno=100;
    /*
    2개 행 이(가) 업데이트되었습니다.
    */

ROLLBACK;
    /*
    롤백완료.
    */

-- 기본 키는 테이블 당 하나뿐이므로 지정하여 삭제할 수 있습니다.
    -- 기본 키를 삭제하는 경우 참조하는 키로 사용을 많이 하고 있으므로 오류가 많이 발생합니다.
    -- CASCADE와 같이 사용하는 경우가 많습니다.
ALTER TABLE emp
DROP primary key;
    /*
    Table EMP이(가) 변경되었습니다.
    */

ROLLBACK;
    /*
    롤백완료.
    */
    
-- 제약 조건을 추가합니다.
ALTER TABLE emp
ADD primary key(empid);
    /*
    Table EMP이(가) 변경되었습니다.
    */
    
-- 제약 조건을 확인합니다.
SELECT c.table_name, cc.column_name, c.constraint_name, c.constraint_type, c.search_condition
FROM user_constraints c JOIN user_cons_columns cc
ON (c.constraint_name = cc.constraint_name)
WHERE c.table_name='EMP';
    /*
    TABLE_NAME  COLUMN_NAME   CON_NAME      CON_TYPE   SEARCH_CONDITION        
    --------------------------------------------------------------------------------------------------------------------------------
        EMP              EMAIL                     SYS_C007368         C          "EMAIL" IS NOT NULL
        EMP              SALARY                  SYS_C007369         C         "SALARY" IS NOT NULL
        EMP              SALARY                  EMP_SAL_CK         C         salary BETWEEN 2500 AND 30000
        EMP              EMPID                     EMP_ID_PK            P          ( null )
    */

-- 삭제 시 기존 값을 NULL 값으로 변경합니다.
ALTER TABLE emp
ADD CONSTRAINT emp_deptno_fk FOREIGN KEY(deptno)
REFERENCES dept(deptno) 
ON DELETE SET NULL;
    /*
    Table EMP이(가) 변경되었습니다.
    */


-- 직원 정보를 조회합니다.
SELECT * FROM emp;
    /*
    EMPID   EMP_NAME  EMAIL SALARY  HIRE_DATE   DEPTNO
    -----------------------------------------------------------------------------------
    1              name03         user    7000        23/08/03          10
    2              name04         user    24000      23/08/03          20
   */