[호기심] PK 칼럼인데, NULLABLE 인데 어떻게 ?

[호기심] PK 칼럼인데, NULLABLE 인데 어떻게 ?
[확인결과] ENABLE NOVALIDATE 로 생성 하면 PK 지만 NULLABLE 가능 , NULLABLE 해결은 ENABLE VALIDATE 로 가능함 

 

DROP TABLE B ;
Table dropped.

create table B
(
  B_1 varchar2(10) NOT NULL,
  B_2 varchar2(10),
  B_3 varchar2(10)
) ;
Table created.


ALTER TABLE B ADD CONSTRAINT XPXB PRIMARY KEY
( B_1, B_2 ) USING INDEX ENABLE NOVALIDATE ;  
Table altered.

COL INDEX_NAME FOR A30
COL TABLE_NAME FOR A30
COL COILUMN_NAME FOR A30
SELECT INDEX_NAME, INDEX_TYPE  FROM DBA_INDEXES WHERE TABLE_NAME ='B';

INDEX_NAME                     INDEX_TYPE
------------------------------ ---------------------------
XPXB                           NORMAL



COL INDEX_NAME FOR A30
COL COLUMN_NAME FOR A30
SELECT INDEX_NAME, COLUMN_NAME FROM DBA_IND_COLUMNS WHERE TABLE_NAME ='B' ORDER BY 1,COLUMN_POSITION ; 


INDEX_NAME                     COLUMN_NAME
------------------------------ ------------------------------
XPXB                           B_1
XPXB                           B_2

COL CONSTRAINT_NAME FOR A30
COL CONSTRAINT_TYPE FOR A30
COL VALIDATE FOR A30
SELECT OWNER, CONSTRAINT_NAME, CONSTRAINT_TYPE, STATUS, VALIDATED
FROM DBA_CONSTRAINTS WHERE TABLE_NAME ='B';  2

OWNER          CONSTRAINT_NAME                CONSTRAINT_TYPE                STATUS   VALIDATED
-------------- ------------------------------ ------------------------------ -------- -------------
SCOTT          SYS_C006228                    C                              ENABLED  VALIDATED
SCOTT          XPXB                           P                              ENABLED  NOT VALIDATED   <<< NOT VALIDATE 임 

SELECT OWNER, TABLE_NAME,COLUMN_NAME, NULLABLE
FROM  DBA_TAB_COLUMNS
WHERE TABLE_NAME ='B'
ORDER BY COLUMN_ID ;  

OWNER          TABLE_NAME                     COLUMN_NAME                    N
-------------- ------------------------------ ------------------------------ -
SCOTT          B                              B_1                            N
SCOTT          B                              B_2                            Y   << B_2 가 PK 임에도 불구하고 NULLABLE = Y 
SCOTT          B                              B_3                            Y

-- PK 칼럼 NOT NULL 로 변경하기 ( ENABLE VALIDATE 하기 ) 
ALTER TABLE B ENABLE VALIDATE CONSTRAINT XPXB ;
Table altered.

SELECT OWNER, CONSTRAINT_NAME, CONSTRAINT_TYPE, STATUS, VALIDATED
FROM DBA_CONSTRAINTS WHERE TABLE_NAME ='B';  2

OWNER          CONSTRAINT_NAME                CONSTRAINT_TYPE                STATUS   VALIDATED
-------------- ------------------------------ ------------------------------ -------- -------------
SCOTT          SYS_C006228                    C                              ENABLED  VALIDATED
SCOTT          XPXB                           P                              ENABLED  VALIDATED     <<< VALIDATE 임

SELECT OWNER, TABLE_NAME,COLUMN_NAME, NULLABLE
FROM  DBA_TAB_COLUMNS
WHERE TABLE_NAME ='B'
ORDER BY COLUMN_ID ;  2    3    4

OWNER          TABLE_NAME                     COLUMN_NAME                    N
-------------- ------------------------------ ------------------------------ -
SCOTT          B                              B_1                            N
SCOTT          B                              B_2                            N   << B_2 가 NULLABLE =N 로 변경됨 
SCOTT          B                              B_3                            Y