ORACLE 06. DDL (CREATE)_ 제약조건(CONSTRAINTS)

< 제약조건 CONSTRAINTS >

   - 원하는 데이터값(유효한 형식의 값)만 유지하기 위해서 특정 컬럼에 부여하는 제약 
   - 데이터 무결성 보장을 목적으로 함 

* 제약조건의 종류 : NOT NULL , UNIQUE, CHECK(조건), PRIMARY KEY, FOREIGN KEY

* 제약조건을 부여하는 방식 : 컬럼레벨방식 / 테이블레벨방식 

---

1. NOT NULL 제약조건 

- 해당 컬럼에 반드시 값이 존재해야만 할 경우 ( 즉, 해당 컬럼에 절대 NULL이 들어와서는 안되는 경우)
- 삽입/ 수정시 NULL값을 허용하지 않도록 제한함
- NOT NULL 제약조건은 컬럼레벨방식만 사용가능 

CREATE TABLE MEM_NOTNULL(
    MEM_NO NUMBER NOT NULL,
    MEM_ID VARCHAR2(20) NOT NULL,
    MEM_PWD VARCHAR2(20) NOT NULL,
    MEM_NAME VARCHAR2(20) NOT NULL,
    GENDER CHAR(3),
    PHONE VARCHAR2(13),
    EMAIL VARCHAR2(50)
);

INSERT INTO MEM_NOTNULL VALUES(2,'user02',NULL,'김말순','여',NULL,'aa@naver.com');

>> MEM_PWD 컬럼은 NOT NULL 제약조건으로 NULL값이 들어갈 수 없기 때문에 오류 발생 

 

2. UNIQUE 제약조건

 - 해달컬럼에 중복된 값이 들어가서는 안될 경우
    컬럼값에 중복값을 제한하는 제약조건
- 삽입/ 수정시 기존에 있는 데이터값 중 중복값이 있을 경우 오류 발생

> 컬럼레벨방식 

CREATE TABLE MEM_UNIQUE(
    MEM_NO NUMBER NOT NULL ,
    MEM_ID VARCHAR2(20) NOT NULL UNIQUE, --컬럼레벨방식 
    MEM_PWD VARCHAR2(20) NOT NULL,
    MEM_NAME VARCHAR2(20) NOT NULL,
    GENDER CHAR(3),
    PHONE VARCHAR2(13),
    EMAIL VARCHAR2(50)
);

>테이블레벨방식

CREATE TABLE MEM_UNIQUE(
    MEM_NO NUMBER NOT NULL ,
    MEM_ID VARCHAR2(20) NOT NULL, 
    MEM_PWD VARCHAR2(20) NOT NULL,
    MEM_NAME VARCHAR2(20) NOT NULL,
    GENDER CHAR(3),
    PHONE VARCHAR2(13),
    EMAIL VARCHAR2(50),
    UNIQUE (MEM_ID) -- 테이블레벨 방식 
);

INSERT INTO MEM_UNIQUE VALUES(1,'user01','pass01','홍길동',NULL,NULL,NULL);
INSERT INTO MEM_UNIQUE VALUES(2,'user01','pass02','강개똥',NULL,NULL,NULL);

>> MEM_ID에 중복값을 허용하지않는 UNIQUE제약조건이 부여되어있기때문에 오류 발생
     오류 구문을 제약조건명으로 알려줌 => 쉽게 파악하기 어려움 
--> 제약조건 부여시 별도로 제약조건명을 지정해주지 않으면 시스템에서 알아서 임의로 제약조건명 부여

 * 제약조건 부여시 제약조건명 지어주는 방법

 >컬럼레벨방식
    CREATE TABLE 테이블명(
        컬럼명 자료형 [CONSTRAINT 제약조건명] 제약조건,
        컬럼명 자료형 제약조건,
        ...
    );
    
    >테이블 레벨방식
        CREATE TABLE 테이블명(
         컬럼명 자료형,
         컬럼명 자료형,
         [CONSTRAINT 제약조건명] 제약조건 (컬럼명)
     );

  예시)

CREATE TABLE MEM_UNIQUE(
    MEM_NO NUMBER CONSTRAINT MEMNO_NN NOT NULL ,
    MEM_ID VARCHAR2(20) CONSTRAINT MEMID_NN NOT NULL, 
    MEM_PWD VARCHAR2(20) CONSTRAINT MEMPWD_NN NOT NULL,
    MEM_NAME VARCHAR2(20) NOT NULL,
    GENDER CHAR(3),
    PHONE VARCHAR2(13),
    EMAIL VARCHAR2(50),
    CONSTRAINT MEMID_UQ UNIQUE (MEM_ID) 
);

  

