Oracle主鍵及約束

標籤：primary   oracle   

Oracle主鍵Primary Key包含非空約束及唯一約束。

添加主鍵的語句

alter table table_name add constraint cons_name primary key(col_name);

查看主鍵是否被建立成功

select dbms_metadata.get_ddl(‘OBJECT_TYPE‘,‘NAME‘,‘SCHEMA‘) from dual;

OBJECT_TYPE (TABLE,PARTITION,INDEX......)

NAME (OBJECT_NAME)

SCHEMA (INSTANCE)

對應object表中的相應內容：

select object_type,object_name,owner from dba_objects

 

擷取到的DDL語句中表示已經建立了主鍵列，該列位於CUSTTEL上

CREATE TABLE "NEWCCS"."USERLEVELBYTEL_BAK"    ( "CUSTTEL" VARCHAR2(20),  "USERLEVEL" NUMBER(*,0) NOT NULL ENABLE,  "CUSTID" VARCHAR2(20)   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)  TABLESPACE "NEWCCS" ;   ALTER TABLE "NEWCCS"."USERLEVELBYTEL_BAK" MODIFY ("USERLEVEL" NOT NULL ENABLE);

建立主鍵後發現該表增加了唯一索引及主鍵欄位

CREATE TABLE "NEWCCS"."USERLEVELBYTEL_BAK"    ( "CUSTTEL" VARCHAR2(20),  "USERLEVEL" NUMBER(*,0) NOT NULL ENABLE,  "CUSTID" VARCHAR2(20),   CONSTRAINT "PK_PRIMARY" PRIMARY KEY ("CUSTTEL")  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255   STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)  TABLESPACE "NEWCCS"  ENABLE   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)  TABLESPACE "NEWCCS" ;   CREATE UNIQUE INDEX "NEWCCS"."PK_PRIMARY" ON "NEWCCS"."USERLEVELBYTEL_BAK" ("CUSTTEL")   PCTFREE 10 INITRANS 2 MAXTRANS 255   STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)  TABLESPACE "NEWCCS" ;   ALTER TABLE "NEWCCS"."USERLEVELBYTEL_BAK" ADD CONSTRAINT "PK_PRIMARY" PRIMARY KEY ("CUSTTEL")  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255   STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)  TABLESPACE "NEWCCS"  ENABLE;   ALTER TABLE "NEWCCS"."USERLEVELBYTEL_BAK" MODIFY ("USERLEVEL" NOT NULL ENABLE);

 

驗證：

反覆向表中插入資料 insert into userlevelbytel values(‘10010‘,‘3‘,‘ ‘);

出現如下錯誤提示（唯一約束錯誤）：

ORA-00001: unique constraint (NEWCCS.PK_PRIMARY) violated

插入空值 insert into userlevelbytel values(‘‘,‘3‘,‘ ‘);

出現如下錯誤提示（唯一約束錯誤）：

ORA-01400: cannot insert NULL into ("NEWCCS"."USERLEVELBYTEL_BAK"."CUSTTEL")

 

單獨添加唯一約束對比區別

刪除該表主鍵：

alter table userlevelbytel_bak drop constraint pk_primary;

CREATE TABLE "NEWCCS"."USERLEVELBYTEL_BAK"    ( "CUSTTEL" VARCHAR2(20),  "USERLEVEL" NUMBER(*,0) NOT NULL ENABLE,  "CUSTID" VARCHAR2(20),   CONSTRAINT "UNIQUE_CUSTTEL" UNIQUE ("CUSTTEL")  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS   STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)  TABLESPACE "NEWCCS"  ENABLE   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)  TABLESPACE "NEWCCS" ;   CREATE UNIQUE INDEX "NEWCCS"."UNIQUE_CUSTTEL" ON "NEWCCS"."USERLEVELBYTEL_BAK" ("CUSTTEL")   PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS   STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)  TABLESPACE "NEWCCS" ;   ALTER TABLE "NEWCCS"."USERLEVELBYTEL_BAK" MODIFY ("USERLEVEL" NOT NULL ENABLE);   ALTER TABLE "NEWCCS"."USERLEVELBYTEL_BAK" ADD CONSTRAINT "UNIQUE_CUSTTEL" UNIQUE ("CUSTTEL")  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS   STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)  TABLESPACE "NEWCCS"  ENABLE;

可以看到唯一約束被建立

測試：

反覆插入非空記錄：

ORA-00001: unique constraint (NEWCCS.UNIQUE_CUSTTEL) violated

反覆插入空記錄：

select * from userlevelbytel_bak where custtel is null;

查詢結果為多個重複的空記錄

 

這裡面可以看出唯一約束僅對非空值進行約束，而在Oracle中NULL值無法比較，因此即使反覆插入，唯一約束也無法限制，但在組合約束條件下，比如（A,B,NULL）則是可以進行比較的。

舉例：

alter table userlevelbytel_bak add constraint unique_custtel unique(custtel,userlevel);

反覆插入空記錄：

ORA-00001: unique constraint (NEWCCS.UNIQUE_CUSTTEL) violated

本文出自 “LinuxOracle” 部落格，請務必保留此出處http://onlinekof2001.blog.51cto.com/3106724/1582962

Oracle主鍵及約束