Lynn | Lynn の世界

关于Oracle数据库表的主键，通常可以理解为非空且唯一，关于这种理解是否准确，请看下面的测试！

################################ 默认情况 #################################
lynn@10G> create table t1 (id number(8) primary key,name varchar2(10));

Table created.

lynn@10G> desc t1
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 ID                                                    NOT NULL NUMBER(8)
 NAME                                                           VARCHAR2(10)

lynn@10G> insert into t1 values (1,'aa');

1 row created.

lynn@10G> insert into t1 values (2,'bb');

1 row created.

lynn@10G> select * from t1;

        ID NAME
---------- ----------
         1 aa
         2 bb

lynn@10G> select INDEX_NAME,INDEX_TYPE,TABLE_OWNER,TABLE_NAME,TABLE_TYPE,UNIQUENESS from user_indexes where TABLE_NAME='T1';

INDEX_NAME      INDEX_TYPE TABLE_OWNE TABLE_NAME TABLE_TYPE  UNIQUENES
--------------- ---------- ---------- ---------- ----------- ---------
SYS_C005647     NORMAL     LYNN       T1         TABLE       UNIQUE

lynn@10G> select OWNER,CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME from user_constraints where TABLE_NAME='T1';

OWNER                          CONSTRAINT_NAME                C TABLE_NAME
------------------------------ ------------------------------ - ----------
LYNN                           SYS_C005647                    P T1

lynn@10G> drop index SYS_C005647;
drop index SYS_C005647
           *
ERROR at line 1:
ORA-02429: cannot drop index used for enforcement of unique/primary key

lynn@10G> alter table t1 drop primary key;

Table altered.

lynn@10G> select INDEX_NAME,INDEX_TYPE,TABLE_OWNER,TABLE_NAME,TABLE_TYPE,UNIQUENESS from user_indexes where TABLE_NAME='T1';

no rows selected

lynn@10G> select OWNER,CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME from user_constraints where TABLE_NAME='T1';

no rows selected


##################### 创建表时同时创建索引并指定给主键列 #####################
lynn@10G> CREATE TABLE t2
  2    (
  3      id NUMBER(8) PRIMARY KEY USING INDEX
  4      (CREATE INDEX t2_indx ON t2
  5        (id
  6        )
  7      ),
  8      name VARCHAR2(10)
  9    );

Table created.

lynn@10G> desc t2
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 ID                                                    NOT NULL NUMBER(8)
 NAME                                                           VARCHAR2(10)

lynn@10G> insert into t2 values(1,'aa');

1 row created.

lynn@10G> insert into t2 values(2,'bb');

1 row created.

lynn@10G> insert into t2 values(2,'bb');
insert into t2 values(2,'bb')
*
ERROR at line 1:
ORA-00001: unique constraint (LYNN.SYS_C005649) violated


lynn@10G> select * from t2;

        ID NAME
---------- ----------
         1 aa
         2 bb

lynn@10G> select INDEX_NAME,INDEX_TYPE,TABLE_OWNER,TABLE_NAME,TABLE_TYPE,UNIQUENESS from user_indexes where TABLE_NAME='T2';

INDEX_NAME      INDEX_TYPE TABLE_OWNE TABLE_NAME TABLE_TYPE  UNIQUENES
--------------- ---------- ---------- ---------- ----------- ---------
T2_INDX         NORMAL     LYNN       T2         TABLE       NONUNIQUE

lynn@10G> select OWNER,CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME from user_constraints where TABLE_NAME='T2';

OWNER                          CONSTRAINT_NAME                C TABLE_NAME
------------------------------ ------------------------------ - ----------
LYNN                           SYS_C005649                    P T2

lynn@10G> drop index T2_INDX;
drop index T2_INDX
           *
ERROR at line 1:
ORA-02429: cannot drop index used for enforcement of unique/primary key

lynn@10G> alter table t2 drop primary key;

Table altered.

lynn@10G> select INDEX_NAME,INDEX_TYPE,TABLE_OWNER,TABLE_NAME,TABLE_TYPE,UNIQUENESS from user_indexes where TABLE_NAME='T2';

INDEX_NAME      INDEX_TYPE TABLE_OWNE TABLE_NAME TABLE_TYPE  UNIQUENES
--------------- ---------- ---------- ---------- ----------- ---------
T2_INDX         NORMAL     LYNN       T2         TABLE       NONUNIQUE

lynn@10G> select OWNER,CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME from user_constraints where TABLE_NAME='T2';

no rows selected


################### 先创建表 | 再创建索引 | 再指定给主键列 ###################
lynn@10G> create table t3 (id number(8),name varchar2(10));

Table created.

lynn@10G> insert into t3 values (1,'aa');

1 row created.

lynn@10G> insert into t3 values (2,'bb');

1 row created.

