[SQL 23] 제약 조건 - NOT NULL, UK(Unique Key), CHECK

샘플 스키마 : HR

설명하는 과정에서 예제로 주어진 문장은 눈으로 보지만 말고 직접 실행시켜 보길 권한다.

No. 01
제약 조건

제약 조건이란 테이블 단위에서 데이터의 무결성을 보장해주는 규칙이다. 제약 조건은 테이블에 데이터가 입력, 수정, 삭제되거나 테이블이 삭제, 변경될 경우 잘못된 트랜잭션이 수행되지 않도록 결함을 유발할 가능성이 있는 작업을 방지하는 역할을 담당한다.

◆ 테이블 단위에서 정의되고 적용된다.

◆ 속성이 존재하는 겨우 테이블의 삭제를 막아준다.

◆ 자료가 삽입, 갱신, 삭제될 때마다 규칙이 적용된다.

◆ 일시적으로 활성화하거나 비활성화하는 것이 가능하다.

◆ USER_CONSTRAINTS, USER_CONS_COLUMNS 딕셔너리에서 검색한다.

◆ 제약 조건은 개체처럼 관리되므로 반드시 이름이 필요하다. 제약 조건에 이름을 정의하지 않으면 오라클 서버가 자동으로 SYS_Cn형태의 이름을 붙인다.

◆ 제약 조건은 다음 두 경우에 생성할 수 있다.

 - 테이블이 생성될 때 동시에

 - 테이블이 만들어진 후

오라클에서 제공되는 제약조건

• NOT NULL
• UNIQUE KEY(UK)
• PRIMARY KEY(PK)
• FOREIGN KEY(FK)
• CHECK

기본 구조

CREATE TABLE [스키마.]테이블이름(

컬럼 데이터타입 [DEFAULT default값] [컬럼 레벨 제약조건],

…

[테이블 레벨 제약조건],

…

);

컬럼 레벨 제약조건

컬럼 [CONSTRAINT 제약_조건_이름] 제약조건,

테이블 레벨 제약조건

컬럼, …

[CONSTRAINT 제약_조건_이름] 제약조건 (컬럼),

제약 조건을 정의하는 방법은 두가지 방법이 제공된다.

· 컬럼 레벨 정의 : 하나의 컬럼을 정의하면서 같이 정의하는 방법

· 테이블 레벨 정의 : 테이블 생성 명령어 마지막에 기술하는 방법

둘 중 어떤 것을 사용해도 상관없지만 테이블 레벨 정의를 사용하는 것이 가독성이 높다.

주의할 것은 NOT NULL 제약조건은 컬럼 레벨 정의만 할 수 있다.

제약 조건 이름

제약조건에 이름을 붙이지 않아도 오라클 서버가 자동으로 생성하지만 직접 이름을 붙여주는 것이 좋다. 제약조건 이름에는 테이블이름, 컬럼이름, 제약조건이 포함되도록 하는 것이 좋다. 나중에 제약 조건 이름만 보더라도 어떤 테이블의 어떤 컬럼에 제약조건이 있는지 한눈에 알아 볼 수 있도록 하기 위해서이다. '테이블이름_컬럼이름_제약조건'과 같이 짓는 것이 좋다.

위처럼 이름을 지으면 여러 사람이 같이 작업할 때 다른 사람이 만든 테이블이라도 한 번에 알아 볼 수 있다. 제약조건이름을 꼭 지어야 한다고 하지는 않지만 실무에서는 암묵적으로 위와 같은 형태로 이름을 지어놓는 것이 예의이다.

◈ 예제

▶ 컬럼 레벨 제약조건 :

create table emp(

eno number(6) constraint emp_eno_pk primary key,

first_name varchar2(20),

gender varchar2(2) constraint emp_gender_nn not null

);

Table EMP이(가) 생성되었습니다.

drop table emp cascade constraint;

Table EMP이(가) 삭제되었습니다.

▶ 테이블 레벨 제약조건 :

create table emp(

eno number(6),

first_name varchar2(20),

gender varchar2(2) constraint emp_gender_nn not null,

constraint emp_eno_pk primary key (eno)

);

Table EMP이(가) 생성되었습니다.

drop table emp cascade constraint;

Table EMP이(가) 삭제되었습니다.

No. 02
NOT NULL

NOT NULL 설정

