Integrity, Views, Security, and Catalogs

Introduction

The DBA must begin by creating the tables and constraints and loading the data. Impose integrity constraints on the columns

Then provide views of the data, virtually restructuring the physical tables into variant table forms, to simplify access to data.

Provide security, so that only authorized users are able to read or update certain confidential data.

The structure of the various tables, views, and other objects of a database are made available to the DBA through a set of system-defined tables, called system catalogs.

数据完整性

保证数据完整性（主要指数据的正确性与一致性）是数据库管理员的最重要任务之一。

可以通过限制(Constraint)、规则(Rule)和默认(Default)保证数据的完整性，也可以在应用程序层保证数据完整性（这是应用程序设计的问题），或通过存储过程和触发器保证。

数据完整性类型包括：实体完整性、参照完整性和用户定义完整性

实体完整性(Entity Integrity) ：现实世界的实体是可区分的，即它们具有某种唯一性标识。相应地，关系模型中主键应作为唯一性标识。因此实体完整性规则规定基本关系的所有主键(Primary Key)都不能取空值(NULL) 。

参照完整性（Referential Integrity） ：参照完整性维护表与表之间的相关性，通常用“主键(Primary Key)/外键(Foreign Key)”保证，其中Foreign Key可以取NULL值，或取其参照表中Primary Key的取值。

用户定义的完整性(User_defined Integrity )：针对某一具体数据的约束条件，由应用环境决定。例如：某个属性必须取惟一值(UNIQUE)、某个非主属性不能取NULL值、某个属性的取值范围在0～100之间(CHECK)等等。

保证数据完整性

声明性数据完整性

声明性数据完整性用限制(Constraint)、规则(Rule)和默认(Default)在数据库中提供保证，这是保证完整性的较好方法。它驻留在数据库内，编程简单，开销小，能更集中管理和保证数据的一致性。

过程性数据完整性

过程性数据完整性用存储过程、触发器和应用程序代码保证，通常较为复杂、开销较大，但可以执行更多的业务规则。

通常，过程性数据完整性是声明性数据完整性的补充

Column Constraint

NOT NULL －If NOT NULL appears in a col_constr, then the DEFAULT clause cannot specify NULL;

CONSTRAINT－allows us to specify a name for each constraint other than NOT NULL, so that we can later drop the constraint with an ALTER Table statement.

UNIQUE－ It can be specified even if NOT NULL is not, and the column is then constrained so that all non-null values in the table are unique, but multiple nulls can exist for this column

PRIMARY KEY －A column with the PRIMARY KEY column constraint is implicitly defined to be NOT NULL and UNIQUE. The UNIQUE clause and PRIMARY KEY clause cannot both be used for a column, although the PRIMARY KEY clause and the NOT NULL clause can be used together.

CHECK

REFERENCES －The optional ON DELETE CASCADE clause that followed the REFERENCES clause requires that when a row in the referenced table is deleted that is referenced by rows in the referencing table, then those rows in the referencing table are deleted.

If not ON DELETE clause appears, then such a delete of a referenced row will simply fail to execute.

Example:

use cap;

create table sales(sid char(4) not null unique,

cid char(4) references customers on delete cascade,

dollars real constraint dellars_min check (dollars>0.0));

Table Constraint

UNIQUE －It is possible to specify a set of columns that must be unique in combination.

PRIMARY KEY －Specify a non-empty set of columns to be a primary key. Every column that participates in PRIMARY KEY clause is implicitly defined to be NOT NULL.

CHECK

FOREIGN KEY… REFERENCES…[ON DELETE [CASCADE]]

Create table customers (

cid char(4) not null,

cname varchar(13),

city varchar(20),

discnt real constraint discnt_max check(discnt <15.0),

primary key (cid)

)

Create table orders(

Ordno integer not null,

Monthe char (3),

cid char(4),

aid char(3),

pid char(3),

qty integer not null constraint qtyck check(qty >= 0),

dollars float default 0.0 constraint dollarsck check (dollars > 0.0,)

primary key (ordno).

Constraint cidref foreign key (cid) references customers,

Constraint aidref foreign key (aid) references agents,

Constraint pidref foreign key (pid) references customers,

)

Primary Keys, Foreign Keys, and Referential Integrity

如果表T1中任意行的F值的组合，至少包含一个空值或与被引用表T 2中为候选键或主键的列集合 P的组合值匹配，则列集合 F被定义为外键。

换句话说，如果表T 1的每一行中F的列都满足（1）至少有一列为空值（如果该列允许为空），（2）如果不包含空值，就必须与表 T 2中某行的相应P的组合值相等，则参照完整性约束是有效的。

The Alter Table Statement

Use cap;

Alter Table sale

Add Constraint PK_sid primary Key (sid),

Constraint Ck_dollars_max Check (dollars<=1000.00)

限制

限制是保证数据完整性的主要方法，限制类型有Primary Key、Foreign Key、Unique、Check和Default，其中Default可以作为限制，也可以作为数据库中的对象。

级联参照完整性：直到 SQL Server2000 中才加入进来的功能，在 Create Table 与 Alter Table 命令中增加了 ON DELETE 和 ON UPDATE 子句。对这些子句指定 CASCADE 时，父表的操作级联到子表。

Alter Table Employee

