数据库外文文献及翻译
DBMS-Enforced Data Integrity
A database is only as useful as the user's confidence in it. That's why the server must enforce data integrity rules and business policies. SQL Server enforces data integrity within the database itself, guaranteeing that complex business policies are followed and that mandatory relationships between data elements are complied with.
Because SQL Server's client/server architecture allows you to use a variety of front-end applications to manipulate and present the same data from the server, it would be cumbersome to encode all the necessary integrity constraints, security permissions, and business rules into each application. If business policies were all coded in the front-end applications, every application would need to be modified every time a business policy changed. Even if you attempted to encode business rules into every client application, the danger of an application misbehaving would still exist. Most applications can't be fully trusted. Only the server can act as the final arbiter, and the server must not provide a back door for a poorly written or malicious application to subvert its integrity.
SQL Server uses advanced data integrity features such as stored procedures, declarative referential integrity (DRI), datatypes, constraints, rules, defaults, and triggers to enforce data integrity. Each of these features has its own use within a database; by combining these integrity features, you can make your database flexible and easy to manage yet secure.
Declarative Data Integrity
The ability to enforce data integrity based on the constraints you define when you create the database tables is known as declarative data integrity. The different kinds of declarative data integrity constraints correspond to the three fundamental aspects of data integrity: entity integrity, referential integrity, and domain integrity.
A central tenet of relational database theory is that every tuple of every relation (more colloquially, every row of every table) can be uniquely identified—a condition known as entity integrity. The attribute or combination of attributes (the column or combination of 原文请找腾讯752018766辣-文^论^文.网http://www.751com.cn the columns that make up the primary key when you define a table. This is known as a PRIMARY KEY constraint. SQL Server uses this PRIMARY KEY constraint to guarantee that the uniqueness of the values in the designated columns is never violated. It enforces entity integrity for a table by making sure that it has a primary key.
Sometimes more than one column (or combination of columns) of a table can uniquely identify a row—for example, an employee table might have an employee ID (emp_id) column and a Social Security number (soc_sec_num) column, both of whose values are considered unique. Such columns are often referred to as alternate keys or candidate keys. These keys must also be unique. Although a table can have only one primary key, it can have multiple alternate keys. SQL Server supports the multiple alternate key concept via UNIQUE constraints. When a column or combination of columns is declared unique, SQL Server prevents any row from being added or updated that would violate this uniqueness.
Assigning an arbitrary unique number as the primary key when no natural or convenient key exists is often most efficient. For example, businesses commonly use customer numbers or account numbers as unique identifiers or primary keys. SQL Server makes it easy to efficiently generate unique numbers by allowing one column in a table to have the IDENTITY property. You use the IDENTITY property to make sure that each value in the column is unique and that the values will increment (or decrement) by the amount you specify from a starting point that you specify. (A column with the IDENTITY property typically also has a PRIMARY KEY or UNIQUE constraint, but this isn't required.)
The second type of data integrity is referential integrity. SQL Server enforces logical relationships between tables with FOREIGN KEY constraints. A foreign key in a table is a column or combination of columns that match the primary key (or possibly an alternate key) of another table. The logical relationship between those two tables is the basis of the relational model; referential integrity means that this relationship is never violated.
For instance, a simple SELECT example includes a titles table and a publishers table. In the titles table, the column title_id (title ID) is the primary key. In the publishers table, the column pub_id (publisher ID) is the primary key. The titles table also includes a pub_id column, which isn't the primary key because a publisher can publish multiple titles. Instead, pub_id is a foreign key, and it references the primary key of the publishers table. If you declare this relationship when you define the table, SQL Server enforces it from both sides. First, it ensures that a title can't be entered in the titles table, or an existing pub_id in the titles 1649