lynn@10G> desc t3
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 ID                                                             NUMBER(8)
 NAME                                                           VARCHAR2(10)

lynn@10G> create bitmap index t3_indx on t3(id);

Index created.

lynn@10G> alter table t3 add primary key(id) using index t3_indx;
alter table t3 add primary key(id) using index t3_indx
*
ERROR at line 1:
ORA-14196: Specified index cannot be used to enforce the constraint.

lynn@10G> drop index t3_indx;

Index dropped.

lynn@10G> create index t3_indx on t3(id) reverse;

Index created.

lynn@10G> alter table t3 add primary key(id) using index t3_indx;

Table altered.

lynn@10G> desc t3
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 ID                                                    NOT NULL NUMBER(8)
 NAME                                                           VARCHAR2(10)

lynn@10G> select INDEX_NAME,INDEX_TYPE,TABLE_OWNER,TABLE_NAME,TABLE_TYPE,UNIQUENESS from user_indexes where TABLE_NAME='T3';

INDEX_NAME      INDEX_TYPE TABLE_OWNE TABLE_NAME TABLE_TYPE  UNIQUENES
--------------- ---------- ---------- ---------- ----------- ---------
T3_INDX         NORMAL/REV LYNN       T3         TABLE       NONUNIQUE

lynn@10G> select OWNER,CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME from user_constraints where TABLE_NAME='T3';

OWNER                          CONSTRAINT_NAME                C TABLE_NAME
------------------------------ ------------------------------ - ----------
LYNN                           SYS_C005651                    P T3

lynn@10G> drop index t3_indx;
drop index t3_indx
           *
ERROR at line 1:
ORA-02429: cannot drop index used for enforcement of unique/primary key

lynn@10G> alter table t3 drop constraint SYS_C005651;

Table altered.

lynn@10G> select OWNER,CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME from user_constraints where TABLE_NAME='T3';

no rows selected

lynn@10G> select INDEX_NAME,INDEX_TYPE,TABLE_OWNER,TABLE_NAME,TABLE_TYPE,UNIQUENESS from user_indexes where TABLE_NAME='T3';

INDEX_NAME      INDEX_TYPE TABLE_OWNE TABLE_NAME TABLE_TYPE  UNIQUENES
--------------- ---------- ---------- ---------- ----------- ---------
T3_INDX         NORMAL/REV LYNN       T3         TABLE       NONUNIQUE

lynn@10G> drop index t3_indx;

Index dropped.

lynn@10G> create index t3_indx on t3 (round(id,0));

Index created.

lynn@10G> alter table t3 add primary key(id) using index t3_indx;
alter table t3 add primary key(id) using index t3_indx
*
ERROR at line 1:
ORA-14196: Specified index cannot be used to enforce the constraint.

############################## 主键测试结论 ###############################
关于主键的官方解释链接：
http://docs.oracle.com/cd/E11882_01/server.112/e40540/datainte.htm#CNCPT322
http://docs.oracle.com/cd/E11882_01/server.112/e40540/datainte.htm#CNCPT1646
http://docs.oracle.com/cd/E11882_01/server.112/e41084/clauses002.htm#SQLRF52180

To enable a unique or primary key constraint, you must have the privileges necessary to create an index on the table. You need these privileges because Oracle Database creates an index on the columns of the unique or primary key in the schema containing the table.

primary key 可理解为如下形式：
not null constraint + unique constraint + index

1.虽然这个index可以不是unique，但却必须存在，对于Oracle DB而言，它认为这个index是unique的，这个索引不能是函数和位图索引；
2.如果主键列上的索引是人为指定创建的，当主键约束被删除后，主键列上的索引不会像默认情况那样也被删除；
3.通常理解的primary key是not null + unique index也没错，因为not null + unique index可以实现主键的作用，但是主键的唯一性不是通过主键列上的这个index来保证实现，而是通过主键约束本身来保证唯一性的！

