一：创建测试表student
SCOTT@ORA11GR2>create table student
(
sno number(5),
sname varchar2(20),
idcard varchar2(18)
);
Table created.
SCOTT@ORA11GR2>
二：创建主键
SCOTT@ORA11GR2>alter table student add constraint pk_student_sno primary key(sno);
Table altered.
SCOTT@ORA11GR2>
三：创建唯一约束
SCOTT@ORA11GR2>alter table student add constraint uk_student_sname unique(sname);
Table altered.
SCOTT@ORA11GR2>
四：创建唯一索引
SCOTT@ORA11GR2>create unique index uidx_student_idcard on student(idcard) tablespace users;
Index created.
SCOTT@ORA11GR2>
五：约束的测试
SCOTT@ORA11GR2>insert into student(sno,sname,idcard) values(1,'zhangsan','123456');
1 row created.
SCOTT@ORA11GR2>commit;
Commit complete.
SCOTT@ORA11GR2>
1）sno重复：
SCOTT@ORA11GR2>insert into student(sno,sname,idcard) values(1,'lisi','110102');
insert into student(sno,sname,idcard) values(1,'lisi','110102')
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.PK_STUDENT_SNO) violated
SCOTT@ORA11GR2>
2）sname重复：
SCOTT@ORA11GR2>insert into student(sno,sname,idcard) values(2,'zhangsan','110102');
insert into student(sno,sname,idcard) values(2,'zhangsan','110102')
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.UK_STUDENT_SNAME) violated
SCOTT@ORA11GR2>
3）idcard重复：
SCOTT@ORA11GR2>insert into student(sno,sname,idcard) values(2,'lisi','123456');
insert into student(sno,sname,idcard) values(2,'lisi','123456')
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.IDX_STUDENT_IDCARD) violated
SCOTT@ORA11GR2>
4）sno为null值
SCOTT@ORA11GR2>insert into student(sno,sname,idcard) values(null,'lisi','78910JQK');
insert into student(sno,sname,idcard) values(null,'lisi','78910JQK')
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("SCOTT"."STUDENT"."SNO")
SCOTT@ORA11GR2>
5）sname、idcard为null值
SCOTT@ORA11GR2>insert into student(sno,sname,idcard) values(2,null,null);
1 row created.
SCOTT@ORA11GR2>commit;
Commit complete.
SCOTT@ORA11GR2>
六：查看一下字典表中，约束的情况
SCOTT@ORA11GR2>col table_name for a15
SCOTT@ORA11GR2>col column_name for a15
SCOTT@ORA11GR2>col constraint_name for a20
SCOTT@ORA11GR2>select table_name,column_name,constraint_name from user_cons_columns where table_name=upper('student');
TABLE_NAME COLUMN_NAME CONSTRAINT_NAME
--------------- --------------- --------------------
STUDENT SNAME UK_STUDENT_SNAME
STUDENT SNO PK_STUDENT_SNO
SCOTT@ORA11GR2>
七：查看一下字典表中，索引的情况
SCOTT@ORA11GR2>select table_name,column_name,index_name from user_ind_columns where table_name=upper('student');
TABLE_NAME COLUMN_NAME INDEX_NAME
--------------- --------------- --------------------
STUDENT SNO PK_STUDENT_SNO
STUDENT SNAME UK_STUDENT_SNAME
STUDENT IDCARD UIDX_STUDENT_IDCARD
SCOTT@ORA11GR2>
SCOTT@ORA11GR2>select index_name,uniqueness,tablespace_name from user_indexes where table_name=upper('student');
INDEX_NAME UNIQUENES TABLESPACE_NAME
-------------------- --------- ---------------
IDX_STUDENT_IDCARD UNIQUE USERS
UK_STUDENT_SNAME UNIQUE USERS
PK_STUDENT_SNO UNIQUE USERS
SCOTT@ORA11GR2>
小结：
1：主键不允许为null，且不能重复
2：唯一约束、唯一索引允许为空但不能重复（另：一个字段中可以存在两条以上null值，因为null^=null)
3：在创建主键和唯一索引的同时，都各自自动创建了一个唯一索引
另：主键和唯一索引可以在创建表的时候一并创建，且可以在字段级创建也可以在表级创建，当然，等表建好后，在命令行中创建，索引却只能在命令行中创建。
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/685769/viewspace-743314/ ，如需转载，请注明出处，否则将追究法律责任。
请登录后发表评论
登录
全部评论
