OracleのPK削除時の挙動について

概要

OracleのPK削除時の挙動について確認しました。

確認内容

CREATE TABLE文でPK付のテーブルを作成します。

SQL> CREATE TABLE TEST.TEST0722
  2  (
  3  COL1 NUMBER,
  4  COL2 NUMBER,
  5  CONSTRAINT TEST0722_PK PRIMARY KEY( COL1 )
  6  );

表が作成されました。

データディクショナリビューを確認して制約、インデックスが作成されていることを確認します。

SQL> COL CONSTRAINT_NAME FOR A30
SQL> COL TABLE_NAME FOR A30
SQL> COL COLUMN_NAME FOR A30
SQL> SELECT
  2  CON.CONSTRAINT_NAME,CON.TABLE_NAME,CON.COLUMN_NAME
  3  FROM DBA_CONSTRAINTS C,DBA_CONS_COLUMNS CON
  4  WHERE C.OWNER='TEST'
  5  AND C.CONSTRAINT_NAME=CON.CONSTRAINT_NAME
  6  AND C.CONSTRAINT_TYPE='P'
  7  AND C.TABLE_NAME='TEST0722';

CONSTRAINT_NAME                TABLE_NAME                     COLUMN_NAME
------------------------------ ------------------------------ ------------------------------
TEST0722_PK                    TEST0722                       COL1

SQL> COL INDEX_NAME FOR A30
SQL> SELECT INDEX_NAME FROM DBA_INDEXES
  2  WHERE OWNER='TEST' AND TABLE_NAME='TEST0722';

INDEX_NAME
------------------------------
TEST0722_PK

先にインデックスを削除しようとするとエラーとなりました。

SQL> drop index TEST.TEST0722_PK;
drop index TEST.TEST0722_PK
                *
行1でエラーが発生しました。:
ORA-02429: 一意キーまたは主キーの保持に使用される索引は削除できません。

制約を削除します。

SQL> ALTER TABLE TEST.TEST0722 DROP CONSTRAINT TEST0722_PK;

表が変更されました。

制約と索引が存在しないことを確認します。

SQL> SELECT
  2  CON.CONSTRAINT_NAME,CON.TABLE_NAME,CON.COLUMN_NAME
  3  FROM DBA_CONSTRAINTS C,DBA_CONS_COLUMNS CON
  4  WHERE C.OWNER='TEST'
  5  AND C.CONSTRAINT_NAME=CON.CONSTRAINT_NAME
  6  AND C.CONSTRAINT_TYPE='P'
  7  AND C.TABLE_NAME='TEST0722';

レコードが選択されませんでした。

SQL> COL INDEX_NAME FOR A30
SQL> SELECT INDEX_NAME FROM DBA_INDEXES
  2  WHERE OWNER='TEST' AND TABLE_NAME='TEST0722';

レコードが選択されませんでした。

重複レコードもNULLレコードも挿入可能です。

SQL> INSERT INTO TEST.TEST0722 VALUES(1,2);

1行が作成されました。

SQL> INSERT INTO TEST.TEST0722 VALUES(1,2);

1行が作成されました。

SQL> INSERT INTO TEST.TEST0722 VALUES(NULL,2);

1行が作成されました。

制約を削除する際にKEEP INDEXオプションを指定して削除します。
準備としてもう一度PKを追加します。

SQL> ALTER TABLE TEST.TEST0722 ADD CONSTRAINT TEST0722_PK PRIMARY KEY(COL1);

表が変更されました。

SQL> SELECT
  2  CON.CONSTRAINT_NAME,CON.TABLE_NAME,CON.COLUMN_NAME
  3  FROM DBA_CONSTRAINTS C,DBA_CONS_COLUMNS CON
  4  WHERE C.OWNER='TEST'
  5  AND C.CONSTRAINT_NAME=CON.CONSTRAINT_NAME
  6  AND C.CONSTRAINT_TYPE='P'
  7  AND C.TABLE_NAME='TEST0722';

CONSTRAINT_NAME                TABLE_NAME                     COLUMN_NAME
------------------------------ ------------------------------ ------------------------------
TEST0722_PK                    TEST0722                       COL1

SQL> SELECT INDEX_NAME,UNIQUENESS FROM DBA_INDEXES
  2  WHERE OWNER='TEST' AND TABLE_NAME='TEST0722';

INDEX_NAME                     UNIQUENES
------------------------------ ---------
TEST0722_PK                    UNIQUE

索引は残すように制約を削除します。

SQL> ALTER TABLE TEST.TEST0722 DROP CONSTRAINT TEST0722_PK KEEP INDEX;

表が変更されました。

SQL> SELECT
  2  CON.CONSTRAINT_NAME,CON.TABLE_NAME,CON.COLUMN_NAME
  3  FROM DBA_CONSTRAINTS C,DBA_CONS_COLUMNS CON
  4  WHERE C.OWNER='TEST'
  5  AND C.CONSTRAINT_NAME=CON.CONSTRAINT_NAME
  6  AND C.CONSTRAINT_TYPE='P'
  7  AND C.TABLE_NAME='TEST0722';

レコードが選択されませんでした。

SQL> SELECT INDEX_NAME,UNIQUENESS FROM DBA_INDEXES
  2  WHERE OWNER='TEST' AND TABLE_NAME='TEST0722';

INDEX_NAME                     UNIQUENES
------------------------------ ---------
TEST0722_PK                    UNIQUE

SQL>

一意索引のみが残っているので重複レコードはエラーとなりますが、NULLレコードは挿入できます。

SQL> INSERT INTO TEST.TEST0722 VALUES(1,2);

1行が作成されました。

SQL> INSERT INTO TEST.TEST0722 VALUES(1,2);
INSERT INTO TEST.TEST0722 VALUES(1,2)
*
行1でエラーが発生しました。:
ORA-00001: 一意制約(TEST.TEST0722_PK)に反しています


SQL> INSERT INTO TEST.TEST0722 VALUES(NULL,2);

1行が作成されました。