CREATE TABLE 테이블(

컬럼 데이터타입 CONSTRAINT 제약_조건_이름 NOT NULL,

…

• 테이블 레벨 정의가 불가능하다.
• 지정된 컬럼은 널 값을 허용하지 않는다.
• 반드시 널 값이 필요한 컬럼을 제외하고 가능한 모든 컬럼에 널 값을 허용하지 않는 것이 좋다.

insert into departments(department_id, manager_id, location_id)

values (400, 200, 1700);

오류 보고 -

ORA-01400: cannot insert NULL into ("HR"."DEPARTMENTS"."DEPARTMENT_NAME")

DEPARTMENT_NAME 컬럼에는 NOT NULL 제약조건이 정의되어 있다.

그러므로 DEPARTMENT_NAME 컬럼에는 값을 넣지 않으면 위와 같은 오류가 발생한다.

No. 03
UNIQUE KEY(UK)

Unique Key 설정

CREATE TABLE 테이블(

…

CONSTRAINT 제약_조건_이름 UNIQUE (컬럼) );

CREATE TABLE 테이블(

컬럼 데이터타입 CONSTRAINT 제약_조건_이름 UNIQUE,

…

• UK(Unique Key)를 정의한다.
• 중복된 값을 허용하지 않지만 여러 개의 널 값은 허용한다.
• 널 값은 비교가 불가능함으로 여러 개가 있어도 중복된 값이 아니다.
• 고유키로 불린다.
• 고유 인덱스(Unique Index)가 자동으로 생성된다.

위와 같이 테이블에 UNIQUE 제약조건이 정의되어 있는 컬럼이 있다고 할 때, 위의 두 데이터를 순서대로 넣는다고 하자. 첫 번째 데이터는 오류없이 삽입될 것이지만, 두 번째 데이터는 오류가 발생할 것이다. 첫 번째 데이터를 삽입했을 때 이미 'JSMITH'라는 EMAIL이 존재하기 때문에 두 번째 데이터의 'JSMITH' EMAIL은 UNIQUE 제약조건에 위배되기 때문이다.

◈ 예제

create table emp(

employee_id number(6),

last_name varchar2(20) not null,

email varchar2(25),

constraint emo_email_uk unique (email)

);

또는

create table emp(

employee_id number(6)

last_name varchar2(20) not null,

email varchar2(25) constraint emo_email_uk unique

);

Table EMP이(가) 생성되었습니다.

insert into emp values (208, 'Smith', 'JSMITH');

1 행 이(가) 삽입되었습니다.

insert into emp values (209, 'Asumin', 'JSMITH');

오류 보고 -

ORA-00001: unique constraint (HR.EMO_EMAIL_UK) violated

drop table emp cascade constraint;

Table EMP이(가) 삭제되었습니다.

No. 04
CHECK

Check 설정

CREATE TABLE 테이블(

…

CONSTRAINT 제약_조건_이름 CHECK (조건) );

CREATE TABLE 테이블(

컬럼 데이터타입 CONSTRAINT 제약_조건_이름 CHECK (조건),

…

• 행에 입력될 데이터의 조건을 정의한다.
• 조건은 WHERE 절에 기술하는 조건 형식과 동일하다.
• 다음과 같은 표현은 허용되지 않는다.
1. CURRVAL, NEXTVAL, LEVEL, ROWNUM pseudocolumn 에 대한 참조
2. SYSDATE, UID, USER, USERENV 함수에 대한 호출]
3. 다른 행의 다른 값을 참조하는 질의

◈ 예제

create table emp(

employee_id number(6),

sal number(8, 2),

last_name varchar2(20) not null,

constraint emp_sal_ck check (sal > 1000)

);

또는

create table emp(

employee_id number(6),

sal number(8, 2) constraint emp_sal_ck check (sal > 1000),

last_name varchar2(20) not null

);

Table EMP이(가) 생성되었습니다.

insert into emp values (208, 1200, 'SMAT');

1 행 이(가) 삽입되었습니다.

insert into emp values (209, 900, 'ARIS');

오류 보고 -

ORA-02290: check constraint (HR.EMP_SAL_CK) violated

drop table emp cascade constraint;

Table EMP이(가) 삭제되었습니다.

NOT NULL, UK, CHECK 사용 예제

create table emp(

eno number(4),

ename varchar2(20) constraint emp_ename_nn not null,

gno varchar2(13),

gender varchar2(5),

constraint emp_gno_uk unique (gno),

constraint emp_gno_ck check (length(gno) = 13),

constraint emp_gender_ck check (gender in ('woman', 'man'))

);

Table EMP이(가) 생성되었습니다.

drop table emp cascade constraint;

Table EMP이(가) 삭제되었습니다.