Oracle学习笔记（四）——Primary Key&Unique约束

Primary Key

primary key主键唯一表示一条记录，主键所包含的列（单个或多个）不能有重复值，所包含的列也不能为null。

创建测试所有的表test_constraint_tab

create table test_constraint_tab as select * from dba_objects;

查看test_constraint_tab的索引信息和约束信息

SQL> select index_name, index_type, table_name, table_owner from user_indexes wh ere table_name = upper('test_constraint_tab');

未选定行

SQL> select owner, constraint_name, constraint_type, table_name from user_constr aints where table_name = upper('test_constraint_tab');

未选定行

test_constraint_tab现在还没有任何索引和约束信息，下面添加primary key约束后再查看索引和约束信息

SQL> alter table test_constraint_tab add constraint pk_object_id primary key(obj ect_id);

表已更改。

SQL> select index_name, index_type, table_name, table_owner from user_indexes where table_name = upper('test_constraint_tab');

INDEX_NAME                     INDEX_TYPE                  TABLE_NAME                     TABLE_OWNER
------------------------------ --------------------------- ------------------------------ ------------------------------
PK_OBJECT_ID                   NORMAL                      TEST_CONSTRAINT_TAB            SYS

SQL> select owner, constraint_name, constraint_type, table_name from user_constraints where table_name = upper('test_constraint_tab');

OWNER                 CONSTRAINT_NAME                C TABLE_NAME
--------------------- ------------------------------ - ------------------------------
SYS                   PK_OBJECT_ID                   P TEST_CONSTRAINT_TAB

给表添加主键约束后自动创建了主键约束PK_OBJECT_ID和索引PK_OBJECT_ID。接着删除primary key约束，在查看索引和约束情况

SQL> alter table test_constraint_tab drop constraint pk_object_id;

表已更改。

SQL> select index_name, index_type, table_name, table_owner from user_indexes where table_name = upper('test_constraint_tab');

未选定行

SQL> select owner, constraint_name, constraint_type, table_name from user_constraints where table_name = upper('test_constraint_tab');

未选定行

可以看出删除约束后关联删除了自动创建的索引。接下来先在object_id列创建普通索引，再添加约束，然后再删除约束查看索引和约束情况

SQL> create index pk_object_id on test_constraint_tab(object_id);

索引已创建。

SQL> select index_name, index_type, table_name, table_owner from user_indexes where table_name = upper('test_constraint_tab');

INDEX_NAME                     INDEX_TYPE                  TABLE_NAME                     TABLE_OWNER
------------------------------ --------------------------- ------------------------------ ------------------------------
PK_OBJECT_ID                   NORMAL                      TEST_CONSTRAINT_TAB            SYS

SQL> alter table test_constraint_tab add constraint pk_object_id primary key(object_id);

表已更改。

SQL> select owner, constraint_name, constraint_type, table_name from user_constraints where table_name = upper('test_constraint_tab');

OWNER          CONSTRAINT_NAME                C TABLE_NAME
-------------- ------------------------------ - ------------------------------
SYS            PK_OBJECT_ID                   P TEST_CONSTRAINT_TAB

SQL> alter table test_constraint_tab drop constraint pk_object_id;

表已更改。

SQL> select index_name, index_type, table_name, table_owner from user_indexes where table_name = upper('test_constraint_tab');

INDEX_NAME                     INDEX_TYPE                  TABLE_NAME                     TABLE_OWNER
------------------------------ --------------------------- ------------------------------ ------------------------------
PK_OBJECT_ID                   NORMAL                      TEST_CONSTRAINT_TAB            SYS

SQL> select owner, constraint_name, constraint_type, table_name from user_constraints where table_name = upper('test_constraint_tab');

未选定行

从上面执行的结果可以看出，可以先创建索引再添加同名的约束，采用这种方式创建约束后在drop掉约束索引不会自动删除。

继续进行测试，删除test_constraint_tab的索引和约束后，重新创建主键约束，然后尝试直接drop掉自动创建的索引

SQL> alter table test_constraint_tab add constraint pk_object_id primary key(object_id);

表已更改。

SQL> drop index PK_OBJECT_ID;
drop index PK_OBJECT_ID
           *
第 1 行出现错误:
ORA-02429: 无法删除用于强制唯一/主键的索引

SQL> alter table test_constraint_tab drop constraint pk_object_id;

表已更改。

SQL> create index pk_object_id on test_constraint_tab(object_id);

索引已创建。

SQL> alter table test_constraint_tab add constraint pk_object_id primary key(object_id);

表已更改。

SQL> drop index PK_OBJECT_ID;
drop index PK_OBJECT_ID
           *
第 1 行出现错误:
ORA-02429: 无法删除用于强制唯一/主键的索引

从测试可以看出只要主键约束存在，不管是自动创建的索引还是在创建约束之前创建的索引都不能被删除。

Primary Key包含的列不允许存在NULL值。

