oracle——05数据完整性

 

数据的完整性就是指数据库中的数据在逻辑上的一致性和准确性。数据完整性一般分为3种：域完整性、实体完整性和参照完整性。完整性约束是通过限制列数据、行数据和表之间的数据来保证数据的完整性。完整性约束定义在表上，存储在数据字典中。

 

1、域完整性

域完整性又称列完整性，是指确定一个数据集对某一列是否有效和确定是否允许空值。域完整性通常是通过使用有效性检查来实现的，还可以通过限制数据类型、格式或者可能的取值范围来实现。

Oracle可以通过check约束实现域完整性。check约束表示一个字段的输入必须满足check约束的条件，若不满足，则数据无法正常输入。

利用SQL语句操作check约束主要有如下几种方式：

（1）利用SQL语句在创建表时创建check约束

 

create table tableName(
    columnName dataType [not null | null] [default expression] check(checkExpression),
    columnName dataType [not null | null] [default expression] constraint checkName check(checkExpression),
    ...n
)

 

 

示例代码：

 

create table person(
    id int primary key,
    sex varchar(4) check(sex='男' or sex='女'),
    age int default 0 constraint ageCheck check(age>=0 and age<=125)
)

 

 

（2）利用SQL语句在修改时创建check约束

 

alter table tableName add(constraint checkName check(checkExpression), columnName dataType check(checkExpression))

 

 

示例代码：

 

alter table person add(constraint sexCheck check(sex='男' or sex='女'), age int check(age>=0 and age<=125))

 

 

（3）利用SQL语句删除check约束

 

alter table tableName drop constraint  checkName;

 

 

示例代码：

 

alter table person drop constraint sexCheck;

 

 

 

2、实体完整性

实体完整性又称行完整性，它有求表中的每一行都有一个唯一的标识符，这个标识符就是主关键字。通过索引、unique约束和primary key约束可以实现实体完整性。

为表创建主键可实现表的实体完整性，主键可以是一列或几列的组合，一个表只能有一个primary key约束，且primary key约束中的列不能为空值，当为表创建primary key时，Oracle会自动为primary key对应的列创建唯一索引，实现数据的唯一性。有了索引后，就可以实现对数据的快速访问。

如果要确保一个表中的非主键列的值不可重复，可以在该列加上unique约束，unique约束也会自动产生索引。

primary key约束和unique约束的区别如下：

（1）一个数据表只能有一个primary key约束，但可以有多个unique约束。

（2）primary key对应字段的值不能为null，而unique约束对应列的值可以为null。

（3）primary key对应字段或字段的组合的值是不能重复的，而unique约束列的值为null的时候是可以重复的，也就是说unique约束列可以有多个null值。

利用SQL创建和删除primary key约束和unique约束

（1）在创建表的时候创建对应的约束

 

create table tableName(columnName dataType primary key, columnName dataType unique,...n);
create table tableName(columnName1 dataType, columnName2 dataType, ...n, primary key(columnName1, columnName2), unique(columnName3, columnName4));

 

 

示例代码：

 

create table person(id int primary key, name varchar(20) unique, age int);
create table person(id int, name varchar(20), age int, primary key(id), unique(name));

 

 

（2）在修改表的时候创建对应的约束

 

alter table tableName add(constraint pkName primary key(column1, column2), constraint uniqueName unique(column3, column4));

 

示例代码：

 

alter table person add(constraint personPk primary key(id), constraint nameUnique unique(name));

 

（3）删除primary key和unique约束

 

alter table tableName drop constraint constraintName;

 

示例代码：

 

alter table person drop constraint personPk;

       

 

3、参照完整性

参照完整性又称为引用完整性，它可以保证从表（参照表）中的数据与主表（被参照表）中的数据的一致性。参照完整性通过定义从表的外键与主表的主键相对应来实现。可以利用foreign key定义从表中的外键，利用primary key定义主表中的主键。

--如果主表中的一条记录被从表中的外键所引用，那么主表的这一条记录既不能被删除也不能修改主关键字。

--如果从表的外键没有进行not null约束，那么从表的外键也是可以为null的。

利用SQL语句定义和删除表间的参照关系

（1）创建表时同时定义表间的参照关系

 

create table tableName(columnName dataType [foreign key] references referencesTableName(referencesColumn),...n);

 

--referencesTableName表示参照表的表名，referencesColumn表示参照的参照表中的字段名，即参照的是参照表中的哪个字段。

示例代码：

 

create table saleBill(id int primary key, saleBookBillId int references saleBookBill(id));

 

--该代码表示saleBill表中的saleBookBillId字段是参照saleBookBill表的id字段。

（2）通过修改表定义外键约束

 

alter table tableName add(constraint constraintName foreign key(column1[, column2, ...n]) references refTableName(refColumn1[, refColumn2, ...n]));

 

示例代码：

 

alter table saleBill add(constraint saleBillFk foreign key(saleBookBillId) references saleBookBIll(id));

 

（3）利用SQL语句删除表间的参照关系

 

alter table tableName drop constraint constraintName;

 

示例代码：

 

alter table saleBill drop constraint saleBillFk;