The seven data integrity of MSSQL

Whether it is a product, or information, quality is very important. The quality of information refers to the accuracy, completeness and consistency of information. In many database application systems, the quality of data is often the most important factor that results in the success of the system. Data integrity is an important method to guarantee the quality of data, and it is an important feature of modern database system. The Microsoft SQL Server 2008 System provides a series of data integrity methods and mechanisms, such as constraints, triggers, and so on. Among them, the constraint technology is the most widely used data integrity method. Focus ? Constraint Concepts and types ? Default constraint ? Check Constraint ? PRIMARY KEY constraint ? Unique constraint ? FOREIGN KEY constraints Preview lessons ? Concepts and types of constraints ? Administrative constraints

Introduction to Data integrity

The database system is actually a storehouse of data stored by the computer, and the system user can perform a series of operations on the stored data. Data integrity refers to the consistency and accuracy of the data stored in the database. In the design of the evaluation database, the design of data integrity is an important index of database design. In the Microsoft SQL Server 2008 system, there are 3 types of data integrity, namely: domain integrity, entity integrity, and referential integrity.

Domain integrity, which can also be called column integrity, specifies whether a dataset is valid for a column, and determines whether null values are allowed. Domain integrity is typically implemented by using validation checks, and can also be achieved by restricting the data type, format, or possible range of values. For example, setting an employee's date to enter the company is greater than the employee's birth date,

Entity integrity, also known as row integrity, requires that all rows in the table have a unique identifier, which is commonly referred to as a primary key value.

Referential integrity, referential integrity guarantees that the relationship between the primary key and the foreign key is always maintained. If a row in the referenced table is referenced by a foreign key, then this row of data cannot be deleted directly, and the user cannot directly modify the primary key value.

In a Microsoft SQL Server 2008 system, data integrity can be achieved in two ways, declaring data integrity and process data integrity.

Declaring data integrity is the implementation of data integrity by the data standards defined in the object definition, which is automatically enforced by the system itself. The way data integrity is declared includes the use of various constraints, defaults, and rules. For example, when a PRIMARY key constraint is defined in a table, the definition is automatically enforced by the system.

Process data integrity is achieved through data integrity standards defined in the scripting language. In the course of executing these scripts, the implementation of enforced integrity is defined by the script. Procedures for data integrity include the use of triggers and stored procedures.

? The concept and type of constraint

Constraints are a very effective way to ensure data integrity by restricting data in columns, data in rows, and tables. Constraints ensure that valid data is entered into a column and maintains a specific relationship between the table and the table. The Microsoft SQL Server 2008 system provides 5 types of constraints, primary key (primary key), FOREIGN key (foreign key), UNIQUE, CHECK, DEFAULT constraint.

Each type of data integrity, such as domain integrity, entity integrity, and referential integrity, is guaranteed by different constraint types.

You can use the CREATE TABLE statement or the ALTER TABLE statement to complete the constraint creation. Use the CREATE TABLE statement to indicate that a constraint is defined when the table is created, using the ALTER TABLE statement to add a constraint to an existing table. You can add constraints to a table even if you already have data in the table.

When defining constraints, you can either place constraints on a single column, or you can place constraints on multiple columns. If you place a constraint on a column, the constraint is called a column-level constraint because it can only be referenced by the column where the constraint resides. If you place a constraint on more than one column, which is called a table-level constraint, you can refer to the constraint by multiple columns.

? View constraints

When you create a constraint, you can specify the name of the constraint. Otherwise, the Microsoft SQL Server system will provide a complex, system-generated name. For a database, the constraint name must be unique. In general, the name of the constraint should follow this format: Constraint type abbreviation _ table name _ Column Name _ code. You can use catalog views to view information about constraints, including Sys.key_constraints, Sys.check_constraints, sys.default_constraints. The Sys.key_constraints catalog view is used to view information about primary keys and unique constraints, and the Sys.check_constraints catalog view is used to view information about check constraints in Sys.default_ You can view information about the default constraint in the Constraints catalog view.

? Default Constraints

When you insert data by using the INSERT statement, if you do not specify data for a column, the default constraint enters a value in that column.

For example, a default constraint of "male" is defined in the Gender column of the person table that records the personnel information. When data is entered into the table, if no data is provided for the Gender column, the default constraint automatically inserts the value "male" into the column. Therefore, the default constraint can achieve guaranteed domain integrity.

The basic syntax for defining a DEFAULT constraint is not exactly the same in the CREATE TABLE statement and in the ALTER TABLE statement.

? Check Constraint

A check constraint is used to restrict the data entered by a user into a column, that is, only the specified range of data can be entered in that column. A check constraint is very similar to a foreign key constraint, which restricts the range of values for a column, but the foreign key restricts the range of values for the column by other tables, and the check constraint restricts the column's range of values through the specified logical expression.

