샘플 스키마 : 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';
'SQL' 카테고리의 다른 글
| [SQL 26] 컬럼 추가/수정/삭제와 서브 쿼리를 이용한 테이블 생성 (0) | 2017.12.22 |
|---|---|
| [SQL 25] 제약 조건 관리 (0) | 2017.12.21 |
| [SQL 23] 제약 조건 - NOT NULL, UK(Unique Key), CHECK (0) | 2017.12.16 |
| [SQL 22] 테이블 생성과 삭제, 데이터 타입 (0) | 2017.12.15 |
| [SQL 21] 서브쿼리를 사용한 DML 문장과 다이렉트 로드 (0) | 2017.12.15 |