SQL> create table test_constraint_tab2 (
  2     col1 varchar(20),
  3     col2 varchar(20),
  4     col3 varchar(20)
  5  );

表已创建。

SQL> alter table test_constraint_tab2 add constraint pk_col1_col2 primary key(co l1, col2);

表已更改。

SQL> insert into test_constraint_tab2 (col1, col2, col3) values (null, null, 'col3_1');
insert into test_constraint_tab2 (col1, col2, col3) values (null, null, 'col3_1'
)
                                                            *
第 1 行出现错误:
ORA-01400: 无法将 NULL 插入 ("SYS"."TEST_CONSTRAINT_TAB2"."COL1")


SQL> insert into test_constraint_tab2 (col1, col2, col3) values ('col1_1', null, 'col3_1');
insert into test_constraint_tab2 (col1, col2, col3) values ('col1_1', null, 'col
3_1')
                                                                      *
第 1 行出现错误:
ORA-01400: 无法将 NULL 插入 ("SYS"."TEST_CONSTRAINT_TAB2"."COL2")


SQL> insert into test_constraint_tab2 (col1, col2, col3) values (null, 'col2_1', 'col3_1');
insert into test_constraint_tab2 (col1, col2, col3) values (null, 'col2_1', 'col
3_1')
                                                            *
第 1 行出现错误:
ORA-01400: 无法将 NULL 插入 ("SYS"."TEST_CONSTRAINT_TAB2"."COL1")

Unique约束

SQL> alter table test_constraint_tab add constraint unique_object_id unique(object_id);

表已更改。

SQL> select index_name, index_type, table_name, table_owner from user_indexes where table_name = upper('test_constraint_tab');

INDEX_NAME                     INDEX_TYPE                  TABLE_NAME                     TABLE_OWNER
------------------------------ --------------------------- ------------------------------ ------------------------------
UNIQUE_OBJECT_ID               NORMAL                      TEST_CONSTRAINT_TAB            SYS

SQL> select owner, constraint_name, constraint_type, table_name from user_constraints where table_name = upper('test_constraint_tab');

OWNER      CONSTRAINT_NAME                C TABLE_NAME
---------- ------------------------------ - ------------------------------
SYS        UNIQUE_OBJECT_ID               U TEST_CONSTRAINT_TAB

SQL> alter table test_constraint_tab drop constraint unique_object_id;

表已更改。

SQL> select index_name, index_type, table_name, table_owner from user_indexes where table_name = upper('test_constraint_tab');

未选定行

SQL> select owner, constraint_name, constraint_type, table_name from user_constraints where table_name = upper('test_constraint_tab');

未选定行

SQL> create index unique_object_id on test_constraint_tab(object_id);

索引已创建。

SQL> alter table test_constraint_tab add constraint unique_object_id unique(object_id);

表已更改。

SQL> select index_name, index_type, table_name, table_owner from user_indexes where table_name = upper('test_constraint_tab');

INDEX_NAME                     INDEX_TYPE                  TABLE_NAME                     TABLE_OWNER
------------------------------ --------------------------- ------------------------------ ------------------------------
UNIQUE_OBJECT_ID               NORMAL                      TEST_CONSTRAINT_TAB            SYS

SQL> select owner, constraint_name, constraint_type, table_name from user_constraints where table_name = upper('test_constraint_tab');

OWNER        CONSTRAINT_NAME                C TABLE_NAME
------------ ------------------------------ - ------------------------------
SYS          UNIQUE_OBJECT_ID               U TEST_CONSTRAINT_TAB

SQL> alter table test_constraint_tab drop constraint unique_object_id;

表已更改。

SQL> select index_name, index_type, table_name, table_owner from user_indexes where table_name = upper('test_constraint_tab');

INDEX_NAME                     INDEX_TYPE                  TABLE_NAME                     TABLE_OWNER
------------------------------ --------------------------- ------------------------------ ------------------------------
UNIQUE_OBJECT_ID               NORMAL                      TEST_CONSTRAINT_TAB            SYS

SQL> select owner, constraint_name, constraint_type, table_name from user_constraints where table_name = upper('test_constraint_tab');

未选定行

可以看出创建Unique约束后在约束列也会自动创建索引，而且删除约束后索引也自动删除。先创建索引后可以创建同名的unique约束，但是删除约束后索引不会自动被删除。同样，存在unique约束无法删除其对应的索引。

SQL> drop index unique_object_id;
drop index unique_object_id
           *
第 1 行出现错误:
ORA-02429: 无法删除用于强制唯一/主键的索引

Unique约束允许包含的列存在NULL值

SQL> alter table test_constraint_tab2 add constraint unique_col3 unique(col3);

表已更改。

SQL> insert into test_constraint_tab2 (col1, col2, col3) values ('col1_1', 'col2_1', null);

已创建 1 行。

SQL> insert into test_constraint_tab2 (col1, col2, col3) values ('col1_2', 'col2_2', null);

已创建 1 行。

此外Oracle不允许在相同的列上既创建Primary Key约束又创建Unique约束。