SQL-제약 조건 이해와 설정-PK, FK

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

- PRIMARY KEY

- FOREIGN KEY

- UNIQUE KEY

- NOT NULL

- CHECK

 

PK(Primary Key, 주키, 주식별자)
결정 인자라고도 부른다. 테이블의 모든 데이터를 유일하게 식별해주는 컬럼이다. 테이블은 무조건 PK를 가지고 있다.

 

함수적 종속

레코드 내에 존재하는 항목(컬럼)들 중에 속성 B가 속성 A에 함수적 종속관계에 있다는 것은 A를 이용해서 B를 식별할 수 있다는 뜻이다. A -> B의 형태로 사용하며 A를 결정인자(Determinant), 프라이머리 키라고 부른다.

 

A -> (B< C)인 경우 A -> B, A -> C가 성립한다. 

(A, B) -> C인 경우 A -> C, B -> C는 성립하지 않는다.

 

FK(Foreign Key, 외부키, 외부식별자)

테이블간 관계(Relationship)를 의미한다. 테이블은 참조를 당하는 경우가 아니면 무조건 FK를 가지고 있다.

 

UNIQUE KEY : 데이터 정보에 없을 수는 있지만 절대 중복되는 값은 없다. ex) 주민등록 번호

 

NOT NULL : NULL값이 들어가면 안됨. ex) 숫자

 

CHECK : 직접 써주는 제약조건. WHERE절에 써주는 방식과 같다.

 

* PK(Primary Key), FK(Foreign Key) 설정과 조회

 

Primary Key 설정
CREATE TABLE 테이블 (
.....
CONSTRAINT 제약_조건 PRIMARY KEY (컬럼) // CONSTRAINT는 예약어. 이 다음에 제약 조건이 온다.

);

 

