概要
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行が作成されました。
