MSSQL 7 Data integrity

Both products and information are of great importance. Information quality refers to information accuracy, integrity, consistency, and so on. In many database application systems, data quality is often an important factor that leads to system success. Data integrity is an important method to ensure data quality and an important feature of modern database systems. Micros

Both products and information are of great importance. Information quality refers to information accuracy, integrity, consistency, and so on. In many database application systems, data quality is often an important factor that leads to system success. Data integrity is an important method to ensure data quality and an important feature of modern database systems. Micros

Both products and information are of great importance. Information quality refers to information accuracy, integrity, consistency, and so on. In many database application systems, data quality is often an important factor that leads to system success. Data integrity is an important method to ensure data quality and an important feature of modern database systems. Microsoft SQL Server 2008 provides a series of data integrity methods and mechanisms, such as constraints and triggers. Constraints are the most widely used data integrity methods. Key points ? Constraint concepts and types ? DEFAULT constraint ? CHECK constraints ? Primary key constraint ? UNIQUE constraints ? Foreign key constraint Preview lessons ? Concept and type of constraints ? Management constraints

Data integrity overview

A database system is actually a warehouse where computers store data. you 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. When evaluating database design, data integrity design is an important indicator of database design. In Microsoft SQLServer 2008, three data integrity types are available: Domain integrity, entity integrity, and reference integrity.

Domain integrity, also known as column integrity, specifies whether a dataset is valid for a column and determines whether null values are allowed. Domain integrity is usually implemented through validity check. It can also be achieved by limiting the data type, format, or possible value range. For example, set an employee to enter the company on a date greater than the employee's date of birth,

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

Integrity of reference. integrity of reference ensures that the relationship between the primary key and the foreign key is always maintained. If a row in the reference table is referenced by a foreign key, this row of data cannot be deleted directly, and you cannot directly modify the primary key value.

In Microsoft SQL Server 2008, data integrity can be declared in two ways: data integrity and process data integrity.

Declared data integrity is achieved through the data standards defined in the object definition, which is automatically enforced by the system itself. You can declare data integrity by using various constraints, defaults, and rules. For example, if a primary key constraint is defined in a table, this definition is automatically enforced by the system.

Process Data integrity is achieved through the data integrity standards defined in the scripting language. The execution of these scripts is implemented by the force integrity defined in the script. Process data integrity includes the use of triggers and stored procedures.

? Concept and type of constraints

Constraints are a very effective way to ensure data integrity by limiting data in columns, data in rows, and data between tables. Constraints ensure that valid data is input into the column and the specific relationship between the table and the table is maintained. Microsoft SQLServer 2008 provides five types of constraints: primary key (primary key), foreign key (foreign key), UNIQUE, CHECK, and DEFAULT.

Each data integrity type, such as domain integrity, entity integrity, and reference integrity, is guaranteed by different constraints.

When creating constraints, you can use the create table statement or alter table statement. Use the create table statement to define constraints when creating a TABLE. use the alter table statement to add constraints to an existing TABLE. Even if the table already has data, you can add constraints to the table.

When defining a constraint, you can put the constraint on one or multiple columns. If you place a constraint on a column, this constraint is called a column-level constraint because it can only be referenced by the column where the constraint is located. If you place a constraint on multiple columns, this constraint is called a table-level constraint. in this case, multiple columns can reference this constraint.

? View constraints

When creating a constraint, you can specify the constraint name. Otherwise, the Microsoft SQL Server System provides a complex name automatically generated by the system. 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 the directory view to view constraints. These directory views include sys. key_constraints, sys. check_constraints, and sys. default_constraints. Sys. the key_constraints Directory view is used to view information about primary keys and UNIQUE constraints. sys. the check_constraints Directory view is used to view information about CHECK constraints. you can view information about the DEFAULT constraint in the default_constraints Directory view.

? Default constraint

When data is inserted using the INSERT statement, if no data is specified for a column, the DEFAULT constraint will enter a value in the column.

For example, in the gender column of the person table that records the personnel information, a DEFAULT constraint is defined as "male ". If no data is provided for the gender column when you enter data in the table, the DEFAULT constraint automatically inserts the DEFAULT value "male" into the column. Therefore, the DEFAULT constraint can ensure domain integrity.

The basic syntax for defining the DEFAULT constraint is not identical in the create table statement and in the alter table statement.

? CHECK constraints

