[oracle] 무결성 제약조건

※데이터 무결성(Data Integrity)

여러가지 의미를 내포하고 있는데,

권한이 있는 사람만 해당 데이터를 볼수 있어야 하며, 데이터의 정확성을 보장한다는 의미를 가진다.

즉, 제대로 된 데이터들이 올바르게 저장될 수 있도록 하기 위해 데이터베이스 측에서 제공하는 기능을 통틀어서 말한다.

이러한 제약조건들은 테이블의 컬럼에 적용되기 때문에 컬럼의 속성이라고 표현할 수 있다.

1.NULL/NOT NULL

제약조건에 포함되지는 않지만 컬럼에 설정되는 값이다.

컬럼에 설정되는 값이기에 ADD, ALTER, DROP 대신 MODIFY를 사용한다.

데이터가 없음을 의미한다. 보통 NULL은 컬럼들의 디폴트 값이다.

CREATE TABLE TEST

(

컬럼 데이터타입

   COL1 VARCHAR2(10) NOT NULL,

   COL2 VARCHAR2(10) NULL

)

NULL값을 직접 넣기 위해서는 NULL 또는 ''을 입력하면 된다. SPACE, 빈 공백과는 다르다.

-.NULL, NOT 입력

ALTER TABLE EMPLOYEE MODIFY  GENDER NOT NULL | NULL;

ALTER TABLE EMPLOYEE MODIFY (GENDER NOT NULL | NULL);

-.NULL, NOT NULL 제거

ALTER TABLE EMPLOYEE MODIFY GENDER NULL;

2.UNIQUE

테이블에 있는 데이터를 유일하게 식별하기 위한 무결성 제약조건 중의 하나이다.

단일 컬럼을 UNIQUE키로 만들수도 있고, 두개 이상의 여러 컬럼을 UNIQUE키로 만들수도 있다.

이런 경우를 복합(composite) UNIQUE라고 한다.

특이한 것은 UNIQUE 제약조건은 NULL을 허용한다.

UNIQUE 조건이 적용된 필드는

중복값을 허용하지 않지만 NULL값은 '값이 없음' 이기에 중복값 비교 대상에서 제외 된다.

그렇기에 UNIQUE 제약조건이 설정된 필드에 NULL이 가능해도 되도록이면 NOT NULL로 처리하도록 설정해야 한다.

즉, UNIQUE의 원래 목적이 테이블내에서 각 데이터의 유일한 값을 식별하기 위한 것이므로 그 목적에 맞게 사용하려면

굳이 NULL을 허용할 필요가 없는것이다.

제약조건 등록 형식: CONSTRAINT 제약조건_명 제약조건_키워드(컬럼 리스트)

UNIQUE : 컬럼에서 값의 유일성을 식별, 유일성 유무 체크 -> UNIQUE 인덱스 생성, UNIQUE 속성, UNIQUE 제약조건

PRIMARY KEY : 테이블에서 가장 대표되는 키. UNIQUE 키 + NOT NULL 속성 -> UNIQUE 인덱스 생성, UNIQUE 속성, PRIMARY KEY 제약조건

CREATE TABLE TEST

(

COL1 VARCHAR2(10) UNIQUE NOT NULL,

COL2 VARCHAR2(10),

제약조건_키워드 제약조건명 제약조건(컬럼리스트)

CONSTRAINT UK_COL2 UNIQUE(COL2)

);

-.UNIQUE 추가

ALTER TABLE TEST

ADD CONSTRAINTS UK_COL3_COL4 UNIQUE(COL3, COL4);

-.UNIQUE 삭제

ALTER TABLE TEST

DROP CONSTRAINTS UK_COL3_COL4;

3.기본키(PRIMARY KEY)

기본키는 UNIQUE와 동일하게 한 테이블에 있는 데이터들을 유일하게 식별하기 위한 무결성 제약조건이다.

하지만 다른점이 있다면

기본키가 걸린 컬럼은 반드시 NOT NULL 속성을 가진다는 것이다. 기본키 = UNIQUE 인덱스 + NOT NULL 속성