insert into B_F_OSCK_HF_TRUST_LN_SL select to_char(sysdate, 'YYYY-MM-DD') RPT_DT, 'FXC201407100001' PRO_ID, '20141001-20141127-650670000-0001' BAT_NO, rownum DATA_NO, '1' ECP_TYP, --异常类型 am.SA_TX_DT, am.SA_TX_TM, am.Flag, decode(am.Flag, 'ACA', '内部帐流水', 'SAE', '企业活期流水') Flag_desc, am.TRAN_CD, am.OPEN_INST_NO, --开户机构 oi.INST_NAME_L4 OPEN_INST_NM, --开户机构名称 am.OPEN_TLR, --开户柜员 ot.TELLER_NAME, --开户柜员名称 am.bus_inst_no, bi.INST_NAME_L4 bus_inst_nm, --交易机构名称 am.BUS_TLR, bt.TELLER_NAME, --交易柜员名称 am.TX_LOG_NO, am.cust_acct_no, am.cust_name, --账户名称 am.CR_TX_AMT, am.ACCT_BAL, am.DR_CR_COD, am.DOC_TYP, am.BINO, am.DSCRP_COD, am.SA_RMRK_DESC, am.SA_RMRK, am.CM_OPP_ACCT_NO, am.SA_OP_CUST_NAME, am.SA_OP_BANK_NO, am.SA_OP_TX_NO, am.SA_OP_TX_NO_TYP, am.SA_TX_CRD_NO, am.TX_TYPE, am.CUST_NO, am.ACCT_NO, to_char(sysdate, 'YYYY-MM-DD hh24:mi:ss'), --数据加载时间 '', --备用1 '', --备用2 '', --备用3 '', --备用4 '' --备用5 from (select a.SA_TX_DT, to_char(to_timestamp(a.SA_TX_TM, 'hh24missff3'), 'hh24:mi:ss') SA_TX_TM, 'SAE' Flag, --记录类型,ACA内部帐流水，SAE企业活期流水 a.TRAN_CD, sae.INST_NO OPEN_INST_NO, --经营管理机构 sae.TLR_NO OPEN_TLR, --开户柜员号 a.BUS_INST_NO, a.XT_OP_TRL BUS_TLR, TX_LOG_NO, a.CUST_ACCT_NO, sae.CUST_NAME, a.CR_TX_AMT, a.ACCT_BAL, a.DR_CR_COD, a.DOC_TYP, a.BINO, a.DSCRP_COD, a.SA_RMRK_DESC, a.SA_RMRK, a.CM_OPP_ACCT_NO, a.SA_OP_CUST_NAME, a.SA_OP_BANK_NO, a.SA_OP_TX_NO, a.SA_OP_TX_NO_TYP, a.SA_TX_CRD_NO, a.TX_TYPE, a.CUST_NO, a.ACCT_NO FROM BF_EVT_DEP_SAE a left join odsbdata.BF_AGT_DEP_ACCT_SAE sae on sae.ACCT_NO = a.ACCT_NO inner join (select sae.sa_tx_dt, sae.acct_no from tmp_osck_sae_bal sae, tmp_osck_aca_sae_inst_false fal where sae.SA_TX_DT = fal.SA_TX_DT and sae.inst_no = fal.inst_no) b on a.SA_TX_DT = b.SA_TX_DT and a.acct_no = b.acct_no union all select BK_DATE, to_char(to_timestamp(CR_TX_TM, 'hh24missff3'), 'hh24:mi:ss') CR_TX_TM, 'ACA' Flag, --记录类型,ACA内部帐流水，SAE企业活期流水 '' TRAN_CD, aca.OPR_UNIT_CD open_inst_no, '' open_TLR, --没有开户柜员 nvl(trim(a2.TRAD_BRAN), substr(LN_TX_OPR_NO, 1, 9)) bus_inst_no, --交易机构 LN_TX_OPR_NO bus_tlr, TX_LOG_NO, GL_ACCT_NO, GL_ACCT_NAME, CR_AMT, BAL, DR_CR_COD, DOC_TYP, BINO, DSCRP_COD, SUMMARY, '' SA_RMRK, '' CM_OPP_ACCT_NO, '' SA_OP_CUST_NAME, '' SA_OP_BANK_NO, '' SA_OP_TX_NO, '' SA_OP_TX_NO_TYP, '' SA_TX_CRD_NO, '' TX_TYPE, '' CUST_NO, cust_acct_no ACCT_NO FROM BF_EVT_GL_INN_CCBS_TXN a2 left join BF_AGT_INN_CCBS_ACA aca on aca.CCBS_ACCT_NO = a2.GL_ACCT_NO inner join (select aca.sa_tx_dt, aca.acct_no from tmp_osck_aca_bal aca, tmp_osck_aca_sae_inst_false fal where aca.SA_TX_DT = fal.SA_TX_DT and aca.inst_no = fal.inst_no) b2 on a2.bk_date = b2.SA_TX_DT and a2.GL_ACCT_NO = b2.acct_no) am left join B_S_DIM_INST_CV bi on bi.INST_COD_L4 = am.bus_INST_NO left join B_F_CM_IEMP_TLR_V bt on bt.TELLER_ID = am.bus_tlr left join B_S_DIM_INST_CV oi on oi.INST_COD_L4 = am.open_INST_NO left join B_F_CM_IEMP_TLR_V ot on ot.TELLER_ID = am.OPEN_TLR where am.BUS_INST_NO in (select ORG_ID from B_F_DIM_MAP_INST_V start with ORG_ID = '650670000' connect by prior ORG_ID = UP_ORG_ID)

Lynn の世界

远方，除了遥远，还有梦想！

作者归档：Lynn

关于Lynn

关于Primary Key测试

ORA-7445 [qkaqkn]