TABLE에 대한 사소한 몇 가지의 의문 해결

-- 첫번째 의문 : NOT NULL과 PRIMARY KEY

궁금증 1) NOT NULL 제약조건과 PRIMARY KEY 제약조건을 같이 쓸수 있을까? --> YES

아래 DDL문은 이채남 저 오라클 실습 1장에 나오는 예제 테이블 생성 쿼리이다. EMP_ID에 NOT NULL과 PRIMARY KEY 제약조건을 같이 줬는데, PK 자체가 NOT NULL에 Unique니까 이렇게 잡아주는게 의미가 있나? 또는 틀린 구문은 아닐까? 라는 의문이 들었다.

CREATE TABLE TEMP2 (

 EMP_ID      NUMBER NOT NULL PRIMARY KEY,

 EMP_NAME    VARCHAR2(10) NOT NULL,

 BIRTH_DATE  DATE,

 DEPT_CODE   VARCHAR2(06) NOT NULL,

 EMP_TYPE    VARCHAR2(04),

 USE_YN      VARCHAR2(01) NOT NULL,

 TEL         VARCHAR2(15),

 HOBBY       VARCHAR2(30),

 SALARY      NUMBER,

 LEV         VARCHAR2(04)

);

아래 쿼리를 수행시켜서 확인해봤다.

select a.owner, a.constraint_name, a.constraint_type, a.table_name, b.column_name, a.search_condition from user_constraints a, user_cons_columns b

where a.owner=b.owner

and a.constraint_name=b.constraint_name

and a.table_name=b.table_name

and a.table_name='TEMP2';

OWNER   CONS_NAME CONS_TYPE TAB_NAME COL_NAME SEARCH_CONDITION

PRAC01 SYS_C0011311 C TEMP2 EMP_ID "EMP_ID" IS NOT NULL

PRAC01 SYS_C0011312 C TEMP2 EMP_NAME "EMP_NAME" IS NOT NULL

PRAC01 SYS_C0011313 C TEMP2 DEPT_CODE "DEPT_CODE" IS NOT NULL

PRAC01 SYS_C0011314 C TEMP2 USE_YN "USE_YN" IS NOT NULL

PRAC01 SYS_C0011315 P TEMP2 EMP_ID

위의 결과에서 EMP_ID 컬럼에 PK constraint 와 NOT NULL Constraint 조건이 함께 생성된걸 확인 할 수 있다.

만약 첫번째 DDL문에서 not null 조건을 뺀 채 생성한다면 어떻게 될까?

OWNER   CONS_NAME CONS_TYPE TAB_NAME COL_NAME SEARCH_CONDITION

PRAC01 SYS_C0011328 C TEMP2 EMP_NAME "EMP_NAME" IS NOT NULL

PRAC01 SYS_C0011329 C TEMP2 DEPT_CODE "DEPT_CODE" IS NOT NULL

PRAC01 SYS_C0011330 C TEMP2 USE_YN "USE_YN" IS NOT NULL

PRAC01 SYS_C0011331 P TEMP2 EMP_ID

위의 결과처럼 EMP_NAME에 대해 NOT NULL constraint는 생성되지 않는다.

궁금증 2) PRIMARY KEY  컬럼에 Null을 명시해도 오류가 나지 않는가? 테이블이 생성되는가? --> YES

CREATE TABLE TEMP2 (

 EMP_ID      NUMBER NULL PRIMARY KEY,

 EMP_NAME    VARCHAR2(10) NOT NULL,

 BIRTH_DATE  DATE,

 DEPT_CODE   VARCHAR2(06) NOT NULL,

 EMP_TYPE    VARCHAR2(04),

 USE_YN      VARCHAR2(01) NOT NULL,

 TEL         VARCHAR2(15),

 HOBBY       VARCHAR2(30),

 SALARY      NUMBER,

 LEV         VARCHAR2(04)

);

결과적으로 위와 같이 생성해도 아~무 문제도 없다. 언뜻 생각하면 헷갈릴 수 있지만 각 컬럼값에 대해 NULL이 default 값이란걸 생각해보면 당연한 결과이다. 즉 위의 쿼리는 아래의 쿼리와 동일하다.

CREATE TABLE TEMP2 (

 EMP_ID      NUMBER PRIMARY KEY,

 EMP_NAME    VARCHAR2(10) NOT NULL,

 BIRTH_DATE  DATE,

 DEPT_CODE   VARCHAR2(06) NOT NULL,

 EMP_TYPE    VARCHAR2(04),

 USE_YN      VARCHAR2(01) NOT NULL,

 TEL         VARCHAR2(15),

 HOBBY       VARCHAR2(30),

 SALARY      NUMBER,

 LEV         VARCHAR2(04)

);

그리고 당연하게도 PK가 해당 컬럼에 있기 때문에, 테이블 생성 시 NULL을 명시했다고 해서 해당 컬럼이 NULL인 레코드가 테이블에 insert되는 경우는 발생하지 않기 때문에 안심하시라.

