约束

概述

约束是数据库用来确保数据满足业务规则的手段不过在真正的企业开发中除了主键约束这类具有强需求的约束像外键约束检查约束更多时候仅仅出现在数据库设计阶段真实环境却很少应用更多是放到程序逻辑中去进行处理。这也比较容易理解约束会一定程度上较低数据库性能有些规则直接在程序逻辑中处理就可以了同时也有可能在面对业务变更或是系统扩展时数据库约束会使得处理不够方便。不过在我看来数据库约束是保证数据准确性的最后一道防线对于设计合理的系统处于性能考虑数据库约束自然可有可无不过若是面对关联关系较为复杂的系统且对系统而言数据的准确性完整性要高于性能要求那么这些约束还是有必要的否则就会出现各种相对业务规则来说莫名其妙的脏数据本人可是深有体会的。。。总之对于约束的选择无所谓合不合理需要根据业务系统对于准确性和性能要求的侧重度来决定。
数据库约束有五种
主键约束PRIMARY KEY
唯一性约束UNIQUE)
非空约束NOT NULL)
外键约束FOREIGN KEY)
检查约束CHECK)

主键约束PRIMARY KEY)

主键是定位表中单个行的方式可唯一确定表中的某一行关系型数据库要求所有表都应该有主键不过Oracle没有遵循此范例要求Oracle中的表可以没有主键这种情况不多见。关于主键有几个需要注意的点
键列必须必须具有唯一性且不能为空其实主键约束 相当于 UNIQUE+NOT NULL
一个表只允许有一个主键
主键所在列必须具有索引主键的唯一约束通过索引来实现如果不存在将会在索引添加的时候自动创建
添加主键约束的添加可在建表时创建也可如下所示在建表后添加一般推荐建表后添加灵活度更高一些建表时添加某些约束会有限制

 alter table emp add constraint emp_id_pk primary key(id);

唯一性约束UNIQUE)

唯一性约束可作用在单列或多列上对于这些列或列组合唯一性约束保证每一行的唯一性。
UNIQUE需要注意
对于UNIQUE约束来讲索引是必须的。如果不存在就自动创建一个UNIQUE的唯一性本质上是通过索引来保证的
UNIQUE允许null值UNIQUE约束的列可存在多个null。这是因为Unique唯一性通过btree索引来实现而btree索引中不包含null。当然这也造成了在where语句中用null值进行过滤会造成全表扫描。
添加唯一约束

SQL> alter table emp add constraint emp_code_uq unique(code);

非空约束NOT NULL)

非空约束作用的列也叫强制列。顾名思义强制键列中必须有值当然建表时候若使用default关键字指定了默认值则可不输入。
　　添加非空约束语法较特别

SQL> alter table emp modify ename not null;

外键约束FOREIGN KEY

外键约束定义在具有父子关系的子表中外键约束使得子表中的列对应父表的主键列用以维护数据库的完整性。不过出于性能和后期的业务系统的扩展的考虑很多时候外键约束仅出现在数据库的设计中实际会放在业务程序中进行处理。外键约束注意以下几点
　　外键约束的子表中的列和对应父表中的列数据类型必须相同列名可以不同
　　对应的父表列必须存在主键约束PRIMARY KEY或唯一约束UNIQUE
　　外键约束列允许NULL值对应的行就成了孤行了
　　其实很多时候不使用外键很多人认为会让删除操作比较麻烦比如要删除父表中的某条数据但某个子表中又有对该条数据的引用这时就会导致删除失败。我们有两种方式来优化这种场景
　　第一种方式简单粗暴删除的时候级联删除掉子表中的所有匹配行在创建外键时通过 on delete cascade 子句指定该外键列可级联删除

SQL> alter table emp add constraint emp_deptno_fk foreign key(deptno) references dept (deptno) on delete cascade;

第二种方式删除父表中的对应行会将对应子表中的所有匹配行的外键约束列置为NULL通过 on delete set null 子句实施

SQL> alter table emp add constraint emp_deptno_fk foreign key(deptno) references dept(deptno) on delete set null;

实际上外键约束列和对应的父表列可以在同一张表中常见的就是表的业务逻辑含义是一棵树最简单的例子如下id为主键idfid为父idfid存储对id的引用这种结构的表根据业务要求可通过Oracle的递归查询来获取这种层级关系

检查约束CHECK)

检查约束可用来实施一些简单的规则比如列值必须在某个范围内。检查的规则必须是一个结果为true或false 的表达式比如

SQL> alter table emp add constraint emp_sex_ck check(sex in('男','女'));

另外两种添加约束的方法

alter方法添加约束比较灵活但是有些业务可能会对用户权限做限制如果没有alter any table权限这个可能无法正常使用这时候还有另外两种添加约束的方法。
我们可以采用在表内添加外键的方式来解决
一种是无命名的方式

create table jack (id int primary key not null,name varchar2(20));

另一种是有命名的方式

create table jack (id int ,name varchar2(20),constraint ixd_id primary key(id));

这两种方式如果分别查询会得到不同的索引结果感兴趣可以自行查询

select table_name,index_name from user_indexes where table_name='JACK';

其他操作

查询主键

select table_name,index_name from user_indexes where table_name='JACK';

select a.table_name,a.index_name,b.constraint_name,b.constraint_type,b.status from user_indexes a,user_constraints b where b.table_name='JACK'and a.table_name=b.table_name;

select owner,constraint_name,table_name,column_name from user_cons_columns where table_name = 'JACK';

查看constraint

SELECT constraint_name, table_name, r_owner, r_constraint_name
FROM all_constraints
WHERE table_name = '大写表名' and owner = '大写用户';

删除主键

alter table jack drop constraint SYS_C0011105;