主キー制約と索引の作成順についてのメモ(仕様確認)

下記仕様があることを知らなかったので検証確認してみました。

主キー制約の仕様

主キー制約 主キー制約は、列を表またはビューの主キーとして指定します。複合主キーは、列の組合せを主キーとして指定します。主キー制約を表内に定義する場合に必要となるのは、PRIMARY KEYキーワードのみです。主キー制約を表外に定義する場合は、1つ以上の列も指定する必要があります。複合主キーは表外に定義する必要があります。

主キー制約を満たすには次の条件があります。・主キーの値が、表の中の複数行に存在することはできません。・主キーを構成する列に、NULLを持たせることはできません。

主キー制約を作成する場合・Oracle Databaseでは、主キー制約を適用する前に既存の索引に一意の値の集合が含まれる場合、その索引が使用されます。既存の索引は、一意であると定義されている場合も、一意でないと定義されている場合もあります。DML操作を実行すると、この既存の索引を使用して主キー制約が適用されます。・既存の索引を使用できない場合は、一意索引が生成されます

検証手順

--== 下準備 ==--
-- 表領域作成
create tablespace idx_test_tbs datafile '/u01/app/oracle/oradata/orcl/idx_test_tbs.dbf' size 100M;

-- ユーザー作成
create user idx_test
identified by "idx_test"
default tablespace idx_test_tbs
temporary tablespace temp;

-- 権限付与
grant dba to idx_test;

--== 検証(制約 --> 索引) ==--
-- 表作成
create table idx_test.test_tbl(
  col1 varchar(10),
  col2 varchar(10),
  col3 varchar(10)
);

-- 主キー制約付与
alter table idx_test.test_tbl add constraint pk1 primary key (col1, col2) using index; 

-- 索引作成
create index idx_test.idx on idx_test.test_tbl (col1, col2, col3);

-- 作成された索引を確認
col index_name format a15
select index_name
from dba_indexes
where owner = 'IDX_TEST';

--== 逆順での作成を検証(索引 --> 制約) ==--
-- 表を削除
drop table idx_test.test_tbl;

-- 表作成
create table idx_test.test_tbl(
  col1 varchar(10),
  col2 varchar(10),
  col3 varchar(10)
);

-- 索引作成
create index idx_test.idx on idx_test.test_tbl (col1, col2, col3);

-- 主キー制約付与
alter table idx_test.test_tbl add constraint pk1 primary key (col1, col2) using index;

-- 作成された索引を確認
col index_name format a15
select index_name
from dba_indexes
where owner = 'IDX_TEST';

検証ログ

下準備

SQL> --== 下準備 ==--
-- 表領域作成
create tablespace idx_test_tbs datafile '/u01/app/oracle/oradata/orcl/idx_test_tbs.dbf' size 100M;SQL> SQL> 

表領域が作成されました。

SQL> -- ユーザー作成
create user idx_test
identified by "idx_test"
default tablespace idx_test_tbs
temporary tablespace temp;SQL>   2    3    4  

ユーザーが作成されました。

SQL> -- 権限付与
grant dba to idx_test;SQL> 

権限付与が成功しました。

制約→索引の順で作成

SQL> --== 検証(制約 --> 索引) ==--
-- 表作成
create table idx_test.test_tbl(
  col1 varchar(10),
  col2 varchar(10),
  col3 varchar(10)
);SQL> SQL>   2    3    4    5  

表が作成されました。

SQL> -- 主キー制約付与
alter table idx_test.test_tbl add constraint pk1 primary key (col1, col2) using index;SQL> 

表が変更されました。

SQL> -- 索引作成
create index idx_test.idx on idx_test.test_tbl (col1, col2, col3);SQL> 

索引が作成されました。

SQL> -- 作成された索引を確認
col index_name format a15
select index_name
from dba_indexes
where owner = 'IDX_TEST';SQL> SQL>   2    3  

INDEX_NAME
---------------
PK1  ★
IDX  ★

主キー制約の索引と、その後作成された索引が２つ存在している事が確認できます。

索引→制約の順で作成

SQL> --== 逆順での作成を検証(索引 --> 制約) ==--
-- 表を削除
drop table idx_test.test_tbl;SQL> SQL> 

表が削除されました。

SQL> -- 表作成
create table idx_test.test_tbl(
  col1 varchar(10),
  col2 varchar(10),
  col3 varchar(10)
);SQL>   2    3    4    5  

表が作成されました。

SQL> -- 索引作成
create index idx_test.idx on idx_test.test_tbl (col1, col2, col3);SQL> 

索引が作成されました。

SQL> -- 主キー制約付与
alter table idx_test.test_tbl add constraint pk1 primary key (col1, col2) using index;SQL> 

表が変更されました。

SQL> -- 作成された索引を確認
col index_name format a15
select index_name
from dba_indexes
where owner = 'IDX_TEST'; SQL> SQL>   2    3  

INDEX_NAME
---------------
IDX  ★

主キー制約の索引が作成されていない事が確認できます。

検証にてリファレンス記載の下記仕様を確認する事ができた。

主キー制約を作成する場合・Oracle Databaseでは、主キー制約を適用する前に既存の索引に一意の値の集合が含まれる場合、その索引が使用されます。既存の索引は、一意であると定義されている場合も、一意でないと定義されている場合もあります。