oracle约束

为什么要有约束?

约束:constraint 是一个关键字

约束就是为了防止那些无效或者有问题的数据流入表中,术语则为--"维护数据的一致性".约束是强加在表上的规则,条件.当对表进行DML或者DDL操作的时候,如果当前操作违反约束条件,或者规则,那么oracle系统就会拒绝执行这个操作.这样的好处是错误一开始几能被oracle发现,从而使数据库开发维护都更加容易.

oracle一共提供了5种约束

1. 非空约束(NOT NULL) 所定义的列不能为空,也是唯一一种只能在列一级定义的约束.
2. 唯一约束(UNIQUE) 在表中每一行中所定义的这列或这些列的值都不能相同(意思就是,该表中,该约束所定义的列,不能有重复值出现)
3. 主键约束(primary key) 唯一的标识表中的每一行
4. 外键约束(foreign key) 用来维护从表的主表之间的引用完整性
5. 条件约束(check) 表中每行都要满足该约束条件

约束是加在表上的,因为只有表中存有数据.可以在创建表时定义约束.也可以在已经存在的表上来定义约束.可以在列,也可以在表一级定义.约束存放在oracle数据字典中.可通过数据字典查询.定义事可给出约束名字,如果不给,那么oracle将会自动生成名字.格式为 SYS_Cn  其中 n 为大于0的自然数.

约束查询

约束可从oracle中的 user_constraints表中查询到.其中

1. C 代表 CHECK(条件约束) 以及 NOT NULL(非空约束)
2. P 代表 PRIMARY KEY(主键约束)
3. R 代表 REFERENTIAL INTEGRITY, 即 FOREIGN KEY(外键约束)
4. U 代表UNIQUE(唯一约束)

常用简写:

1. UK UNIQUE KEY唯一约束
2. PK PRIMARY KEY主键约束
3. FK FOREIGN KEY外键约束
4. CK CHECK 条件约束
5. NN NOT NULL 非空约束

非空约束(NOT NULL)

如果列设置为非空,无论 INSERT, UPDATE,都不能将该列的值置空,否则oracle将会报错违反非空约束.

个人理解,非空约束不能为空,那么假设我们有一个员工表,那么名字这一列,肯定不能为空的.

唯一约束(UNIQUE)

唯一约束约束得列,允许插入空值,并且允许插入多个空值,因为在oracle中,NULL值是唯一的,NULL值不等于任何NULL值,所以他们是不相等的,既然如此,那么就可以在使用唯一约束约束的列中插入多个NULL值.

创建表时可以指定唯一约束.

也可以在已有的表当中添加约束,语法为:

添加约束:ALTER TABLE 表名 ADD CONSTRAINT 约束名 UNIQUE (列名);

删除约束:ALTER TABLE 表名 DROP CONSTRAINT 约束名.

查看约束:SELECT 字段名 FROM USER_CONSTRAINTS WHERE TABLE_NAME = '表名';

个人理解为:加了唯一约束的列,可以插入空值,但是其他的值不能有重复的.应用场景为,假设我们有一个员工表,那么员工号可以使用这个约束.

条件约束

条件约束中的条件与查询语句中的条件相同,但是不能包括以下的内容:

1. CURRVAL, NEXTVAL, LEVEL, ROWNUM 这样的伪列.
2. 引用其他行中值的查询语句
3. SYSDATE, USER, USERENV, UID 函数的调用.

条件约束可在表一级,也可在列一级定义.

示例:

工厂招工:女性,18-35岁,大学或以上学历,未婚....等等.

那么我们再建表的时候可以利用如下SQL

接下来可以查询是否成创建了检查约束

执行 select owner, constraint_name, constraint_type, table_name,search_condition from user_constraints where table_name='PERSON';

尝试插入2条记录,测试约束

其中第一条显示插入成功,而第二条则显示违反检查约束,这是由于我们在表中创建了约束条件 gender = 'F' 这个约束,当检查到插入的数据 不满足检查条件的时候,oracle会拒绝执行该操作语句.修改也是一样的,修改后的值要满足检查条件才会修改成功,否则失败.

主键约束

标识表中唯一一行记录的约束,非空且不能重复.

例如上面示例当中的ID字段 我们可以设为主键.

外键约束

外键约束是用来维护从表以及主表之间的引用完整性的.

语法:

建表时创建约束: CONSTRAINT 约束名 FOREIGN KEY (列名) REFENECES 主表名(主表列名);

在已有的表上添加约束: ALTER TABLE 子表名 ADD CONSTRAINT 约束名 FOREIGN KEY (子表列名) REFERENCES 主表名 (主表列名)

删除约束: ALTER TABLE 表名 DROP CONSTRAINT 约束名;

外键对INSERT 的影响

个人理解为:  对子表或从表进行插入数据时,所插入的数据项中,在从表中被定义为外键的数据列的值,必须在主表中存在,否则会违反完整约束条件,插入失败.而且只要在操作子表或从表时才会触发产生这个类型的错误,如果多主表进行操作的话,则不会产生.

假设A表中有一个字段ID为外键,该字段与B表中的NO相关联,那么在王A表插入数据的时候,如果该数据中ID的值,在B表中的NO里不存在,那么插入就会失败.

外键对DELETE的影响

当子表(从表)与主表有外键约束的时候,只有操作是在主表这一端时才会产生违反引用完整性的问题.而操作子表或者从表时不会产生该问题的.

假设子表A中的 ID 字段与主表B表中的 NO 字段相对应,并且值都是'F'',如果A表中有ID为'F'的记录,这时候如果删除B表中NO为'F'的记录时,会产生违反引用完整性问题.

相反的,如果删除A表中ID为'F'的数据行时,则不会出现类似的问题.

外键对UPDATE的影响

在进行修改操作时,无论是在主表,还是在子表(从表),都有可能产生违反引用完整性的问题.

假设子表A中的ID字段 与主表B表中的NO字段相对应,并且值都是'F'',如果A表中有ID为'F'的记录,这时候如果修改主表B中的NO字段,由'F'修改为其他的值,那么会产生错误,因为在子表A中有ID为'F'的数据记录.

如果修改子表A中的ID为'F'的数据,将'F'修改为其他的值(该值在主表B中NO列不存在时),也会出现违反引用完整性问题,因为这样会找不到父项关键字.

如果此时要修改数据的话,可以先将子表A中的 ID 列字段设为 NULL, 然后将主表B中的 NO 字段修改成想要的值, 然后在将子表A中的 ID 字段修改成主表 B中 NO 的值.

外键对DDL语句的影响

只有在删除主表的时候,会产生违反引用完整性问题.删除从表则不会出现该错误.

假设子表A中的ID字段 与主表B表中的NO字段相对应.

如果这个时候想要删除主表B 无论使用(DROP 还是 TRUNCATE),那么都将会产生错误.

外键的 ON DELETE SET NULL 和 ON DELETE CASCADE 字句

ON DELETE SET NULL字句的作用是:当主表中的一行数据被删除时,oracle系统会自动的将所有从表中依赖于它的数据记录的外键改为空(NULL).

ON DELETE CASCADE字句的作用是:当主表中的一行数据被删除时,oracle系统会自动的将所有从表中依赖于它的数据记录彻底删除.

使用上述2个字句定义的外键,无论是操作主表还是操作子表,都不会违反引用完整性,但是会留下隐患.使用的时候最好谨慎.比如在定义外键的时候使用了 ON DELETE CASCADE ,那么任何在主表上的勿删,都可能造成从表上成千上万甚至更多的数据的丢失.