PK 제약조건과 NOT NULL 제약조건

PK 제약조건과 NOT NULL 제약조건은 밀접한 연관이 있다.

테스트를 위해 아래와 같이 테이블을 생성하자.

-- 1
DROP TABLE t1 PURGE;
DROP TABLE t2 PURGE;

CREATE TABLE t1 AS SELECT ROWNUM AS c1, LPAD ('X', 4000, 'X') AS c2 FROM XMLTABLE ('1 to 100000');
CREATE TABLE t2 (c1 CONSTRAINTS t2_n1 NOT NULL, c2) AS SELECT * FROM t1;

CREATE UNIQUE INDEX t1_pk ON t1 (c1);
CREATE UNIQUE INDEX t2_pk ON t2 (c1);

t1 테이블의 c1 칼럼은 NULLABLE이고 c2 칼럼은 NOT NULL이다.

-- 2
SELECT table_name, nullable FROM user_tab_cols WHERE table_name IN ('T1', 'T2') AND column_name = 'C1';

TABLE_NAME NULLABLE
---------- --------
T1         Y
T2         N

2 행이 선택되었습니다.

PK 제약조건을 추가해보자. t1 테이블은 1.81초, t2 테이블은 0.01초가 소요되었다.

-- 3-1
ALTER TABLE t1 ADD CONSTRAINT t1_pk PRIMARY KEY (c1) USING INDEX t1_pk;

테이블이 변경되었습니다.

경   과: 00:00:01.81

-- 3-2
ALTER TABLE t2 ADD CONSTRAINT t2_pk PRIMARY KEY (c1) USING INDEX t2_pk;

테이블이 변경되었습니다.

경   과: 00:00:00.01

PK 제약조건은 UNIQUE + NOT NULL 제약조건이기 때문에 t2 테이블은 t2_pk 인덱스와 t2_n1 제약조건으로 별도의 작업 없이 바로 제약조건이 추가되었지만, t1 테이블은 NOT NULL 제약조건이 없기 때문에 검증을 위해 테이블 전체를 읽은 것이다.

-- 4
SELECT   table_name, constraint_name, constraint_type, search_condition_vc
    FROM user_constraints
   WHERE table_name IN ('T1', 'T2')
ORDER BY 1, 2;

TABLE_NAME CONSTRAINT_NAME CONSTRAINT_TYPE SEARCH_CONDITION_VC
---------- --------------- --------------- -------------------
T1         T1_PK           P
T2         T2_N1           C               "C1" IS NOT NULL
T2         T2_PK           P

3 행이 선택되었습니다.

*_TAB_COLS 뷰를 다시 조회해보면 t1 테이블의 c1 칼럼이 NOT NULL로 변경된 것을 확인할 수 있다. NOT NULL 제약조건은 없지만 PK 제약조건에 의해 NOT NULL 칼럼으로 변경된 것이다.

-- 5
SELECT table_name, nullable FROM user_tab_cols WHERE table_name IN ('T1', 'T2') AND column_name = 'C1';

TABLE_NAME NULLABLE
---------- --------
T1         N
T2         N

2 행이 선택되었습니다.

CTAS를 사용해 t1 테이블로 t3 테이블로, t2 테이블로 t4 테이블로 생성보자.

-- 6
DROP TABLE t3 PURGE;
DROP TABLE t4 PURGE;

CREATE TABLE t3 AS SELECT * FROM t1 WHERE 0 = 1;
CREATE TABLE t4 AS SELECT * FROM t2 WHERE 0 = 1;

생성된 t3 테이블의 c1 칼럼은 NULLABLE 칼럼이다. CTAS는 테이블을 생성할 때 NOT NULL 제약조건을 참조한다. PK 제약조건의 키 칼럼에 명시적으로 NOT NULL 제약조건을 생성하지 않으면 CTAS -> UNIQUE INDEX -> ADD CONSTRAINT 방식의 작업 성능이 저하될 수 있다.

-- 7
SELECT table_name, nullable FROM user_tab_cols WHERE table_name IN ('T3', 'T4') AND column_name = 'C1';

TABLE_NAME NULLABLE
---------- --------
T3         Y
T4         N

2 행이 선택되었습니다.

아래 쿼리로 PK에 의해 변경된 NOT NULL 칼럼을 식별할 수 있다. 관련 정보에 대한 별도의 속성은 없는 것으로 보인다.

-- 8
SELECT a.owner, a.table_name, a.column_name
  FROM dba_tab_columns a
 WHERE a.owner = 'SCOTT'
   AND a.nullable = 'N'
   AND NOT EXISTS (SELECT 'X'
                     FROM dba_constraints x
                    WHERE x.owner = a.owner
                      AND x.table_name = a.table_name
                      AND x.search_condition_vc = '"' || a.column_name ||  '" IS NOT NULL');


OWNER TABLE_NAME COLUMN_NAM
----- ---------- ----------
SCOTT T1         C1

1 행이 선택되었습니다.

아래 쿼리로 다수의 칼럼을 NOT NULL로 변경할 수 있다.

-- 9
ALTER TABLE t1 MODIFY (c1 NOT NULL, c2 NOT NULL);

테이블이 변경되었습니다.