CREATE TABLE 테이블 (
컬럼 데이터_타입 CONSTRAINT 제약_조건 PRIMARY KEY,
......

이 방식은 지저분해서 쓰지 않는다.

 

Foreign Key 설정
CREATE TABLE 테이블 (
.....
CONSTRAINT 제약_조건 FOREIGN KEY (컬럼)
REFERENCES 참조할_테이블 (참조할_컬럼) [ON DELETE CASCADE] // REFERENCES 뒤에 참조할 테이블과 컬럼을 기술.

);

 

ON DELETE CASCADE : 무결성이 중요한 테이블에서는 사용하지 않는다.

 

제약 조건 조회(반드시 암기할 것)

PK와 FK를 검색해주는 명령어

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; // 테이블이 여러개 나올경우에 정렬

 

FK가 어떤 테이블의 PK를 참조하는지 조회하는 명령어

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;

 

[예제 1] PK와 FK가 추가된 dept와 emp 테이블을 생성.
DROP TABLE emp;
DROP TABLE dept;
PURGE RECYCLEBIN;

CREATE TABLE dept (
dno VARCHAR2(2),
dname VARCHAR2(10),
loc VARCHAR2(6) ,
CONSTRAINT dept_dno_pk PRIMARY KEY(dno) // dept_dno_pk는 dno
);

 

CREATE TABLE emp (
eno VARCHAR2(4),
ename VARCHAR2(10),
sex VARCHAR2(2),
job VARCHAR2(8),
mgr VARCHAR2(4),
hdate DATE,

sal NUMBER,
comm NUMBER,
dno VARCHAR2(2),
CONSTRAINT emp_eno_pk PRIMARY KEY (eno), // emp 테이블의 eno를 PK로 지정
CONSTRAINT emp_mgr_fk FOREIGN KEY (mgr) REFERENCES emp (eno),
CONSTRAINT emp_dno_fk FOREIGN KEY (dno) REFERENCES dept (dno) // dept테이블의 dno를 참조
);

 

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 ('DEPT', 'EMP')
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 ('DEPT','EMP')
ORDER BY p.table_name;

 

[예제 2] Dept 테이블과 emp 테이블에 각각 데이터를 입력하고 제약조건이 입력 데이터를 통제하는지 확인.
INSERT INTO dept (dno, dname, loc)
VALUES ('10','개발','서울'); // commit는 아직 안 했지만 입력은 되있음.

 

INSERT INTO emp (eno,ename, dno)
VALUES ('2000','문시현','10');

 

COMMIT;

 

SQL> INSERT INTO dept (dno, dname, loc)
2 VALUES ('10','총무','부산'); // dno 컬럼에 10번이 이미 있어서 실행 안됨

 

INSERT INTO emp (eno,ename, dno)
2 VALUES ('2001','손하늘','20'); // 20번 부서는 존재하지 않아서 입력 안됨

 

[예제 3]다음과 같은 구조의 테이블을 생성하는 스크립트를 작성하고 이를 이용 테이블을 생성.

@C:\sql\board

 

DESC board;

DESC b_content;

 

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 ('BOARD','B_CONTENT')
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 ('BOARD','B_CONTENT');

 

[예제 4] 제약 조건에 이름을 지정하지 않고 테이블을 생성.

CREATE TABLE test (
2 no NUMBER,
3 PRIMARY KEY (no));

 

SELECT c.table_name, c.constraint_name, c.constraint_type, s.column_name
2 FROM user_constraints c, user_cons_columns s
3 WHERE c.constraint_name = s.constraint_name
4 AND c.table_name = 'TEST';

// 이런식으로 만들면 제약조건의 이름이 랜덤으로 생성되서 의미가 없기때문에 하지 않는다.

 

[실습]

1. 다음 구조를 갖는 테이블을 생성. 각 테이블에는 필요한 PK와 FK를 지정. 데이터 타입이나 길이는 임의로 각자 결정하고 테이블 생성 순서에 주의한다.

테이블의 조건

 

factory란 이름으로 sql 생성 후에 실행

@C:\sql\factory.sql

SELECT table_name FROM user_tables; 로 만들어진 테이블 확인

SELECT table_name, column_name, data_type, data_length 
FROM user_tab_columns

WHERE table_name = '각각 테이블명'; 으로 테이블의 구성 확인

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 ('FACTORY', 'GOODS', 'PROD') 
ORDER BY c.table_name;

으로 PK와 FK를 검색.

 

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 ('FACTORY', 'GOODS', 'PROD') 
ORDER BY p.table_name;

으로 FK가 어떤 PK를 참조하는지 검색.

 

2. 다음 테이블에 대한 표를 보고 테이블 생성을 위한 스크립트 생성 

schoolpkfk란 이름으로 sql 생성 후에 실행

@C:\sql\pkfk.sql

 

SELECT table_name FROM user_tables; 으로 만들어진 테이블 확인

SELECT table_name, column_name, data_type, data_length 
FROM user_tab_columns

WHERE table_name = '각각 테이블명'; 으로 테이블의 구성 확인

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 ('STUDENT', 'PROFESSOR', 'COURSE', 'SCORE') 
ORDER BY c.table_name;

으로 PK와 FK를 검색.

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 ('STUDENT', 'PROFESSOR', 'COURSE', 'SCORE') 
ORDER BY p.table_name;

으로 FK가 어떤 PK를 참조하는지 검색.

 

* Data Dictionary의 종류 (계속 추가해가면서 암기)

user_tables : 테이블의 정보를 조회해 줌

user_constraints : 유저가 소유한 제약조건을 모두 조회

user_cons_columns : 컬럼에 할당된 제약조건을 조회

 

ex)

DB내의 모든 테이블 정보 보기

SELECT table_name FROM user_tables; // SELECT * FROM tab;과 같음.

 

테이블의 내용을 보여주는 명령어

SELECT table_name, column_name, data_type, data_length 
FROM user_tab_columns

WHERE table_name = '테이블 이름'; // desc와 같고 테이블 이름은 대문자로 검색

 

PK와 FK를 검색해주는 명령어

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; // 테이블이 여러개 나올경우에 정렬

 

FK가 어떤 테이블의 PK를 참조하는지 조회하는 명령어

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;