『ORACLE』 约束实验（11g）

not null约束只能在列级定义；可以指定not null约束的名称，如不指定则自动生成名称

SQL> create table t1 (x number not null, y number constraint nn_t111_y not null);

Table created.

SQL> select constraint_name,constraint_type,search_condition from user_constraints where table_name = upper('t1');

CONSTRAINT_NAME              C  SEARCH_CONDITIO
------------------------------ -- ---------------------
SYS_C0011402                    C "X" IS NOT NULL
NN_T111_Y                          C "Y" IS NOT NULL

SQL> create table student1 (sno number(5) not null, sname varchar2(20) not null constraint uk_student_sname1 unique,idcard varchar2(18),createtime 
date default sysdate not null,constraint uk_student_idcard1 unique(idcard));

Table created.

SQL> create table student2 (sno number(5) not null, sname varchar2(20) not null unique,idcard varchar2(18),createtime date default sysdate not null,

constraint uk_student_idcard2 unique(idcard));

Table created.

SQL> select table_name,column_name,constraint_name from user_cons_columns where table_name like upper('student_');

TABLE_NAME COLUMN_NAME CONSTRAINT_NAME
--------------- --------------- ---------------
STUDENT1 SNO SYS_C0011404
STUDENT1 SNAME SYS_C0011405
STUDENT1 CREATETIME SYS_C0011406
STUDENT1 SNAME UK_STUDENT_SNAM
E1

STUDENT1 IDCARD UK_STUDENT_IDCA
RD1

STUDENT2 SNO SYS_C0011409
STUDENT2 SNAME SYS_C0011410
STUDENT2 CREATETIME SYS_C0011411
STUDENT2 SNAME SYS_C0011412
STUDENT2 IDCARD UK_STUDENT_IDCARD2

主键约束

1、行级定义主键，系统分配主键名称

create table t_pk1 
(sno number(5) primary key,
sname varchar2(20),
idcard varchar2(18),
createtime date
);

2、表级定义主键，自定义主键名曾

create table t_pk2
(sno number(5),
sname varchar2(20),
idcard varchar2(18),
createtime date,
constraint pk_t_pk2_sno primary key(sno)
);

 

3、表级定义主键，系统分配主键名称

create table t_pk3
(sno number(5),
sname varchar2(20),
idcard varchar2(18),
createtime date,
primary key(sno)
);

4、行级定义主键，自定义主键名称，并且指定索引使用的表空间
create table t_pk4 
(sno number(5) constraint pk_t_pk4_sno primary key using index tablespace example,
sname varchar2(20),
idcard varchar2(18),
createtime date
);
5、表级定义主键，自定义主键名称，并且指定索引使用的表空间
create table t_pk5
(sno number(5),
sname varchar2(20),
idcard varchar2(18),
createtime date,
constraint pk_t_pk5_sno primary key(sno) using index tablespace example
);

SQL> select table_name,column_name,constraint_name from user_cons_columns 
2 where table_name like upper('t_pk_');

查看主键情况

TABLE_NAME COLUMN_NAME CONSTRAINT_NAME
--------------- --------------- ---------------
T_PK1 SNO SYS_C0011414
T_PK2 SNO PK_T_PK2_SNO
T_PK3 SNO SYS_C0011416
T_PK4 SNO PK_T_PK4_SNO
T_PK5 SNO PK_T_PK5_SNO

查看主键自动创建索引情况

SQL> select index_name,uniqueness,tablespace_name 
2 from user_indexes 
3 where table_name like upper('t_pk_');

INDEX_NAME UNIQUENES TABLESPACE_NAME
------------------------------ --------- ------------------------------
SYS_C0011414 UNIQUE USERS
PK_T_PK2_SNO UNIQUE USERS
SYS_C0011416 UNIQUE USERS
PK_T_PK5_SNO UNIQUE EXAMPLE
PK_T_PK4_SNO UNIQUE EXAMPLE

清理环境

drop table t_pk1 purge;
drop table t_pk2 purge;
drop table t_pk3 purge;
drop table t_pk4 purge;
drop table t_pk5 purge;

命令行增加主键，自定义主键名称

table t_pk6 
(sno number(5),
sname varchar2(20),
idcard varchar2(18),
createtime date
); 
alter table t_pk6 add constraint pk_t_pk6_sno primary key(sno);

命令行增加主键，自定义主键名称，并且指定自动创建的索引表空间

create table t_pk7 
(sno number(5),
sname varchar2(20),
idcard varchar2(18),
createtime date
);

添加check约束，保证所有的名字都为大写
alter table t_pk7 add constratint pk_t_pk7_sno primary key(sno) using index tablespace example;

SQL> create table t115
  2    (id number(5),
  3     name varchar(20) constraint ck_t115_name check(name = upper(name)),
  4     createtime date default sysdate
  5     );
   
Table created.

SQL> insert into t115 values (1,'SL',null);

1 row created.

SQL> insert into t115 values (2,'ss',sysdate);
insert into t115 values (2,'ss',sysdate)
*
ERROR at line 1:
ORA-02290: check constraint (HR.CK_T115_NAME) violated

 

设置表的默认值，并创建唯一键约束

create table t1161 as select * from employees;
ALTER TABLE t1161 MODIFY LAST_NAME NULL;
ALTER TABLE t1161 MODIFY EMAIL NULL;
ALTER TABLE t1161 MODIFY HIRE_DATE NULL;
ALTER TABLE T1161 MODIFY job_id NULL;

alter table t1161 add constraint uk_email unique (email);

唯一键创建后会自动创建一个唯一索引
select index_name,index_type,uniqueness from user_indexes where table_name = 't1161';

alter table t1161 add constraint fk_emp_manager foreign key(manager_id) references t1161 (employee_id);