2012.01.11 SQL 7일차(무결성제약조건 Constraint) ;N/N, PRIMARY KEY, FOREIGN KEY, UNIQUE KEY, CHECK, 관련 딕셔너리

무결성 제약조건(Constraint)
; 조건에 맞지않은 데이터는 입력되지 않게 하고, 맞는 데이터만 입력되게하는 제약조건

Constraint의 개념 : 데이터의 정확성과 일관성을 보장하기 위해 테이블 생성시에 각 칼럼에 대해 정의 하는 규칙을 의미

특징
1. 입력된 데이터가 조건에 맞나 검사해보기 떄문에 당연히 안쓴것보다는 속도가 저하
2. 규칙은 컬럼별로(컬럼별로 검문소를 하나씩 세운다고 생각하세요)
3.  constraint 생성시 딕셔너리에 저장된다, (예 : user_constraints 테이블, user_cons_columns )
4. 일시적으로 활성, 비활성할 수 있다. 

Constraint의 종류
1. NOT NULL : NULL이 아닌 값만 허용

2.  Unique Key(고유키) : 중복되지 않은 유일한 값,
                   입력 시 기존에 있던 모든 값과 비교를 해야하기 때문에 속도저하를 막기위해서 oracle에서 자동으로 INDEX생성
                   (NULL값 입력 가능, 단 unique해야하기 떄문에 한번만 입력가능)

3. Primary Key(기본키) : Unique Key + Not Null의 특징을 가짐, 테이블을 대표하는 키로 절대 바뀌지않는 값을 기본키로 설정해야한다.

4. Foreign Key(참조) : 아래 따로 설명, Reference Key가 될 col은 무조건 Unique Key나 Primary Key가 되어야 합니다.

5. CHECK : 어떤 일정한 사이의 값만 받고 싶을 때 사용 
(예 : CONSTRAINT subject_term_ck CHECK (term between 1 and 5)  ; 1~5사이의 값만 받는 컬럼생성) 


Foreign Key

설명하기 위해 아래 테이블 들이 있다고 가정하고 생각해봅시다

사원table  <--- Child(자식) 테이블(참조하는 테이블)
    사번     NAME           DEPTNO  <--- Foreign Key(외래키)
---------- ---------- ----------
     001     전인하            1000
     002     이동훈            2000
 

 
부서table <--- Parent(부모) 테이블(기준이 되는 테이블)
DEPT_NAME DEPTNO  <--- Reference Key(참조키)
------------- ------ 
경영지원      1000    
재무           2000   
총무           3000   

여기서 사원테이블에 값을 insert할때마다 부서테이블의 deptno를 참조해서
존재하면 사원테이블에 값을 넣을 수 있게 하고 싶다고 생각합시다.
이럴 때 사용하는 것이 Foreign Key입니다.

기준이 되는 테이블을 부모테이블이라고 하고 부모테이블의 기준칼럼을 Reference Key라고 지칭하며,
참조하는 테이블을 자식테이블이라고 하고, 참조하는 칼럼을 Foreign Key라고 합니다.

여기서 사원table에 사번, NAME, DEPTNO가 (003, 이준원, 4000) 인 자료를 입력한다고 생각하면,
부모테이블에서 DEPTNO을 검색해보고 4000되는 값이 없으므로 위 자료는 '땍' 하면서 입력을 하지 못합니다.

위 상황을 보면 일단 자료를 입력할때 마다 부모테이블에서 검색해봐야 하므로 당연히 속도는 저하가 됩니다.

속도저하를 해결하기 위해서는 자식테이블의 Foreign Key와 부모테이블의 Reference Key 둘다 Index가 생성되어 있어야하고,
왠만하면 잘 사용하지 않으려고 하는 곳도 많습니다.

생성방법
( http://gyh214.tistory.com/61 을 참조하세요)
1. 테이블을 만듬과 동시에 제약조건 생성

문법)

    CREATE TABLE [schema.] table

    (column datatype [DEFAULT expression]

    [column_constraint],

    [table_constraint] [,…])

예제 :