The CHECK constraint is used to restrict users from entering data in a certain column. that is, only data in a specified range can be entered in this column. CHECK constraints are similar to foreign key constraints. both of them limit the value range of a column, but foreign keys use other tables to limit the value range of a column, the CHECK constraint restricts the value range of a column through a specified logical expression.

For example, you can create a CHECK constraint in the gender column that describes the gender of a student's gender and specify the value range as "male" or "female ". In this way, when you input data to the gender column, either the data is "male" or the data is "female", rather than other irrelevant data..

CHECK constraints must be considered

L multiple CHECK constraints cannot be defined for one column.

L when an INSERT or UPDATE statement is executed, this constraint verifies that the data meets the CHECK constraints. However, CHECK constraints are not checked when the DELETE statement is executed.

L CHECK constraints can be found in other columns in this table. For example, the employee table contains the birth date column and the employment date column. the birthdate column can reference the hiredate column so that the data in the birthdate column is smaller than that in the hiredate column.

L The CHECK constraint cannot be placed on a column with the IDENTITY attribute or a column with the timestamp data type. Because both columns automatically insert data.

L The CHECK constraint cannot contain subquery statements.

? Primary key constraint

The primary key constraint defines a primary key value in the table, which is the unique identifier used to determine each row of data in the table. Among all the constraint types, the primary key constraint is the most important constraint type and the most widely used constraint type. This constraint forces entity integrity. A table can have at most one primary key, and the primary key column cannot have null values.

For example, in the students table, the studentID column that describes the student ID is generally used as the primary key value, because the value in the studentID column is unique. A primary key is often defined in one column, but can also be defined in multiple columns. When a primary key is defined in multiple columns, although data in a column may be duplicated, the combined values of these columns cannot be repeated.

Use primary key constraints

L each table can only define one primary key constraint.

L The value entered by the primary key column must be unique. If the primary key constraint consists of two or more columns, the combination of these columns must be unique.

L null values are not allowed in primary key columns.

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

? Unique Constraint

The UNIQUE constraint specifies that one or more columns in a table cannot have the same data of two or more rows. This constraint forces entity integrity by implementing a unique index. When a table already has a primary key constraint, if you need to implement entity integrity in other columns, and because the table cannot have two or more primary key constraints, therefore, you can only create a UNIQUE constraint. Generally, the UNIQUE constraint is called a candidate primary key constraint.

For example, in the students table, the primary key constraint is created on the studentID column. if you need to ensure that the data in the SSL column storing the ID card number in this table is UNIQUE, you can use the UNIQUE constraint..

Use of unique constraints must be considered

L 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 a table can have multiple UNIQUE constraints.

L You can place the UNIQUE constraint on one or more columns. The combination of these columns or columns must have UNIQUE values. However, the column where the UNIQUE constraint is located is not the primary key column of the table.

L UNIQUE constraints force a UNIQUE index to be created on the specified column. By default, a unique non-clustered index is created. However, when defining the UNIQUE constraint, you can also specify that the created index is a clustered index.

? Foreign key constraint

Foreign key constraints force reference integrity. Foreign key constraints define one or more columns that can reference the primary key constraint column or UNIQUE constraint column in the same table or another table. In fact, the dependency between tables can be achieved by creating a foreign key constraint.

In general, there are a lot of relationships between tables in Microsoft SQL Server relational database management systems. these relationships are achieved by defining primary key constraints and foreign key constraints.

Summary

1. Data integrity is enhanced to ensure data accuracy, consistency, and reliability in the database. It can be divided into the following types:

Entity integrity: ensure that each row can be uniquely identified as a primary key.

Domain integrity: ensure that only valid range values are allowed to be stored in the column

Integrity of reference: make sure that the foreign key value matches the corresponding gradual value

User-defined integrity: a series of rules specified by the user, which do not belong to entity, domain, or referential integrity type.

2. primary key constraints are defined in a column or the values of a series of columns that uniquely determine a row in the table

3. the unique constraint is used to enhance the uniqueness of non-primary key columns.

4. Foreign key constraints associate one or more columns in the table with one series of columns defined by the primary key constraint (primary key columns in another table)

5. check the constraints to enhance the domain integrity by limiting the values inserted in the column. The IN, LIKE, and BETWEEN keywords are used to define check constraints.

6. the default constraint can be used to specify the constraint value of a column, and you do not need to insert a value for this column.