Oracle® 非公式 SQL, PL/SQL & DBA's リファレンス

ALTER TABLE （プライマリキーの追加、削除、変更）

主キー、プライマリキー制約の追加

基本書式

ALTER TABLE table
ADD [ CONSTRAINT constraint_name ]
     PRIMARY KEY ( column1 [, column2 [, ...]] )
       [ USING INDEX [ ( CREATE INDEX ) ]]

ALTER TABLE ADD PRIMARY KEY

主キー、プライマリキーの名称( primary_key_name ) を省略

ALTER TABLE table_name ADD
    [ CONSTRAINT primary_key_name ]
    PRIMARY KEY (col_name, colname2 ..) ;

ADD PRIMARY KEY と USING INDEX

キーの作成時に使用される索引の情報の付加

ALTER TABLE table_name ADD CONSTRAINT primary_key_name 
  PRIMARY KEY  (col_name, colname2 ..) 
    USING INDEX -- インデックス作成時の属性情報
    PCTFREE 10 INITRANS 2 
    STORAGE( INITIAL 64K )
    TABLESPACE USERS ;

ADD PRIMARY KEY と USING INDEX & CREATE INDEX

キーの作成時に使用する索引作成文を記述

ALTER TABLE table_name ADD CONSTRAINT primary_key_name 
  PRIMARY KEY  (user_id) 
  USING INDEX -- インデックス CREATE 文
  (
    -- CREATE INDEX 文をそのまま記述
    -- インデックス名はプライマリキー同じ名前でも名前の衝突はしない
    CREATE UNIQUE INDEX primary_key_name ON table_name (col_name, colname2 ..) 
    PCTFREE 50 INITRANS 20
    STORAGE( INITIAL 1M )
    TABLESPACE USERS
  ) ;

長文になりやすいので以下のように DDL 2つに分ける方が良いと思う。

ADD PRIMARY KEY と USING INDEX に既存インデックスを関連付け

キーの作成時に適用する既存の索引名を指定

-- 既存の非ユニークのインデックスでも利用できる
CREATE UNIQUE INDEX index_name ON table_name (col_name, colname2 ..) .. 略

プライマリキーとインデックスの関連付け

ALTER TABLE table_name ADD CONSTRAINT primary_key_name 
  PRIMARY KEY  (col_name, colname2 …) 
  USING INDEX index_name ;

主キー、プライマリキー制約の削除

ALTER TABLE DROP PRIMARY

プライマリキーの削除

ALTER TABLE table_name DROP PRIMARY KEY;
 or
ALTER TABLE table_name DROP CONSTRAINT primary_key_name ;

プライマリキー制約の削除だけで併設インデックスは消さない

ALTER TABLE table_name DROP PRIMARY KEY KEEP INDEX;

主キー、プライマリキー制約の変更

1. プライマリキーを削除して新しいプライマリキーを作成（変更ではない）
2. プライマリキーと候補キーを交替

の 2通りの方法があるが、1. プライマリキーの削除を行なうとユニーク制約も同時に解除されてしまうので実行タイミングに注意が必要。

補足： ALTER TABLE MODIFY PRIMARY KEY で変更できるのは DEFERRABLE（遅延制約）、RELY、ENABLE/DISABLE、VALIDATE、例外表、USING INDEX でプライマリキー項目の追加や変更を直接できない。

1. プライマリキー制約の削除と作成

ALTER TABLE table_name DROP PRIMARY KEY DROP INDEX ; -- DROP INDEX がデフォルト

ALTER TABLE table_name ADD CONSTRAINT MY_TABLE_PK
  PRIMARY KEY  ( alter_col_name,  alter_col_name2 …) ;  -- USING INDEX は省略

2.1 プライマリキーと候補キーを交替

テーブル名「MY_TABLE」、キー項目 「ID」、プライマリキー名 「MY_TABLE_PK」、インデックス名 「MY_INDEX_PK」 の状態（※1）を新・プライマリキー項目 「ALTER_ID」 に変更したい場合

ALTER INDEX MY_INDEX_PK RENAME TO MY_INDEX_UQ ;
ALTER TABLE MY_TABLE DROP PRIMARY KEY KEEP INDEX ;

ユニークインデックスを使用している場合、プライマリキーを 候補キー に変更中も一意性を維持できる。

既存のプライマリキーが非ユニークインデックスを使用してる場合、 Oracle 12c 以降であれば INVISIBLE で一時的にインデックスを併設させることができる（VISIBLE and/or DROP するのを忘れずに）

-- CREATE INDEX MY_INDEX_NON_UQ ON MY_TABLE ( ID ) ; -- 名称変更済の既設インデックス相当
CREATE UNIQUE INDEX MY_INDEX_UQ ON MY_TABLE ( ID ) INVISIBLE ;

プライマリキーを再作成とインデックスの作成

ALTER TABLE MY_TABLE ADD CONSTRAINT MY_TABLE_PK
  PRIMARY KEY  ( ALTER_ID ) ;
  -- USING INDEX は省略

プライマリキーへの項目追加も手順が増えるが可能。Oracle 12c 以降であれば INVISIBLE を使えば手順を減らせることができる。

2.2 インデックスもある既存の候補キーをプライマリキーに昇格する場合

現行・プライマリキー名が MY_TABLE_PK / インデックス名が MY_INDEX_PK
新・プライマリキーがカラム名 ALTER_ID で既設のインデックス名が CANDIDATE_INDEX の場合

ALTER INDEX MY_INDEX_PK RENAME TO MY_INDEX_UQ ;
ALTER TABLE MY_TABLE DROP PRIMARY KEY KEEP INDEX ;

プライマリキーに名前をつけて管理しているときには名前を変更

ALTER INDEX CANDIDATE_INDEX RENAME TO MY_TABLE_PK ;

USING INDEX でインデックスをプライマリキーに紐付ける

ALTER TABLE MY_TABLE ADD CONSTRAINT MY_TABLE_PK
  PRIMARY KEY  ( ALTER_ID ) 
  USING INDEX MY_TABLE_PK ;

（※1) プライマリキーの確認をするための SQL

テーブル名 MY_TABLE のプライマリキー名、構成カラム、インデックス名などを確認する SQL

select 
  c.table_name, c.constraint_name, c.status cc_status,
  cc.position, cc.column_name, 
  ix.index_name, ix.uniqueness, ix.tablespace_name, ix.visibility, ix.status
from user_indexes ix,
  user_constraints c, user_cons_columns cc
where
  ix.table_name in ('MY_TABLE') and c.constraint_type = 'P'
  and ix.index_name = c.index_name and c.constraint_name = cc.constraint_name
order by cc.constraint_name, cc.position;

プライマリキー制約の disable と enable について

プライマリキーを disable したとき、テーブル定義時に作成、または、関連付けした索引の定義情報（インデックス名、表領域、パーティション、PCTFREE、INITRANS、etc）が全損失する。 その後 enable したときにインデックスが再構築され、すべてデフォルト値のインデックスが作成される。

ALTER TABLE table_name MODIFY PRIMARY KEY disable;
ALTER TABLE table_name MODIFY PRIMARY KEY enable;

参考 ⇒ ORA-00001: 一意制約に反しています

---

関連事項

• テーブルの作成 〜 CREATE TABLE
• テーブルの削除 〜 DROP TABLE