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
郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。