3.  CHECK (조건식) 제약조건 

 - 해당 컬럼에 들어올 수 있는 값에 대한 조건을 제시해 둘 수 있음
  - 해당 조건에 만족하는 데이터 값만 담길 수 있음 

CREATE TABLE MEM_CHECK(
    MEM_NO NUMBER NOT NULL,
    MEM_ID VARCHAR2(20) NOT NULL UNIQUE,
    MEM_PWD VARCHAR2(20) NOT NULL,
    MEM_NAME VARCHAR2(20) NOT NULL,
    GENDER CHAR(3) CHECK(GENDER IN ('남','여')), --컬럼레벨방식 
    PHONE VARCHAR2(13),
    EMAIL VARCHAR2(50)
    --CHECK(GENDER IN ('남','여')) --테이블레벨방식  
);

INSERT INTO MEM_CHECK
VALUES (2,'user02','pass02','홍길여','ㅇ',null,null);

>> CHECK 제약 조건에 위배되어 오류 발생 

 

 

4. PRIMARY KEY(기본키)  제약조건 

 - 테이블에서 각 행들을 식별하기 위해 사용될 컬럼에 부여하는 제약조건 (식별자의 역할)
    EX) 회원번호, 학번, 사번, 부서코드, 직급코드, 주문번호, 예약번호, 운송장번호, ..
    
  - PRIMARY KEY 제약조건을 부여하면 그 컬럼에 자동으로 NOT NULL + UNIQUE 의미 
    
    *유의사항 : 한 테이블당 단 한개만 설정 가능

CREATE TABLE MEM_PRI(
    MEM_NO NUMBER CONSTRAINT MEMNO_PK PRIMARY KEY, --컬럼레벨방식 --기본키
    MEM_ID VARCHAR2(20) NOT NULL UNIQUE, --대체키 
    MEM_PWD VARCHAR2(20) NOT NULL,
    MEM_NAME VARCHAR2(20) NOT NULL,
    GENDER CHAR(3) CHECK(GENDER IN ('남','여')),
    PHONE VARCHAR2(13),
    EMAIL VARCHAR2(50)
    --, CONSTRAINT MEMNO_PK PRIMARY KEY(MEM_NO) --> 테이블레벨방식 
);

VALUES(1,'user01','pass01','강개순','여','010-1111-2222',null);
INSERT INTO MEM_PRI 
VALUES(1,'user02','pass02','홍길여','여',null,null);

>> PRIMARY KEY 컬럼에 중복값 담을 수 없음 -> UNIQUE제약 조건 위배 

INSERT INTO MEM_PRI 
VALUES(null,'user02','pass02','홍길여','여',null,null);

>>PRIMARY KEY컬럼에  NULL값 담을 수 없음 -> NOT NULL 제약조건 위배 

 

* 두 컬럼을 묶어 PRIMARY KEY 제약조건 부여할 수 있음 (복합키)
-예시) 어떤 회원이 어떤상품을 언제 찜했는지에  대한 데이터를 보관하는 테이블 

CREATE TABLE TB_LIKE(
    MEM_NO NUMBER,
    PRO_NAME VARCHAR2(10),
    LIKE_DATE DATE,
    PRIMARY KEY(MEM_NO,PRO_NAME)
);
INSERT INTO TB_LIKE VALUES(1,'A',SYSDATE);
INSERT INTO TB_LIKE VALUES(1,'B',SYSDATE);
INSERT INTO TB_LIKE VALUES(1,'A',SYSDATE);
-->> MEM_NO 와 PRO_NAME 두 컬럼이 묶여 중복판별되기 때문에 마지막 컬럼은 INSERT되지 않음

 

 

5.FOREIGN KEY (외래키)  제약조건

- 참조 무결성을 위한 제약조건으로 참조된 다른 테이블이 제공한 값만 사용하도록 제한을 거는것
   -->다른 테이블을 참조한다고도 표현( 부모테이블 -|-----<---자식테이블 )

* 참조할 컬럼명 생략시 참조할 테이블에 PRIMARY KEY로 지정된 컬럼으로 자동 매칭 

[표현법]

 > 컬럼레벨방식 
      컬럼명 자료형 [CONSTRANINT 제약조건명] REFERENCES 참조할 테이블명[(참조할 컬럼명)]
      
> 테이블레벨방식
     [CONSTRANINT 제약조건명] FOREIGN KEY (컬럼명) REFERENCES 참조할 테이블명[(참조할컬럼명)]

 

예시)

