利用Oracle数据库表完整性功能提高数据输入准确率

Oracle数据库表的完整性功能是一个非常实用的功能，利用这个功能可以提高表中数据的准确率。根据其具体实现方式的不同，表的完整性包括三部分内容，分别是实体完整性、域完整性与参照完整性。这三部分功能平时各负其责，共同实现了表的完整性功能。

　　如现在有如下一张员工表格。若我们现在采用Oralce数据库管理这张数据表，那我们该如何结合表的完整性功能，提高这张员工数据表的数据准确率呢?

　　身份证号码　　　　　　　　姓名　　　入职日期

　　339005198103031716　　甲　　　2008-2-1

　　339005198303031718　　乙　　　2008-2-2

　　339005198003031719　　丙　　　2008-2-3

　　339005198203031720　　丁　　　2008-2-4

　　一、实体完整性。

　　实体完整性，是指在表的级别上，防止重复记录的产生。如作为上面这一张员工信息表，其身份证号码当然不能重复。若输入重复的话，则系统就应该提示错误信息。这就是通过实体完整性的功能来实现的。

　　具体的来说，表的实体完整性功能是通过主键来实现的。我们可以给表的某些字段设置主键，从而达到记录不重复的目的。主键其有两个特点，一是不能为空，而是设置的主键的字段内容不能重复。

　　从以上这张表中，我们可以把员工的身份证号码设置为主键。如此，当用户数据身份证号码时，若输入的身份证号码重复的话，则数据库系统会提醒用户，数据的身份证号码已经存在，并且，这条记录将不会被保存。

　　1、根据上面的要求在数据库中创建表格。

　　2、在没有创建主键，利用实体完整性功能之前，若数据的员工记录具有相同的身份证号码，系统也会接受，不会报错。如下图所示。但这明显不符合我们的要求。我们希望当我们不小心输入重复性的数据的时候，如身份证重复的话，则系统将提示错误，并且，不保存该条记录。

　　3、我们在刚才新建的职工表上，加上实体完整性约束。给身份证号码字段加上主键。然后，来看看具体的效果。

　　4、在身份证号码上，我们实现完整性约束，建立主键后，我们发现，此时，在同时建立两条相同的身份证号码记录的话，系统就提示错误信息，所违反了唯一约束条件。从这里就可以看出，实体完整性约束在起作用了。

　　以上笔者描述的了就是Oracle服务器表完整性中的实体完整性的功能。通过这个过程的描述，我们可以看到，通过给表的某些字段设置主键的方法，我们可以实现对于表记录的唯一性控制，从而防止用户不小心输入重复的数据，造成数据库中数据的混乱。

　　在给数据库表实现实体完整性，即给表设置主键的时候，要注意几个要点。

　　一是设置主键的字段是不允许为空的。所以，我们在设计表结构的时候，如果需要给表的某个字段设置成为主键的时候，则最好把这个字段设置为不为空。如此的话，用户在操作数据表的时候，就一定要在该字段输入值，否则的话，数据库就会提示“该字段不能为空”的错误。

　　二是设置主键后，该字段的内容不能为重复。有时候，我们可能因为设计表格的时候，考虑的不够周到，没有把用户表的身份证号码字段设置为主键。若在表中已有数据的情况下，给表设置主键，就可能会发生错误，因为表中的数据可能已经重复了。为此，我们最好在表设计的时候，就要考虑好，到底该字段是否要设置成为主键。

　　三是主键的设置根据不同的业务需求是不一样的，数据库管理员要根据实际的情况选择恰当的字段作为关键字。如在职工基本信息表中，我们可能要求身份证号码唯一。但是，若在其他应用中，若把身份证号码设置为主键的话，就可能发生错误。如在学校的学位管理中，可能一个人会同时取得两个或者两个以上的学位，此时，若把身份证号码设置为主键可能不是很合适。可能需要把身份证号码与学会编号一起设置为主键。若同一张表中，有两个以上的字段设置为主键时，系统的要求是两个字段都重复才是违反这个约束性条件。也就是说，学位表中有两条记录，身份证号码相同学位编号不同(一个人同时有多个学位)，这种情况是不违反实体完整性功能的。所以这要注意，跟设置单个主键有比较大的区别。单个主键是这个字段不能重复，而多个主键的话，是设置主键的字段都不重复就行了。

　　二、参照完整性。

　　在一般的管理系统中，无论是小小的考勤系统，还是大的ERP系统，一般都会包含两张表。一张是员工信息表，一张是部门信息表。如下表所示：

员工信息表

员工编号	性别	部门
SA001	男	销售
SA002	男	销售
PR001	男	采购

 部门信息表

部门编号	部门名称
SA	销售
SA	销售
PR	采购

　　现在我们希望，在员工信息表处定义的部门信息必须存在与部门信息表中。也就是说，当部门信息表中没有定义这个部门的话，在员工信息表中将部门输入这个部门。如在部门信息表里，我们没有输入研发部门，此时，在员工信息表中，我们若把一个员工归属于研发部门的话，则系统要能够提示错误信息，告诉用户这个部门还没有定义，需要先定义该部门信息。

　　这个需求就是要通过参照完整性来实现。参照完整性是指互相关联的两个表之间的约束，具体的说，就是从表中每条记录外键的值必须是主表中所存在的。因此，如果在两个表之间建立了关联关系，则对一个表进行的操作要影响到另一个表中的记录。下面，我结合具体的实例，讲述一下参照完整性的具体实现方式及需要注意的地方。