기본키를 만들면 UNIQUE 인덱스가 생성되어 UNIQUE한 속성을 갖게 되는 것이지 UNIQUE 제약조건이 생성되는 것은 아니다.

제약 조건은 PRIMARY KEY 제약조건이 생성된다.

즉, 기본키를 만들면 PRIMARY KEY 제약조건이, UNIQUE KEY를 만들면 UNIQUE 제약조건이 만들어지며 둘 다 UNIQUE 인덱스가 생성된다.

-.제약조건을 생성하는 방법

①인라인 방식 : 컬럼과 같은 라인에서 선언하는 방식

②아웃라인 방식: CONSTRAINT 키워드를 이용해서 명시적으로 이름을 지정한 뒤 각각 선언하는 방식

생성된 제약 조건은 USER_CONSTRAINTS 시스템 뷰로 조회하여 확인할 수 있다.

③ALTER TABLE을 이용하는 방식: CONSTRAINTS 키워드를 이용해서 정의한다.

CREATE TABLE TEST

(

컬럼 데이터타입 제약조건 리스트  - inline 표기방식

    COL1 VARCHAR2(10) PRIMARY KEY,

    COL2 VARCHAR2(10) UNIQUE,

    COL3 VARCHAR2(10)    NOT NULL,

    COL4 VARCHAR2(10)    NOT NULL

)

CREATE TABLE TEST

(

컬럼 데이터타입 제약조건_리스트  - inline 표기방식

    COL1 VARCHAR2(10),

    COL2 VARCHAR2(10) UNIQUE ,

    COL3 VARCHAR2(10) NOT NULL,

    COL4 VARCHAR2(10) NOT NULL,

제약조건_키워드 제약조건명 제약조건(컬럼리스트) - outline 표기방식

CONSTRAINT  PK_COL1  PRIMARY KEY(COL1)

    CONSTRAINT  UK_COL3_COL4 UNIQUE(COL3, COL4)

)  

 

CREATE TABLE EMPLOYEE

(

    EMP_ID   VARCHAR2(10) CONSTRAINT PK_EMP_ID PRIMARY KEY NOT NULL,

    EMP_NAME VARCHAR2(20),

    GENDER   VARCHAR2(1)  DEFAULT '1' 

                          CONSTRAINT CHK_GENDER CHECK(GENDER IN ('1', '2')),

    DEPT_ID  VARCHAR2(10)

)

-.PRIMARY KEY 추가

ALTER TABLE TEST

ADD CONSTRAINTS PK_COL1 PRIMARY KEY(COL1, COL2);

-.PRIMARY KEY 삭제

ALTER TABLE TEST

DROP CONSTRAINTS PK_COL1

4.외래키(FOREIGN KEY)

관계형 DB에서 '관계' 라는 용어는 테이블 간의 관계를 의미한다. 이러한 관계는 테이블 간에 공통값을 가진 컬럼을 통해서 이루어진다.

-.외래키에는 몇가지 제약사항

ⓐ외래키를 만들기 전에

반드시 부모 테이블이 먼저 생성되어 있어야 하며, 참조하는 부모 테이블의 컬럼은 반드시 기본키 혹은 UNIQUE키 이어야 한다.

ⓑ복합 외래키를 사용할 경우 참조하는 부모 테이블의 기본키나 UNIQUE키도 반드시 복합키가 되어야 한다.

이때 키를 구성하는 컬럼의 순서와 개수도 모두 동일해야 한다.

ⓒ자식 테이블에서 부모 테이블의 특정 값을 사용하고 있을 경우에는 부모 테이블에서 그 값을 삭제할 수 없다.

만약 부모 테이블에 존재하는 값을 삭제하고자 할 경우에는

자식 테이블에 존재하는 데이터를 먼저 삭제한 뒤 부모 테이블의 값을 삭제해야 한다.

그러나 예외적으로 외래키 자체를 사용 불가능한 상태(disable)로 만든다면 부모 테이블에서 삭제할 수 있다.