create table member
(
userid varchar2(10)
    CONSTRAINT member_id_pk PRIMARY KEY,
username varchar2(20)
    CONSTRAINT member_name_nn NOT NULL,
idnum varchar2(13)
    CONSTRAINT member_idnum_nn NOT NULL
    CONSTRAINT member_idnum_uk UNIQUE 
); 

(약식으로 userid varchar2(10) PRIMARY KEY 로 바로 제약조건을 생성할 수 있지만, 약식으로 생성하게 되면
constraint name을(위 예제에서는 member_id_pk,  member_name_nn....) 내 마음대로 지정할 수가 없고, ORACLE에서 자동으로
지정해버리므로
결국엔, 관리할때 불편합니다. (제약조건을 비활성화, 활성화 등등 그런 상황)

생성시 주의/특징 :
1. ,의 위치에 주의
2. 하나의 컬럼에 여러개의 CONSTRAINT를 생성할 수 있습니다.  
3. Primary Key의 경우는 1개의 테이블당 딱 1개만 생성가능합니다. 

2. 테이블을 만들고 나서 나중에 제약조건 추가(Not NULL의 경우 변경)
아래에서 사용한 constraint_name은 사용자가 나중에 관리를 위해서 임의로 부여하는 이름입니다.

1) Primary, Unique의 경우
ALTER TABLE table_name

ADD CONSTRAINT constraint_name CONSTRAINT종류 (col_name);
** CONSTRAINT종류에 Primary key 는 primary key(col_name);   Unique key는 unique(col_name); 입니다.
왜 유니크는 key를 안 붙여놨는지, 참 이상하네요 ㅋ


2) CHECK의 경우(위의 경우와 같은데 col_name쪽에 between 삽입)
ALTER TABLE table_name

ADD CONSTRAINT constraint_name CONSTRAINT종류 (col_name between 1 and 100);

 

3) 참조의 경우

ALTER TABLE child_table_name

ADD CONSTRAINT constraint_name

FOREIGN KEY(foreignkey_col)

REFERENCES parent_table_name(referencekey_col);

4) NOT NULL(N/N)의 경우 : Modify사용

ALTER TABLE table_name

MODIFY (col_name CONSTRAINT constraint_name NOT NULL);

삭제방법 

ALTER TABLE table_name
DROP CONSTRAINT constraint_name

* 강좌 테이블의 subject_term_ck 무결성 제약 조건을 삭제하여라.

ALTER TABLE subject			
DROP CONSTRAINT subject_term_ck; 

제약조건 비활성화, 활성화
1) 제약조건의 비활성화

    문법) ALTER TABLE table

DISABLE CONSTRAINT constraint_name [cascade] ;

    사용예)

   수강 테이블의 sugang_pk, sugang_studno_fk 무결성 제약조건을 비활성화

   하세요.

SQL> ALTER TABLE sugang
	DISABLE CONSTRAINT sugang_pk;
SQL> ALTER TABLE sugang
 	DISABLE CONSTRAINT sugang_studno_fk;

2) 활성화

 문법)

   ALTER TABLE table

   ENABLE [NOVALIDATE] CONSTRAINT constraint_name [CASCADE];

  * NOVALIDATE : 기존 데이터는 적용시키지 않고 새로 입력되거나 수정되는

                          데이터에만 적용시킨다는 의미.

 사용예)

  수강 테이블의 sugang_pk, sugang_studno_fk 무결성 제약조건을 활성화하세요.

SQL> ALTER TABLE sugang
 	ENABLE CONSTRAINT sugang_pk;
SQL> ALTER TABLE sugang
	ENABLE CONSTRAINT sugang_studno_fk; 

제약조건 관련 딕셔너리
( http://gyh214.tistory.com/61 마지막 부분 참조) 
 

* USER_CONSTRAINTS : 제약 조건이 정의된 테이블 이름, 제약조건 이름, 제약 조건 종류 및 활성화 상태 정보 저장
 

* USER_CONS_COLUMNS : 제약조건 정의된 칼럼과 제약조건 이름 저장