MSSQL FK 조회 - MSSQL FK johoe

샘플 스키마 : HR

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

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

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이(가) 생성되었습니다.

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 테이블은 위와 같은 문장으로 생성되었다.

오라클에서만 해당되는 내용이다. 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';