CREATE TABLE EMPLOYEE

(

EMP_ID   VARCHAR2(10) CONSTRAINT PK_EMP_ID PRIMARY KEY NOT NULL,

EMP_NAME VARCHAR2(20),

GENDER   VARCHAR2(1)  DEFAULT '1' 

CONSTRAINT CHK_GENDER CHECK(GENDER IN ('1', '2')),

DEPT_ID  VARCHAR2(10),

/*outline 표기방식*/

제약조건_키워드 제약조건명 외부키_키워드(리스트) 참조_키워드 참조테이블명(리스트)

CONSTRAINT  FK_DEPT_ID FOREIGN KEY(DEPT_ID) REFERENCES DEPARTMENT(DEPT_ID)

)

-.FOREIGN KEY의 조건

반드시 참조하는 부모 테이블의 컬럼은 PRIMARY KEY or UNIQUE KEY 이어야 하며, 컬럼의 수와 순서도 모두 동일해야 한다.

-.외래키 추가

ALTER TABLE EMPLOYEE

ADD CONSTRAINTS FK_DEPT_ID FOREIGN KEY(DEPT_ID) REFERENCES DEPARTMENT(DEPT_ID)

-.PRIMARY KEY 삭제

ALTER TABLE EMPLOYEE DROP CONSTRAINTS FK_DEPT_ID

-.제약조건 사용/사용중지 

ALTER TABLE EMPLOYEE

DISABLE/ENABLE CONSTRAINTS FK_DEPT_ID

5.CHECK

CHECK 제약조건은 입력되는 값을 체크하여 일정한 조건에 해당되는 값만 입력될수 있게 하는 제약조건이다.

CREATE TABLE TEST

(

    COL1 VARCHAR2(1) CHECK(COL1 IN('1', '2')),

    COL2 VARCHAR2(10),

    COL3 VARCHAR2(10),

    COL4 VARCHAR2(10),

제약조건_키워드 제약조건명 제약조건(컬럼 체크 조건) - outline 표기방식

    CONSTRAINT  CHK_COL3 CHECK(COL3 IN('남성', '여성'))

)

-.제약조건 추가

ALTER TABLE EMPLOYEE

ADD CONSTRAINTS CHK_GENDER CHECK(GENDER IN ('1', '2')

-.제약조건 삭제

ALTER TABLE EMPLOYEE DROP CONSTRAINTS CHK_GENDER [CASCADE]

ALTER TABLE EMPLOYEE DROP CONSTRAINTS CHK_GENDER;

6.DEFAULT

제약조건에 포함되지는 않지만 컬럼에 설정되는 값이다. 컬럼에 설정되는 값이기에 ADD, ALTER, DROP 대신 MODIFY를 사용한다.

컬럼에 특정한 값을 디폴트값으로 설정하면 테이블에 데이터를 입력할 때 해당 컬럼에 값을 입력하지 않을 경우

디폴트로 설정한 값이 기본적으로 자동 입력된다.

CREATE TABLE EMPLOYEE

(

EMP_ID   VARCHAR2(10),

GENDER   VARCHAR2(1) ,

   /*outline 표기방식*/

제약조건_키워드 제약조건명 외부키_키워드(리스트) 참조_키워드 참조테이블명(리스트)

   CONSTRAINT  FK_DEPT_ID FOREIGN KEY(DEPT_ID) REFERENCES DEPARTMENT(DEPT_ID)

)

-.속성값 디폴트로 변경

ALTER TABLE 테이블명 MODIFY 값을변경할_컬럼명 DEFAULT 디폴트값

ALTER TABLE EMPLOYEE MODIFY GENDER  DEFAULT 'M';

-.디폴트 속성 삭제

ALTER TABLE EMPLOYEE

ALTER COLUMN GENDER DROP DEFAULT;

-.컬럼추가 및 기본값 설정

ALTER TABLE ADD 추가할_컬럼명 컬럼_타입 DEFAULT 디폴트값

ALTER TABLE ADD INSERT_DATE DATE DEFAULT SYSDATE