　　1、 建立部门信息表。

SQL> create table DEPT(DETPNUM CHAR(10) not null,NAME varchar2(10) not null);

　　表建立完成以后，给部门信息表中插入销售与采购两条记录。

SQL> insert into DEPT values('SA','销售');

SQL> insert into DEPT values('PR','采购');

　　技巧说明：当我们利用语句给一个表中插入记录的时候，如果表中有两个字段，我们给这两个字段都赋值的话，则在插入语句中，可以不写字段的名称。但是，若插入一条记录的时候，一条记录的某个字段没有赋值的话，则要注意一定要按赋值的顺序把字段名一一写清楚。

2、 建立员工信息表，先不设置外键，看效果如何。

SQL create table USERINFO(USERNUM CHAR(10) not null,SEX char(2),DEPTNUM CHAR(10));

　　下面，我们给员工信息表插入一条记录，部门为研发部门(AD)。该部门信息在部门信息表中不存在。在没有建立参照完整性的条件下，数据库将不会提示任何的错误。

SQL> insert into USERINFO values('SA001','Y','AD');

　3、 给员工信息表实现参照完整性功能，给其部门字段设置外键。

　　从上面的记录中可以看到，不设置外键的情况下，即使部门信息表中没有这个部门信息，但是，在员工信息表中仍然可以把员工归属于“研发”部门。这在实际管理中，肯定会出乱子。所以，我们希望当部门信息表中没有研发部门的部门信息时，员工信息表中就也不能出现这个部门。

　　我们先个员工信息表的部门字段定义一个外键。

SQL> ALTER TABLE USERINFO ADD CONSTRAINT FK_DEPTNUM FOREIGN KEY(DEPTNUM) REFERENCES DEPT(DETPNUM);

ALTER TABLE USERINFO ADD CONSTRAINT FK_DEPTNUM FOREIGH KEY(DEPTNUM) REFERENCES DEPT(DETPNUM)

　　如图所示，当员工信息表中已经有记录，并且，该记录的部门编号不存在与部门信息表中，在建立主键的时候，就会产生错误信息，提示此列列表的唯一或主键不匹配。我先把该条记录的部门信息更新一下，然后再建立主键。

SQL> ALTER TABLE USERINFO1 ADD CONSTRAINT FK_DEPTNUM FOREIGN KEY(DETPNUM)REFERENCES DEPT(DETPNUM);

　　这里要注意一个问题，就是主表中的某个字段如果要被其他表设置为外键的话，则这个字段要先设置为主键，否则在建立外键的时候，会报错。

　　4、 给员工信息表插入记录，部门字段不在部门信息表中，系统提示错误信息。

SQL> insert into USERINFO values('SA001','Y','AD');

SQL> insert into USERINFO1 values('SA001','Y','AD');

　　如上图所示，没有建立主键的员工信息表可以顺利插入数据，但是，在下面已经建立了外键的表中的话，插入记录的话，会提示错误信息“违反完整约束条件，未找到父项关键字”。

　　5、 在参完整性的设计中，需要注意的几个方面。

　　一是外键的取名规则。虽然在Oracle数据库中，没有对外键的取名有特殊的强制性要求，只要满足普通的命名规则即可，如不能使用Oracle数据库本身的关键字进行命名或者名字不能以数字开头，除此之外，没有强制性的要求。系统虽然没有强制性要求，但是，作为一个好的数据库开发人员，要养成一个好的命名习惯。如按照我们一般的命名习惯，外键约束取名以FK开头，并用下划线与名字进行分开;名字一般是以字段名进行定义。如上面给员工信息表的部门字段设置外键的话，去外键就取名为FK_DEP。如此的话，以后我们维护这些完整性功能的话，一看约束名字，就知道这个约束是参照完整性约束，其是作用在部门这个字段上。这个命名的另外一个好处，就是方便其他开发人查询。现在的数据库管理系统一般一个人很难完成，都是需要通过一个团队才可以实现。所以，我们在设计数据库的时候，不仅我们自己要能够看懂我们去的名字，而且，其他开发人员也需要能够一目了然的知道这个名字的意思。不仅外键定义是如此，我们上面所说的主键的定义，及后面将要讲到的CHECK约束，都是类似的道理。

　　二是参照完整性约束中，一般把两张表叫做主表与从表。主表就是被参考的表，就是上面例子中所讲的“部门信息表”，而从表就是“员工信息表”。在设计参照完整性功能的时候，我们要注意的的是，主表中被参考的字段与从表中被定义为外键的字段，无论是数据类型还是数据的长度都必须一致。否则的话，容易产生错误。这也是一种数据库开发的良好习惯。

　　三是一单主表中的某个字段被其他表作为外键的话，则这个表的修改与删除就会收到限制。一般情况下，这个被其他表引用了的字段将不能被删除或者修改。用户强行删除的话，系统会提示该表已经有子记录。当然，也可以通过“级联修改”或者“级联删除”实现这个功能。级联修改是指主表中字段内容修改了，则从表中对应的内容也发生改变，如当采购部门名称改为物流部门的话，则在员工信息表中的采购部门也会自动改为物流部门;级联删除也是类似的道理，到主表中的记录被删除的话，从表中的记录也将被删除，当我把部门信息表中的采购部门这条记录删除之后，员工信息表中的所有采购部门的员工记录也将被删除。但是，很明显，这么操作的话，比较危险。所以，笔者一般都不建议如此操作。关于这个级联修改及级联删除功能或后续还会详细介绍。