Day 20 (SQL)

이번 시간에는 제약 조건중 가장 많이 사용하는 PRIMARY KEY, FOREIGN KEY에 대해 설명하겠다.

먼저 제약 조건중 몇가지 종류를 알아보겠다. PRIMARY KEY, FOREIGN KEY, UNIQUE KEY, NOT NULL CHECK가 있다.

PRIMARY KEY와 FOREIGN KEY는 각각 다른용어로 주식별자와 외부식별자로 얘기할 수 있다. 주식별자와 외부식별자라는 용어는 보통 모델링에서 사용하는 언어이고 DB에서는 PRIMARY KEY와 FOREIGN KEY라고 말하며 줄여서 PK와 FK라고 얘기한다.

PK는 데이터를 식별 가능하게 하는 값이기 때문에 절대 중복이 되거나 NULL값이 존재할 수 없다. FK는 명시적 FK와 비명시적 FK가 있다. 명시적 FK는 PK와의 관계를 논리 관계도나 물리 관계도를 작성할때 테이블과 테이블간의 관계를 선으로 표현한 것과 같이 연관 관계를 선으로서 나타 낼 수 있는 것이다. 반면 비명시적 FK는 테이블과 테이블간의 관계를 선으로 나타 낼 수없지만 그 데이터를 참조하고 있는 관계가 있어 글로 써 표현 해야 하는 것이다.

UNIQUE KEY는 데이터가 중복되지 않게 하는 제약 조건이다.

NOT NULL은 NULL값을 입력하지 못하게 하는 제약 조건이다. 숫자타입의 column에는 NULL이 존재 하지 않기때문에 실제로 적어도 숫자타입에는 NOT NULL을 적지 않는다.(*데이터 아라비아숫자가 적혀있다고 데이터 타입이 숫자는 아니다. 예를 들어 주민등록 번호라던지  연산을 할수없거나 하는 의미가 없는 데이터는 숫자가 아니라 문자다.) 또한 NOT NULL은 실제 제약 조건이 아닌 CHECK제약 조건의 일환으로 판단한다.  

CHECK는 WHERE절과 같이 조건을 지정하는 제약 조건이다.

 

PRIMARY KEY설정은 두가지로 가능하다.

첫째는 'CREATE TABLE 테이블( ..... CONSTRAINT 제약조건 PRIMARY KEY (칼럼));'처럼 컬럼을 전부 작성하고 컬럼 설정 밖에서 컬럼에 제약조건을 지정하는 방법이 있다.(*CONSTRAINT는 예약어 이다.)

둘째는 'CREATE TABLE 테이블( 컬럼 데이터타입 CONSTRAINT 제약조건 PRIMARY KEY,...);'처럼 컬럼뒤에 바로 제약 조건을 붙여 컬럼마다 제약 조건을 지정하는 방법이 있다.

두 방법중에서는 첫번째 방법인 컬럼의 데이터 타입과 길이를 먼저 정한후에 제약조건을 적는 방법을 더 많이 사용한다. 가독성이 무척 높아지기 때문이다.

 

FOREIGN KEY의 설정도 PRIMART KEY의 설정 방법과 같이 두가지 존재한다.

'CREATE TABLE 테이블 ( ... CONSTRAINT 제약조건 FOREIGN KEY (컬럼) REFERENCES 참조할테이블 (참조할컬럼) [ON DELETE CASCADE]);'처럼 컬럼을 전부 작성하고 컬럼 설정 밖에서 컬럼에 제약조건을 지정하는 방법이 있다('테이블'의 자리에는 자식테이블의 이름이, '참조할테이블'의 자리에는 부모테이블의 이름이 자리한다).

그리고 'CREATE TABLE 테이블( 컬럼 데이터타입 CONSTRAINT 제약조건 FOREIGN KEY REFERENCES 참조할테이블 (참조할칼럼) [ON DELETE CASCADE],...);'처럼 컬럼뒤에 바로 제약 조건을 붙여 컬럼마다 제약 조건을 지정하는 방법이 있다.

FOREIGN KEY의 설정도 PRIMART KEY의 설정 방법과 같이 첫번째 방법이 가독성이 높아 많이 사용한다.

단, 주의해야 할점은  [ON DELETE CASCADE]라는 옵션이 붙는다. 이 옵션은 사용하지 않는것이 좋은데 이 옵션을 사용하면 정규화 과정을 통해 관계된 모든 테이블의 관련 값들이 삭제된다. 따라서 이 옵션을 사용할때는 반드시 무결성이 중요하지 않은 게시판 등에 사용해야한다.

 

 

테이블의 제약 조건을 조회하는 코드에 대해 알아보겠다.

SELECT c.table_name, c.constraint_name, c.constraint_type, s.column_name
FROM user_constriants c, user_cons_columns s
WHERE c.constraint_name = s.constraint_name
AND c.table_name in ('검색대상테이블1', '검색대상테이블2', ...)
ORDER BY c.table_name;

위의 코드는 어떤 열에 어떤 제약조건이 있는지 조회 하는 코드다.

여기서 알고 가야 할 것은 Dictionary인 user_constraints와 user_cons_columns이다. 또한 user_tables를 사용할때와 같이 Dictionary 를 이용해 코드를 실행할때는 테이블의 이름을 대문자로 작성해야 한다.

 

