0%

oracle 11g 的完整性约束

发表于 分类于 阅读次数： Valine：
本文字数： 2.7k 阅读时长 ≈ 2 分钟

1. 完整性简介

定义：就是正确性、准确性，包含三种：实体完整性、参照完整性、用户自定义完整性。Oracle中主要通过约束、触发器、过程函数实现的。维护数据的完整性，有not null、unique、check、primary key、foreign key五种。not null 只能在列内定义，其他4种约束可以在列定义结束后，在表内定义。

需要说明的是：当使用 unique 唯一约束时，根据NULL的定义，NULL表示的是未知，因此两个NULL比较的结果既不相等，也不不等，结果仍然是未知。根据这个定义，多个NULL值的存在应该不违反唯一约束。

2. 约束示例

  • not null 非空
1
create table yg(bh number(8) not null, xm varchar2(20));
  • unique 唯一（可以为空）
1
create table yg1(bh number(8) unique, xm varchar2(20));
  • check 自定义约束
1
create table yg2(bh number(8) not null check(bh > 0 and bh < 1000), xm varchar2(20));
  • primary key 主键（非空且唯一，自动创建索引）
1
create table yg3(bh number(8) primary key, xm varchar2(20));
  • default 外键（不插入时默认为指定值，插入 null 时不会显示默认值）
1
2
3
4
5
6
7
8
9
create table yg4(bh number(8) primary key, xm varchar2(20), age number(3) default(1));

-- 插入示例如下：
SQL> insert into yg4(bh, xm) values(2, '李四');
SQL> select * from yg4;

        BH XM                                              AGE
---------- ---------------------------------------- ----------
         2 李四                                              1
  • foreign key 外键（外键只能取主键已经有的值）
1
2
-- 如下创建表时，bh 字段引用自 yg3 表的 bh 字段
create table addr(bh number(8), zz varchar2(50), foreign key(bh) references yg3(bh));

3. 查看约束

用户的约束在user_constraints表、和user_cons_columns表中查看，如下所示：

其中 constraint_name 表示约束名，由于我们创建约束的时候没有自定义名称，所以 oracle 自动进行命名。constraint_type 表示约束类型，其中 not null 和 check 属于 C 类型，foreign key 属于 R 类型，primary key 属于 P 类型，unique 属于 U 类型。

在 user_cons_columns 表中我们可以查看具体约束作用的字段，如下所示：

4. 建立主键方式

1
2
3
4
5
6
7
8
9
10
11
-- 1. 默认系统名称
create table t1(id number(10) primary key);
-- 2. 自定义名称
create table t2(id number(10) constraint pk_id primary key;
-- 3. 列外定义主键
create table t3(id number(10), primary key(id));
-- 4. 列外自定义名称定义主键
create table t4(id number(10), constraint pk_id_ot primary key(id));
-- 5. 建表后定义主键
create table t5(id number(10));
alter table t5 add constraint pk_id_ot2 primary key(id);
1
2
3
4
5
6
7
8
9
create table department(deptNo number(20) primary key, deptName varchar2(50));
create table stuInfo(
    sno number(11) primary key, 
    sname varchar2(20) not null, 
    sage number(3) check(sage >= 18 and sage <= 50), 
    smale varchar2(4)  default '男' check(smale = '男' or smale = '女'), 
    deptNo number(20),
    foreign key(deptNo) references department(deptNo)
);

5. 给已有表添加约束

除了添加 not null 需要使用 modify 命令，其他都是类似 alter table tablename add CONSTRAINTS 的方式。

1
2
3
4
5
6
-- 修改编号列不可为空
alter table yg1 modify bh not null;
-- 在姓名列添加唯一约束
alter table yg1 add constraints xm_unique unique(xm);
-- 在姓名列添加自定义约束
alter table yg1 add contraints xm_check check(length(xm) > 4);

6. 删除约束

语法如下所示：

1
2
3
4
-- 删除约束
alter table 表名 drop constraint 约束名;
-- 删除主键
alter table 表名 drop primary key;

如果删除主键约束时，发生了主键被引用的情况，可以通过如下语句进行删除：

1
alter table 表名 drop primary key cascade;

如果删除一个主键被引用的表，可以通过如下语句进行删除：

1
drop table 表名 cascade constraints;
------ 本文结束------