[SQL] 무결성 제약조건

무결성 제약조건 - Data integrity constraint rule

무결성을 보장해 주는 것

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

( 테이블 생성후 컬럼에 규칙 정의하는 것도 가능 )

 

1. NOT NULL

EMPNO NUMBER(4) CONSTRAINT EMP2_EMPNO_NN NOT NULL,

-  해당 컬럼값으로 NULL을 허용하지 않는다.

-  (행단위) 입력시 데이터를 무조건 받겠다!

 

2. UNIQUE

 CONSTRAINT EMP2_EMPNO_UK UNIQUE(EMPNO)

- NULL의 중복은 방지하지 못한다.

- 테이블에서 해당 컬럼값은 항상 유일 무이한 값을 가진다. NULL 허용

- 중복을 허용하지 않겠다.

 

3. PRIMARY KEY (기본키, 주키) not null + unique 동시에 만족

CONSTRAINT EMP2_EMPNO_UK UNIQUE(EMPNO)

                                              DEPT2_PK(이름) 꼭!  <-- PRIMARY KEY로 설정하겠다.

- 해당 컬럼값은 반드시 존재해야 하고 유일해야 한다는 조건

- 테이블 내에서 서로 다른 행을 구분하기 위해 사용

 

4. FOREIGN KEY (외래키, 참조키)

 [CONSTRAINT 제약명 ] FORIEGN KEY (컬럼명2)

                                   REFEPRNCES 참조테이블명 (참조컬럼명)

- 해당 컬럼의 값이 타컬럼의 값을 참조해야만 함 

  즉, 참조되는 컬럼에 존재하지 않는 값은 입력 불가

- 참조되는 컬럼은 unique 또는 primary key 설정이 되어 있어야 함.

- 참조하는 테이블에서 설정 (ex. 사원 테이블의 deptno에 참조키 설정!)

            ---------
          자식테이블
                                                  
- 참조되는 쪽 테이블(부서테이블)의 컬럼은 반드시 unique하거나 또는 primary key여야 함!!
                --------
              부모테이블   
                                  
                                      참조            
     EMP테이블(deptno) ------------------->    DEPT테이블 (deptno)                                  
                                                              10
                                                              20
                                                              30
                                                              40
      사원insert시 (empno,ename,deptno)                                                                                          
        values (8000,'김길동', 30)    ==> 존재하는 부서30에 저장 성공!!                                  
        values (8002,'박길동', 50)    ==> 존재하지 않는 부서50에 저장 실패!!
        
   SCOTT사원의 사번==> 7788
   update emp set deptno=70 where empno=7788;   ==> 존재하지 않는 부서70으로 인해 에러발생!!        

 

   CREATE TABLE 테이블 EMP

   (

      컬럼명 자료형 [CONSTRAINT 제약명 ] REFERENCES 참조테이블명 (참조 컬럼명)

      DEPTNO NUMBER (2)                                               DEPT     (DEPTNO)

   );

   테이블레벨은

   CREATE TABLE 테이블명 EMP

   {

   컬럼명1   자료형,

   컬럼명2DEPTNO 자료형,

   [CONSTRAINT 제약명 ] FORIEGN KEY (컬럼명2)

                                   REFEPRNCES 참조테이블명 (참조컬럼명)

 

    );                                    

5. CHECK

- 해당 컬럼에 저장 가능한 데이터 값의 범위나 사용자 조건을 지정한다.

- 컬럼 레벨로 정의 가능하다. 컬럼레벨? 제약조건의 명시를 테이블 생성시 컬럼정의와 함께 정의

---

ADD NOTNULL이 안되는경우?

테이블 내의 모든 컬럼은 특별한 설정을 하지 않았을경우에 실행이 되지 않는다.

왜? NULL값이 기본적으로 설정되어 있기 때문에 (SET NULL)

---

제약조건 설정하기

1. 제약설정(추가) 
  - 컬럼레벨설정
  create table 테이블명(
        컬럼명1 자료형 [CONSTRAINT 제약명] constraint_type,
        컬럼명2 자료형,
        컬럼명3 자료형
  );
   ex.

   ADD CONSTRAINT DEPT2_PK PRIMARY KEY ( DEPTNO ),

                             DEPT2_PK(이름) 꼭!  <-- PRIMARY KEY (DEPTNO) 로 설정하겠다.

  - 테이블레벨설정
  create table 테이블명(
        컬럼명1 자료형,
        컬럼명2 자료형,
        컬럼명3 자료형,
    [CONSTRAINT 제약명] constraint_type(적용할 컬럼명)
  );
   ex.

   CONSTRAINT EMP2_EMPNO_UK UNIQUE(EMPNO)

  - 테이블생성 후 제약추가
  create table 테이블명(
        컬럼명1 자료형,
        컬럼명2 자료형,
        컬럼명3 자료형
  );
  alter table 테이블명
  ADD [CONSTRAINT 제약명] constraint_type(적용할 컬럼명);
  ex.

  ALTER TABLE EMP2

  ADD CONSTRAINT EMP2_PK PRIMARY KEY (EMPNO) 

 

외래키 추가

 [CONSTRAINT 제약명 ] FORIEGN KEY (컬럼명2)

                                   REFEPRNCES 참조테이블명 (참조컬럼명)

 

 DEPTNO NUMBER(2) REFERENCES DEPT3(DEPTNO) <-- CREATE시 

---

2. 제약삭제
  alter table 테이블명
  DROP CONSTRAINT 제약명;

  ex.

  ALTER TABLE EMP2

  DROP CONSTRAINT EMP_PK;

  alter table 테이블명
  DROP primary key;  

  ex.

  ALTER TABLE EMP2

  DROP PRIMARY KEY;

 

  alter table 테이블명 
  DROP  CONSTRAINT DEPT3_UK;  <-- UNIQUE키명을 삭제 

 

  CASECADE옵션 사용 ( FK 전부 삭제 )

  ALTER TABLE DEPT3

  DROP DONSTRAINT DEPT3_UK CASCADE ;

  - 자동으로 관련된 모든 FK를 먼저 삭제하고 UK를 삭제해줌

  

---

 3. 제약수정 (null <----> not null)
  alter table 테이블명
  MODIFY 컬럼명  [CONSTRAINT 제약명] NOT NULL | NULL ;
   

---

CHECK

- 특정범위 내의 데이터만 입력

- 정해진 데이터만 입력

CHECK (조건식)

---

데이터 딕셔너리 - DD 시스템테이블
- 데이터베이스 자원을 효율적으로 관리하기 위한 다양한 정보를 저장하는
  시스템 테이블.
- 사용자가 테이블을 생성하거나 사용자를 변경하는 등의 작업을 할때
  데이터베이스서버에 의해 자동으로 갱신되는 테이블.
  
<제약조건 확인하기>  
- user_constraints
- desc user_constraints  

OWNER            : 제약조건을 소유한 사용자명
CONSTRAINT_NAME  : 제약조건명
CONSTRAINT_TYPE  : 제약조건타입
TABLE_NAME       : 제약이 속한 테이블명
SEARCH_CONDITION : check제약일 경우 조건으로 사용되어지는 설명.
R_CONSTRAINT_NAME : foreign key경우 어떤 primary key를 참조했는지 정보.

P : primary key
R : foreign key
U : unique
C : check, not null