궁금증 3)  NOT NULL(NULL), PRIMARY KEY 구문의 순서가 바뀌어도 되는가? --> YES

실험으로 확인했으나, 간단한 부분이니 따로 결과를 올리지는 않았다.

-- 두번째 의문 : DROP TABLE 구문

DROP TABLE구문은 다음과 같다.

DROP TABLE schema_name.table_name [cascade constraints] [purge];

궁금증 4) cascade constraints 구문

일단 헷갈리는 부분인 Foreign Key에 대해 정리하고 넘어가자. Foreign Key 생성 예제는 다음과 같다.

ALTER TABLE employees

ADD CONSTRAINTS fk_deptno FOREIGN KEY (department_id)

REFERENCES departments (department_id);

여기서 자식 테이블은 employees, 부모 테이블은 departments가 된다. 즉, Foreign Key constraint(이하 Foreign Key)는 자식테이블에 생성되는 것이다. 또한 Foreign Key는 referential integrity constraint(참조 무결성 제약조건)과 같은 말이다.

도표로 표현하면 다음과 같다.

자식테이블                                     부모테이블

--------                                     ---------

Foreign Key  --> refers to (참조)  -->   Primary Key or Unique Key

                                           *referential integrity relationship (참조 무결성 관계) 

다시 DROP TABLE 구문으로 돌아가서, cascade constraints구는 만약 drop될 테이블이 부모테이블로서 참조되고 있다면, 자식테이블의 참조하는 Foreign Key 제약조건을 함께 삭제하라는 명령이다. 왜냐하면 자식 테이블의 외래키에 의해 참조되고 있는 테이블은 Drop 될 수 없기 때문이다.

결국 drop할 테이블이 부모 테이블이 아니라면 cascade constraints 구문은 불필요하다.

사족) Foreign Key 제약조건과 관련하여 끊임없이 헷갈리는 이유는 짧은 경험 때문이기도 하지만, 자식테이블에 FK를 생성할 때 부모테이블에 대응되는 무언가(?)가 없기 때문이지 않나... 라는 생각이 든다. 따라서 "자식테이블에 FK를 생성하면, 부모 테이블엔 referential integrity relationship(참조 무결성 관계)가 생긴다." 정도로 정리를 해두려고 한다. 물론 엄밀히 따지면 부-자 테이블 간에 해당 관계가 성립되는 것이겠지만...

궁금증 5) Purge 구문과 Constraints

아시다시피 purge 구문은 oracle 10g의 신 기능인 recycle bin과 관련된 구문이다. 즉 테이블을 drop 했을 때 purge하기 전까지는 해당 테이블은 recycle bin에 place되어 복구가 가능하다. 그런데 constraints와 관련하여..

일단 테이블 드랍

drop table temp2;

그리고 다음 구문으로 조회를 한다.

select a.owner, a.constraint_name, a.constraint_type, a.table_name, b.column_name, a.search_condition from user_constraints a, user_cons_columns b

where a.owner=b.owner

and a.constraint_name=b.constraint_name

and a.table_name=b.table_name

and a.table_name like 'BIN%';

OWNER   CONS_NAME CONS_TYPE TAB_NAME COL_NAME SEARCH_CONDITION

PRAC01 BIN$IFoPPCQFRgiZ37xFqNreYA==$0 C BIN$rcwgjLNxTnyUVLV7CgSwgw==$0 EMP_ID "EMP_ID" IS NOT NULL

PRAC01 BIN$VQtSPYN1SoWRJruYXqAABg==$0 C BIN$rcwgjLNxTnyUVLV7CgSwgw==$0 EMP_NAME "EMP_NAME" IS NOT NULL

PRAC01 BIN$za5xbIlVT9u3ju49LJEoDA==$0 C BIN$rcwgjLNxTnyUVLV7CgSwgw==$0 DEPT_CODE "DEPT_CODE" IS NOT NULL

PRAC01 BIN$JRARQl33RA2Yup/70nI8eQ==$0 C BIN$rcwgjLNxTnyUVLV7CgSwgw==$0 USE_YN "USE_YN" IS NOT NULL

PRAC01 BIN$33sqjnJjRJqWelc3yo2tkg==$0 P BIN$rcwgjLNxTnyUVLV7CgSwgw==$0 EMP_ID

다음과 같이 constraints도 완전히 삭제 되지 않고 replace 되었음을 확인할 수 있다.

그리고 recycle bin을 purge 한다.

purge recyclebin;

다시 조회하면 recycle bin에 replaced되었던 constraints도 삭제 되었음을 확인할 수 있다.

만약 해당 테이블을 복원할 필요가 없거나, 백업이 잘 되어 있어서 recycle bin 보관이 필요치 않은 경우라면, 다음 구문으로 깔끔하게 constraints 까지 정리하는 편이 좋겠다.

drop table temp2 purge;