For example, you can create a check constraint in the gender column that describes the student's gender, specifying that the range is "male" or "female". This way, when entering data into the Gender column, either enter the data "male", or enter the data "female", and cannot enter other unrelated data .

The use of CHECK constraints requires consideration

L cannot define multiple check constraints on a column.

When an INSERT statement or an UPDATE statement is executed, the constraint verifies that the corresponding data satisfies the condition of the check constraint. However, the check constraint is not checked when the DELETE statement is executed.

L Check constraints can refer to other columns in this table. For example, in the employee table that contains the date of birth (birthdate) column and the hire date (hiredate) column, the Birthdate column can refer to the HireDate column so that the data for the Birthdate column is less than the data for the HireDate column.

The check constraint cannot be placed on a column with an identity attribute or on a column with a data type of timestamp. Because both of these columns automatically insert data.

The check constraint cannot contain a subquery statement.

? PRIMARY KEY constraint

A PRIMARY KEY constraint defines a primary key value in a table, which is the only identifier that determines the data for each row in the table. In all constraint types, a PRIMARY KEY constraint is the most important type of constraint and the most widely used constraint type. The constraint enforces entity integrity. There can be at most one primary key in a table, and the primary key column does not allow null values.

For example, in the students table, the StudentID column that describes the student number is generally used as the primary key value, because the value in the StudentID column is unique. The primary key is often defined on a column, but it can also be defined on more than one column. When a primary key is defined on more than one column, the combined values of those columns cannot be duplicated, although the data in one column may be duplicated.

Use primary KEY constraints to consider

Each table can have a maximum of one primary KEY constraint defined.

The value entered by the primary key column must be unique. If a PRIMARY key constraint consists of two or more than two columns, then the combination of the columns must be unique.

L The primary key column does not allow null values.

The primary KEY constraint creates a unique index on the specified column. The unique index can be either a clustered index or a non-clustered index. The clustered index is created by default. If a clustered index already exists in the table, either delete the existing clustered index before creating the primary key constraint, or specify that the index you are creating is a nonclustered index.

? Unique constraint

A unique constraint specifies that a column or columns in a table cannot have the same two rows or more than two rows of data exist. This constraint enforces entity integrity by implementing a unique index. When a PRIMARY key constraint is already in the table, if you need to implement entity integrity on other columns, and because the table cannot have two or more two primary key constraints, you can only do so by creating a unique constraint. In general, the unique constraint is called a candidate primary KEY constraint.

For example, in a students table, the PRIMARY KEY constraint is created on the StudentID column, and you can use a unique constraint if you also need to ensure that the data for the SSL column that stores the ID number in the table is unique .

The use of unique constraints requires consideration

The column where the unique constraint is located allows null values, but the column where the primary KEY constraint is located does not allow null values.

L can have multiple unique constraints in a table.

L can place a unique constraint on one or more columns, and the combination of these columns or columns must have a unique value. However, the column that contains the unique constraint is not the primary key column for the table.

The unique constraint forces a unique index to be created on the specified column. By default, a nonclustered index is created that is unique. However, when you define a unique constraint, you can also specify that the index you are creating is a clustered index.

? FOREIGN KEY constraints

FOREIGN KEY constraints enforce referential integrity. A FOREIGN KEY constraint defines one or more columns that can reference either a PRIMARY KEY constraint column or a unique constraint column in the same table or another table. In fact, you can implement a dependency between a table and a table by creating a foreign key constraint.

In general, in a Microsoft SQL Server relational database management system, there are often a number of relationships between tables and tables that are implemented by defining primary key constraints and foreign key constraints.

Summary

1. Data integrity is enhanced to maintain the accuracy, consistency and reliability of data in the database. He can be divided into the following categories:

Entity integrity: Ensure that each row can be called a primary key property uniquely determined

Domain integrity: Ensure that only valid range values are allowed to be stored in columns

Referential integrity: Ensure that the value of the foreign key matches the corresponding gradual value

User-defined integrity: A set of rules specified by the user that is not part of the entity, domain, and referential integrity types.

2. A primary KEY constraint is defined on a column or in a table the value of a series of columns that uniquely determines a row

3. Unique constraints are used to enhance uniqueness of non-primary key columns

4. Foreign KEY constraints associate one or more columns in a table with one of the same series of columns defined by the PRIMARY KEY constraint (the primary key column in the other table)

5. Check constraints enhance domain integrity by restricting the values inserted in the column. In, the like and between keywords are used to define check constraints.

6. The default constraint can be used to specify the constraint value for the column, and the value that the user does not need to insert for such a column.

The seven data integrity of MSSQL