ANBOB™

根据数据库三范式就是每个表建议要有主键，每个字段不可再分，表与之间不存在部分依赖,数据库的优化首先考虑的也是主键, 主键会在对应的字段加唯一约束、加not null,建 索引

常规增加主建

1，建表同时

SQL> create table test13 (id int primary key);

Table created.

2，建表后加字段时

SQL> alter table test12 add id2 int primary key;

Table altered.

3,主键字段存在时

SQL> select constraint_name from user_constraints where table_name='TEST12';

CONSTRAINT_NAME
------------------------------------------------------------
SYS_C0010610

SQL> alter table test12 drop constraint SYS_C0010610
2  ;

Table altered.

SQL> alter table test12 add id1 int;

Table altered.

SQL> alter table test12 add constraint pk_id1_t12  primary key(id1);

Table altered.

完成！

ps:如果在要加主键的字段的值中有空值或不唯一，就会报错，有空值时报

SQL> alter table test12 add constraint pk_id1_t12  primary key(id1);
alter table test12 add constraint pk_id1_t12  primary key(id1)
*
ERROR at line 1:
ORA-01449: column contains NULL values; cannot alter to NOT NULL

增加主键使用反转索引
创建表时

create table Test1(X number primary key
                 using index (create unique index T_PK on T(X) reverse));
				 
create table test2(c1 number primary key using index  reverse, name varchar2(10));

PK已存在时

SQL> create table test100(id int,name varchar2(10), primary key(id));
Table created.

SQL> @ind test100
Display indexes where table or index name matches %test100%...

TABLE_OWNER          TABLE_NAME                     INDEX_NAME                     POS# COLUMN_NAME                    DSC
-------------------- ------------------------------ ------------------------------ ---- ------------------------------ ----
SYS                  TEST100                        SYS_C008273                       1 ID

SQL> alter index SYS_C008273 rebuild reverse;
Index altered.

表存在，PK不存在时

SQL>  create table test100(id int,name varchar2(10));
Table created.

SQL> create index idx_r on test100(id)reverse;
Index created.

SQL>  alter table test100 add constraint pk_r_id primary key(id) using index idx_r;
Table altered.

-- or --

SQL>  alter table test100 add constraint pk_r_id primary key(id) using index (create index pk_r_id on test100(id) reverse);
Table altered.

分区表增加PK LOCAL NON UNIQUE INDEX

demo@ORA12C> create table anbob.t1
  partition by range( dt )
  ( partition p_2014 values less than
     ( to_date('01-Jan-2015','dd-mon-yyyy') ),
    partition p_2015 values less than
     ( to_date('01-Jan-2016','dd-mon-yyyy') ),
    partition p_2016 values less than
     ( to_date('01-Jan-2017','dd-mon-yyyy') ) )
  as
  select a.*,
     to_date('01-Jan-2014','dd-mon-yyyy') + mod(rownum,1080) dt
  from all_objects a;

Table created.

demo@ORA12C> create   index anbob.t1_idx on anbob.t1(dt,object_id) local(
      partition p_2014,
      partition p_2015,
      partition p_2016);

Index created.

demo@ORA12C> alter table anbob.t1 add constraint t1_pk
   primary key(dt,object_id)
   using index anbob.t1_idx;

Table altered.

— over —

打赏

微信扫一扫，打赏作者吧～