[SQL 24] 제약조건 - PK(Primary Key), FK(Foreign Key)

샘플 스키마 : HR

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

PK(Primary Key)와 FK(Foreign Key)는 테이블의 필수 요소로써 모든 테이블은 이들 둘 중 하나 이상을 반드시 포함하고 있다.

No. 01
PRIMARY KEY(PK)

Primary Key 설정

CREATE TABLE 테이블(

…

CONSTRAINT 제약_조건_이름 PRIMARY KEY (컬럼)

);

CREATE TABLE 테이블(

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

…

• 테이블을 생성할 때 PK를 정의한다.
• PK는 각 행을고유하게 식별하는 역할을 담당한다.
• 테이블당 하나만 정의 가능하다.
• 지정된 컬럼에는 중복된 값이나 NULL값이 입력될 수 없다.
• NOT NULL + UNIQUE(UK)를 한 것과 같은 기능을 한다.
• PK로 지정 가능한 컬럼이 여러 개 있을 때는 검색에 많이 사용되고 간단하고 짧은 컬럼을 지정한다.
• 주 식별자, 주키 등으로 불린다.
• 고유 인덱스(Unique index)가 자동으로 생성된다.

◈ 예제

create table dept(

dno varchar2(14),

dname varchar2(14),

loc varchar2(8),

director varchar2(4),

constraint dept_dno_pk primary key (dno)

);

또는

create table dept(

dno varchar2(2) constraint dept_dno_pk primary key,

dname varchar2(14),

loc varchar2(8),

director varchar2(4)

);

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

No. 02
FOREIGN KEY(FK)

Foreign Key 설정

CREATE TABLE 테이블(

…

CONSTRAINT 제약_조건_이름 FOREIGN KEY (컬럼)

REFERENCES 참조할_테이블 (참조할_컬럼)

[ON DELETE CASCADE | ON DELETE SET NULL]

);

CREATE TABLE 테이블(

컬럼 데이터타입 CONSTRAINT 제약_조건_이름 FOREIGN KEY

REFERENCES 참조할_테이블 (참조할_컬럼)

[ON DELETE CASCADE | ON DELETE SET NULL]

…

• 테이블을 생성할 때 FK를 정의한다.
• FK가 정의된 테이블이 자식 테이블이다.
• 참조되는 테이블을 부모 테이블이라고 한다.
• 부모 테이블은 미리 생성되어 있어야 한다.
• 부모 테이블의 참조되는 컬럼에 존재하는 값만을 입력 할 수 있다.
• 부모 테이블은 FK로 인해 삭제가 불가능하다.
• REFERENCES : 참조할 부모 테이블과 부모 테이블에 있는 컬럼을 정의한다.
• ON DELETE CASCADE : 참조되는 부모 테이블의 행에 대한 DELETE를 허용한다.
• 부모 테이블의 행이 지워지면 자식 테이블의 행도 같이 지워진다.
• ON DELETE SET NULL : 참조되는 부모 테이블의 행에 대한 DELETE를 허요한다.
• 부모 테이블의 행이 지워지면 자식 테이블의 행은 NULL 값으로 설정된다.
• 데이터 타입이 반드시 일치해야 한다.
• 참조되는 컬럼은 PK이거나 UK(Unique key)만 가능하다.
• 외부키, 참조키, 외부 식별자 등으로 불린다.

insert into countries values ('KR', 'Korea', 3);

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

insert into countries values ('NK', 'North Korea', 5);

오류 보고 -

ORA-02291: integrity constraint (HR.COUNTR_REG_FK) violated - parent key not found

COUNTRIES 테이블의 REGION_ID 컬럼은 REGIONS 테이블의 REGION_ID 컬럼을 참조한다.

즉, REGIONS 테이블이 부모 테이블이고 COUNTRIES 테이블이 자식 테이블이다. 자식 테이블에 데이터를 삽입할 때 부모 테이블의 참조하는 컬럼에 없는 값을 삽입하면 위와 같이 오류가 발생한다.

 TIP 

일반적으로 데이터베이스를 설계할 때 자식 테이블의 FK 컬럼은 부모 테이블의 참조하는 컬럼과 같은 이름을 사용한다. 다른 이름을 사용해도 무관하지만, 되도록이면 같은 이름을 사용하는 것이 테이블 간의 관계를 한 번에 알아볼 수 있기 때문에 좋다.

◈ 예제

create table emp(

eno varchar2(4),

ename varchar2(10),

job varchar2(6),

mgr varchar2(4),

hdate date,

sal number,

dno varchar2(2),

constraint emo_eno_pk primary key (eno),

constraint emp_eno_fk foreign key (dno) references dept (dno)

);

또는

create table emp(

eno varchar2(4) constraint emp_eno_pk primary key,

ename varchar2(10),

job varchar2(6),

mgr varchar2(4),

hdate date,

sal number,

dno varchar2(2) constraint emp_dno_fk references dept (dno)

);

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

컬럼 레벨로 정의할 때는 foreign key를 생략해도 무관하다.

drop table dept cascade constraint;

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

drop table emp cascade constraint;

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

테이블 생성 예제

create table employees(

employee_id number(6),

first_name varchar2(20),

last_name varchar2(25) constraint emp_last_name_nn not null,

email varchar2(25) constraint emp_email_nn not null,

phone_number varchar2(20),

hire_date date constraint emp_hire_date_nn not null,

job_id varchar2(10) emp_job_nn not null,

salary number(8, 2)

commission_pct number(2, 2),

manager_id number(6),

department_id number(4),

constraint emp_employee_id primary key (employee_id),

constraint emp_email_uk unique (email),

constraint emp_salary_ck check (salary > 0),

constraint emp_dept_fk foreign key (department_id)

references departments (department_id)

);

우리가 사용하고 있는 HR 스키마의 EMPLOYEES 테이블은 위와 같은 문장으로 생성되었다.

No. 03
제약 조건 조회

오라클에서만 해당되는 내용이다. DB 종류마다 다르므로 오라클을 사용할 경우만 참고 하자.

제약 조건 조회

SELECT c.table_name, c.constraint_name, c.constraint_type, s.column_name

FROM user_constraints c, user_cons_columns s

WHERE c.constraint_name = s.constraint_name

AND c.table_name in (검색_대상_테이블_목록)

ORDER BY c.table_name;

• 테이블에 정의된 제약 조건을 검색한다.
• constraint_name : 제약 조건 이름
• constraint_type : 제약 조건 타입
• P(PK), R(FK), U(UK), C(NOT NULL, CHECK)
  

◈ 예제

select c.table_name, c.constraint_name, c.constraint_type, s.column_name

from user_constraints c, user_cons_columns s

where c.constraint_name = s.constraint_name

and c.table_name in ('EMPLOYEES', 'DEPARTMENTS')

order by c.table_name;

참조하는 테이블 목록 조회

SELECT p.table_name 부모테이블, p.constraint_name 부모제약조건,

c.table_name 자식테이블, c.constraint_name 참조제약조건 

FROM user_constraints p, user_constraints c

WHERE c.r_constraint_name = p.constraint_name

AND p.table_name in (검색_대상_테이블_목록)

ORDER BY p.table_name;

• 지정한 테이블을 참조하는 테이블의 목록을 확인한다.
• 자식 테이블이 지정되어 있으면 삭제가 불가능하다.
• ON DELETE 옵션이 설정되어 있는 경우는 삭제 가능

◈ 예제

select p.table_name 부모테이블, p.constraint_name 부모제약조건,

c.table_name 자식테이블, c.constraint_name 참조제약조건

from user_constraints p, user_constraints c

where c.r_constraint_name = p.constraint_name

and p.table_name in ('EMPLOYEES', 'DEPARTMENTS', 'LOCATIONS')

order by p.table_name;

CHECK와 NOT NULL 제약 조건 검색

SELECT constraint_name, search_condition

FROM user_constraints

WHERE table_name = '테이블';

• NOT NULL과 CHECK 제약 조건의 내용을 검색한다.
• 이외 제약 조건은 널 값이다.

◈ 예제

select constraint_name, search_condition

from user_constraints

where table_name = 'EMPLOYEES';