学习Oracle数据库（4）在表上建立不同类型的约束
在表上建立不同类型的约束
掌握oracle提供的五种约束
l Not null
l 唯一约束UNIQUE
l Check检测约束
l Primary key 主健约束
l foreign key外健
1、Not null
定义在表的列上，表明该列必须要有值，不能为null
可以在建立表的时候说明
也可以在表建立后修改为not null
可以给约束指定名称。
如果不指定名称，数据库会给一个系统自动指定名称，SYS_C#######
User_constraints,user_cons_columns可以查看到约束的信息
建立表的时候指定not null约束
connect scott/tiger
drop table t1 purge;
create table t1 (name char(9) not null,
telenum char(8) constraint t1_tele_nl not null);
建立表的时候指明非空约束，一个是系统命名，一个是我们命名。
select CONSTRAINT_NAME,CONSTRAINT_type,TABLE_NAME,column_name
from user_constraints natural join user_cons_columns;
CONSTRAINT_NAME C TABLE_NAME COLUMN_NAME
------------------------------ - ------------------------------ -----------
PK_DEPT P DEPT DEPTNO
FK_DEPTNO R EMP DEPTNO
PK_EMP P EMP EMPNO
T1_TELE_NL C T1 TELENUM
SYS_C005978 C T1 NAME
我们通过数据字典来验证我们的实验结果。
建立表后指定not null约束。要使用modify语法。你不指定名称，数据库自己命名。
connect scott/tiger
drop table t1 purge;
create table t1 as select * from dept;
alter table t1 modify(dname not null);
2、唯一约束UNIQUE
列的值不能重复
可以为NULL
是用索引来维护唯一的
索引的名称和约束的名称相同
建立表的时候指定UNIQUE约束
connect scott/tiger
drop table t1 PURGE;
create table t1 (name char(9) UNIQUE,
mail char(8) constraint t1_mail_u UNIQUE);
建立表的时候指明唯一约束，一个是系统命名，一个是我们命名。
select CONSTRAINT_NAME,CONSTRAINT_type,TABLE_NAME,column_name
from user_constraints natural join user_cons_columns;
CONSTRAINT_NAME C TABLE_NAME COLUMN_NAME
------------------------------ - ------------------------------ -----------
PK_DEPT P DEPT DEPTNO
FK_DEPTNO R EMP DEPTNO
PK_EMP P EMP EMPNO
SYS_C005980 U T1 NAME
T1_MAIL_U U T1 MAIL
建立表之后指定UNIQUE约束
connect scott/tiger
drop table t1 purge;
create table t1 as select * from dept;
alter table t1 add constraint u_dname unique (dname);
select CONSTRAINT_NAME,CONSTRAINT_type,TABLE_NAME,column_name
from user_constraints natural join user_cons_columns;
CONSTRAINT_NAME C TABLE_NAME COLUMN_NAME
------------------------------ - ------------------------------ -----------
PK_DEPT P DEPT DEPTNO
FK_DEPTNO R EMP DEPTNO
PK_EMP P EMP EMPNO
U_DNAME U T1 DNAME
Select table_name,index_name,column_name from user_ind_columns;
TABLE_NAME INDEX_NAME COLUMN_NAME
------------------------------ ------------------------------ -----------
DEPT PK_DEPT DEPTNO
EMP PK_EMP EMPNO
T1 U_DNAME DNAME
3、Check检测约束
Drop table t1 purge;
Create table t1(name varchar2(8)
check (length(name)>4),
Mail varchar2(10) );
alter table t1 add constraint check_mail check (length(mail)>4);
insert into t1 (name,mail)values('abc','sdsfs');
ERROR at line 1:
ORA-02290: check constraint (SCOTT.SYS_C005983) violated
insert into t1 (name,mail)values('abcsdfs','sds');
ERROR at line 1:
ORA-02290: check constraint (SCOTT.CHECK_MAIL) violated
4、Primary key 主健约束
一个表只能有一个主健
主健要求唯一并且非空
可以是联合主健，联合主健每列都要求非空
主健能唯一定位一行,所以主键也叫逻辑rowid
主健不是必需的，可以没有
主健是通过索引实现的
索引的名称和主健名称相同
建立表的时候指定主健，系统命名
drop table t1 purge;
create table t1(mail char(8) primary key,name char(8));
select CONSTRAINT_NAME,CONSTRAINT_type,TABLE_NAME,column_name
from user_constraints natural join user_cons_columns;
CONSTRAINT_NAME C TABLE_NAME COLUMN_NAME
------------------------------ - ------------------------------ -----------
PK_DEPT P DEPT DEPTNO
FK_DEPTNO R EMP DEPTNO
PK_EMP P EMP EMPNO
SYS_C005985 P T1 MAIL
Select table_name,index_name,column_name from user_ind_columns;
TABLE_NAME INDEX_NAME COLUMN_NAME
------------------------------ ------------------------------ -----------
DEPT PK_DEPT DEPTNO
EMP PK_EMP EMPNO
T1 SYS_C005985 MAIL
表建立后指定自命名的主健
drop table t1 purge;
create table t1(mail char(8) ,name char(8));
alter table t1 add constraint pk_t1_mail primary key (mail) ;
select CONSTRAINT_NAME,CONSTRAINT_type,TABLE_NAME,column_name
from user_constraints natural join user_cons_columns;
CONSTRAINT_NAME C TABLE_NAME COLUMN_NAME
------------------------------ - ------------------------------ -----------
PK_DEPT P DEPT DEPTNO
FK_DEPTNO R EMP DEPTNO
PK_EMP P EMP EMPNO
PK_T1_MAIL P T1 MAIL
Select table_name,index_name,column_name from user_ind_columns;
TABLE_NAME INDEX_NAME COLUMN_NAME
------------------------------ ------------------------------ -------------
DEPT PK_DEPT DEPTNO
EMP PK_EMP EMPNO
T1 PK_T1_MAIL MAIL
5、foreign key外健
指定在表的列上
引用本表其它列，或其它表的其它列
被引用的列得有唯一约束或者主健约束，因为引用的是索引的键值，而不是真正的表。
目的是维护数据的完整性
核心是一列是另外一列的子集，null除外
建立主健，建立一个外健来引用主健
conn scott/tiger
drop table e purge;
drop table d purge;
create table d as select * from dept;
create table e as select * from emp;
alter table d add constraint pk_d primary key (deptNO) ;
alter table e add constraint fk_e foreign key (deptno) references d(deptno) ;
违反约束
delete d where deptno=10
ORA-02292: 违反完整约束条件 (SCOTT.FK_E) - 已找到子记录
update e set deptno=50
ORA-02291: 违反完整约束条件 (SCOTT.FK_E) - 未找到父项关键字
建立被级连的外健
alter table e drop constraint FK_E;
alter table e add constraint fk_e foreign key (deptno)
references d(deptno) on delete set null;
父表的值被删除，子表的相关列自动被赋予NULL值。
alter table e drop constraint FK_E;
alter table e add constraint fk_e foreign key (deptno)
references d(deptno) on delete cascade;
父表的值被删除，子表的相关行自动被删除。
6、删除约束
任何约束都可以用约束名称来删除
Alter table ### drop constraint ****;
因为主健只能有一个，所以删除主健约束的时候也可以
Alter table ### drop primary key;
如果主健和唯一性约束被删除，自动建立的索引也会同时被清除。
Not Null约束也可以用alter table modify来删除。
删除有外健引用的主健或唯一约束的时候，外健也要被级连删除。
Alter table ### drop primary key cascade;
如果不加cascade，你删不了，报有外键在使用，不能删除。
按约束的名称来删除约束（可以删除各种约束）
Alter table t1 drop constraint sys_c03033;
非空约束的第二种删除方式
alter table t1 modify (name not null);
alter table t1 modify (name null);