SELECT p.table_name 상위테이블, p.constraint_name 상위제약조건, c.table_name 하위테이블, c.constraint_name 하위제약조건
FROM user_constraints p, user_constraints c
WHER c.r_cosntriant_name = p.constraint_name
AND p.table_name in ('검색대상테이블1', '검색대상테이블2', ...)
ORDER BY p.table_name;

위의 코드는 어떤 테이블의 어떤 열과 어떤 테이블의 어떤 열이 함수적 종속관계인지 알 수 있는 코드이다.

여기서 알아야 할점은 r_constraint_name의 항목인데 관계된 제약조건의 이름을 출력한다. 만약 constraint_name으로 적는다면 하위제약 조건 뿐만 아니라 PK도 출력될것이다.(해봐야함)

 

여담으로 함수적 종속관계는 어떤 데이터가 어떤 데이터를 참조하고 참조받는지에 대한 관계를 나타내는 것이다.

A->B인 경우 A는 B를 결정하기 때문에 A를 결정인자(Determinant)라고 한다.
A->(B, C)인 경우 A->B, A->C가 성립한다.
(A,B)->C인 경우 A->B, A->C는 성립하지 않는다.
A->B, B->C인 경우 A->C가 성립한다. (3차 정규화 대상이 아니다.)

 정규화과정을 통해 함수적 종속관계를 파악하면 부모테이블과 자식테이블의 구분이 된다. 부모테이블과 자식테이블을 이용하여 모델링을 해야 관계도를 그릴 수 있다(참고 : Day 13 마지막 부분).

 

PK와 FK를 이용해서 테이블을 생성하는 부분에서 주의해야 할점은 당연히 제약 조건을 적는 부분이다. 제약 조건을 적을 때 제약 조건명을 적는 부분에는 사용자가 임의로 지정이 가능하다. 물론 지정을 하지 않아도 상관은 없지만 지정하지 않는다면 Oracle 시스템이 임의로 명칭을 지정한다. 하지만 어떤 제약조건을 어떻게 사용했는지 알 수 없기때문에 작성해 주는 것이 좋고 작성방법은 따로 없지만 제약조건을 사용한 테이블 명과 컬럼 이름 어떤 제약조건이 사용되었는지 나타내주는게 가장 좋다고 본다. 예를들면 'emp_eno_pk'로 작성하게되면 'emp테이블의 eno컬럼은 제약조건이 pk다' 라는 것을 손쉽게 구별할 수 있다. 

또한 PK와 FK를 지정해주는 데이터 타입은 당연하지만 일치해야 한다.

 

몇가지 예시를 보자.

1. CONSTRAINT emp_eno_pk PRIMARY KEY(eno)
2. CONSTRAINT emp_mgr_fk FOREIGN KEY(mgr) REFERENCES emp(eno)
3. CONSTRAINT emp_dno_fk FOREIGN KEY(dno) REFERENCES dept(dno)

1번은 PK 제약 조건을 사용한 경우이다. 2번은  FK 제약 조건을 사용했는데 참조 테이블이 자신이므로 Self Reference(자기참조)이다. 3번은 2번과 마찬가지로 FK 제약 조건을 사용했는데 참조 테이블이 dept인 것으로 보아 자식테이블이다. 3번에서 알게된 점은 emp테이블을 작성하기 전에 dept테이블을 작성해야 한다는 것이다. 만약 emp테이블을 dept테이블보다 먼저 작성하게 되면 emp테이블은 참조할 테이블이 존재하기 전이기 때문에 없다고 판단하여 데이터 값이 들어가지 않아 정상적인 테이블 생성이 불가능하다.

 

테이블 상세 도표는 테이블의 관계도로 볼 수 있다.

임의로 보기 쉽게 나열한 상세 도표는 아래와 같다.

컬럼명	dno	dname	loc
PK/UK/NOT NULL	PK
참조 테이블
참조 칼럼
CHECK
데이터 타입	VARCHAR2	VARCHAR2	VARCHAR2
길이	2	10	6

 

상세 도표의 표준안은 아래와 같다. 

컬럼명	PK/UK/NOT NULL	참조 테이블	참조 칼럼	CHECK	데이터 타입	길이
dno	PK				VARCHAR2	2
dname					VARCHAR2	10
loc					VARCHAR2	6

 

 

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

테이블 생성

테이블 정보

테이블내의 제약 조건

테이블 간의 제약 조건 관계

실습 20

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

 

      factory 테이블(공장) : fno(공장번호), fname(공장이름), loc(지역)
      goods 테이블(제품) : gno(제품번호), gname(제품명), pri(표준단가), fac_no(생산공장)
      prod 테이블(출고상품) : s_num(일련번호), gno(제품번호), pri(출고단가), pdate(생산일자)

 

 

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

---

<Day 17의 실습 8번의 질문에 대한 정보>

숫자컬럼의 크기가 줄거나, 문자화 해줄 숫자가 문자화 될 데이터 값보다 작으면 #####으로 출력된다.

 

서식을 조절하는 예약어.

SET LINE 100; : 한 라인에 나오는 글자수를 100글자로 조절한다.

SET PAGES 200; : 한 페이지에 나오는 라인 수를 200줄로 조절한다.

col 'columns' format a10; : columns의 컬럼 크기를 10byte로 설정한다.

 

---

 

2일간 배운 dictionary

user_tables : 스키마 내의 테이블 정보
user_tab_columns : 스키마 내 테이블 컬럼의 이름, 데이터 타입 길이 
user_constriants : 스키마 내 테이블에서 사용한 제약조건
user_cons_columns : 스키마 내 테이블의 함수적 종속 관계