CREATE TABLE MEM(
    MEM_NO NUMBER PRIMARY KEY,
    MEM_ID VARCHAR2(20) NOT NULL UNIQUE,
    MEM_PWD VARCHAR2(20) NOT NULL,
    MEM_NAME VARCHAR2(20) NOT NULL,
    GENDER CHAR(3) CHECK(GENDER IN ('남','여')),
    PHONE VARCHAR2(13),
    EMAIL VARCHAR2(50),
    GRADE_ID NUMBER REFERENCES MEM_GRADE--(GRADE_CODE) 생략시 PRIMARY KEY로 지정된 컬럼 자동 지정 -> 컬럼레벨방식 
    --, FOREIGN KEY(GRADE_ID) REFERENCES MEM_GRADE(GRADE_CODE) --> 테이블레벨방식
);

INSERT INTO MEM
VALUES(3,'user03','pass03','강개순',null,null,null,40);

>> 참조하고있는 테이블 (MEM_GRADE)의 (GRADE_CODE) 값에 40이 존재하지 않음 (PARENT KEY 찾을수 없다는 오류) 

 

* 부모테이블(참조하고있는 테이블) 데이터 값 삭제시
-> 자식테이블( 참조하는 테이블) 에 이미 사용되고 있는 값이 있을 경우 부모테이블로부터 삭제가 안되는 
"삭제 제한" 옵션이 걸려있음 

-> 자식 테이블 생성시 외래키 제약조건 부여할 때 " 삭제옵션" 지정가능
 

*삭제옵션 : 부모 테이블의 데이터를 삭제시 그 데이터를 사용하고 있는 자식 테이블의 값을 어떻게 처리할 것인지 지정하는 옵션 
    -ON DELETE RESTRICTED (기본값) : 삭제제한옵션, 자식데이터로 쓰이는 부모데이터는 삭제 아예 안되게끔하는 옵션
    -ON DELETE SET NULL : 부모데이터 삭제시 해당 데이터를 쓰고있는 자식데이터의 값을 NULL로 변경시키는 옵션 
    -ON DELETE CASCADE : 부모데이터 삭제시 해당 데이터를 쓰고있는 자식데이터의 값도 같이 삭제시키는 옵션

1) ON DELETE SET NULL 옵션 

CREATE TABLE MEM(
    MEM_NO NUMBER PRIMARY KEY,
    MEM_ID VARCHAR2(20) NOT NULL UNIQUE,
    MEM_PWD VARCHAR2(20) NOT NULL,
    MEM_NAME VARCHAR2(20) NOT NULL,
    GENDER CHAR(3) CHECK(GENDER IN ('남','여')),
    PHONE VARCHAR2(13),
    EMAIL VARCHAR2(50),
    GRADE_ID NUMBER REFERENCES MEM_GRADE ON DELETE SET NULL
);

>> 부모 테이블 값 삭제시  자식테이블 값 NULL로 변경됨

2) ON DELETE CASCADE 옵션

CREATE TABLE MEM(
    MEM_NO NUMBER PRIMARY KEY,
    MEM_ID VARCHAR2(20) NOT NULL UNIQUE,
    MEM_PWD VARCHAR2(20) NOT NULL,
    MEM_NAME VARCHAR2(20) NOT NULL,
    GENDER CHAR(3) CHECK(GENDER IN ('남','여')),
    PHONE VARCHAR2(13),
    EMAIL VARCHAR2(50),
    GRADE_ID NUMBER REFERENCES MEM_GRADE ON DELETE CASCADE
);

>> 부모테이블 값 삭제시 자식테이블값도 같이 삭제됨 

 

< 테이블 다 생성한 후 뒤늦게 제약조건 추가할 때 >

[표현법]

 ALTER TABLE 테이블명 변경할내용 ;

>> 변경할 내용
    - PRIMARY KEY : ADD PRIMARY KEY (컬럼명);
    - FOREIGN KEY : ADD FOREIGN KEY (컬럼명) REFERENCES 참조할테이블[(참조할 컬럼)] [삭제옵션];
    - UNIQUE           : ADD UNIQUE (컬럼명);
    - CHECK            : ADD CHECK(컬럼에 대한 조건식);
    - NOT NULL       : MODIFY 컬럼명 NOT NULL;   
                                  MODIFY 컬렴명 NULL;

예시 ) EMPLOYEE_COPY 테이블에 PRIMARY KEY 제약조건 추가 (EMP_ID)

ALTER TABLE EMPLOYEE_COPY ADD PRIMARY KEY (EMP_ID);

예시) EMPLOYEE 테이블에 DEPT_CODE 컬럼에 FOREIGN KEY 제약조건 추가
       (참조하는 테이블 (부모) : DEPARTMENT(DEPT_ID))

ALTER TABLE EMPLOYEE ADD FOREIGN KEY(DEPT_CODE) REFERENCES DEPARTMENT;--(DEPT_ID);