Add Constraint emp_dept_FK Foreign Key (dept)

References department(dept_no)

ON DELETE CASCADE

ON UPDATE CASCADE

对 department 表中 dept_no 的任何改变都会影响到 employee 表。

Check：限制列中能输入的值，Check限制用布尔表达式实现，当求出的值为false时，则不能进行插入或更新操作。

Check限制可以保证数据符合一定的格式，还可以引用同一表中的列，或引用函数。

—Create Table inventory (

item_code char(4) NOT NULL

constraint CK_inventory_item_code

check (item_code like ‘[0-9][0-9][0-9][0-9]’),

high_volume int NOT NULL

constraint CK_inventory_high_volume Check (high_volume>0),

low_volume int NOT NULL

constraint CK_inventory_low_volume Check (low_volume>0),

constraint CK_inventory_hi_lo_check

check (high_volume>=low_volume

and hign_volume-low_volume<1000)

)

管理限制

包括：收集限制信息、关闭与重新启用限制、删除限制。

收集限制信息：可以查询信息结构图，也可以在查询分析器中调用系统存储过程 Sp_help 和 Sp_helpconstraint 。

关闭与重新启用限制：可以关闭 Check与 Foreign Key 限制，使之不检查现有数据或装入的新数据。

在现有的表中添加限制时，如果只对新数据加限制，不检查已有数据，则采用 With NOCHECK

Alter Table Product

WITH NOCHECK

ADD Constraint CK_Product_UnitPrice Check (UnitPrice>=0)

如果要关闭已有限制，对加入新数据不用限制，则采用NOCHECK

Alter Table Product

NOCHECK

Constraint CK_Product_UnitPrice

删除限制：可以用Alter Table 命令删除限制，也可以在企业管理器的设计表选项中删除

Alter Table Product

Drop Constraint CK_product_UnitPrice

默认

默认可以指定列中不输入数值时的默认值，默认可以是求值为常量的任何项目，如常量、内置函数或数字表达式。

默认有两种：声明式默认和关联默认，其功能是相同的，只是实现方式不同。

声明式默认：声明式默认只是另外一种限制，因此用Create Table和Alter Table命令实现。

在Employee表中，不指定电话号码时，默认数值为UNLISTED

Alter Table Employee

Add Constraint DF_emp_ph Default ‘UNLISTED’ for Phone

删除默认

Alter Table Employee

Drop Constraint DF_emp_ph

关联默认：关联默认是独立于数据表创建的一个数据库对象，只有将其应用于一个数据表时，才对该表中对应的数据起作用。

创建：

Create Default default_name

AS constant_expression

只能在当前数据库中创建关联默认对象，其中constant_expression可以是常量、内置函数、算术表达式、全局变量等，但不能包含任何列名或其它数据库对象

在constant_expression中，字符和日期类型的数据应包含在单引号（‘ ’）中，二进制数据必须以0x开头，货币类型数据必须以（$）开头。其它数据则无限制。

例：

在任何表中，不指定电话号码时，默认数值为UNLISTED

Create Default phone_df

AS ‘UNLISTED’

应用：将一个默认对象应用于数据表需要使用系统存储过程Sp_bindefault实现

Sp_bindefault [@defname=] ’default’,// 需要应用的默认对象名称

[@objname=] ‘object_name’ //该默认对象应用到的列名“表名.列名”否则，表示应用到的数据类型名

[,[@futureonly=] ‘futureonly_flag’] //仅适用于用户定义数据类型

关联默认：

不能将默认对象应用于timestamp类型或identity属性列。

可以将新的默认对象应用于已有默认对象的属性列，此时，旧的默认对象被自动删除

将电话号码的默认对象应用于employee表中的phone列

sp_bindefault @defname=‘phone_df’,

@objname=‘employee.phone’

等价于：

sp_bindefault phone_df, ‘employee.phone’

默认的另一个用法是根据系统函数产生数值

Create Table Employee2

( Emp_no int identity(1,1) constraint Emp_PK_emp_no Primary Key Not Null,

Lname char(20) Not Null,

Fname char(20) Not Null,

Phone char(13),

Dept smallint constraint emp_dept_FK references department(dept_no) Not Null,

Updtime datetime Default Getdate() not null,

Updby varchar(30) Default Suser_Sname() not null

)

规则

规则用于限制列中可以存放的数值范围，对于一个具体的数据表而言，规则对象所执行的功能与CHECK约束类似，但规则是一种独立于数据表的数据库对象，只有将其应用于一个数据表时，才对相应的数据起作用

规则的使用与默认类似

例如：用规则限定数据格式

Create rule phone_rule

AS @phone Like ‘([0-9][0-9][0-9][0-9])-[0-9][0-9][0-9][0-9][0-9] [0-9][0-9]’

生成规则

Create RULE code_rule

AS @code in (‘r01’, ‘r02’ ‘r03’)

使用系统存储过程Sp_bindrule将规则关联到列或用户定义数据类型

Sp_bindrule code_rule, ‘product.code’

Sp_bindrule code_rule, color //用户定义数据类型

解除关联

Sp_unbindrule color

规则的局限性

一个列或用户定义数据类型只能有一个相关规则

规则不能验证行中的其它列

规则不符合ANSI-92标准