SQL Application and Development: (eight) data integrity

I. Definition

Data integrity means that all data values stored in the database are in the correct state. If an incorrect data value is stored in the database, the database is said to have lost data integrity.

SQL database does not just store data, it must ensure that the stored data is correct. If the data is inaccurate or inconsistent, the integrity of the data is compromised, causing problems for the reliability of the database itself. To ensure the integrity of the database, SQL provides a number of integrity constraints. An integrity constraint is a rule base table that is applied to a base table to constrain the values that can be placed in these tables. Constraints can also be applied to a single column, to a single table, or to multiple tables.

Two. Learning content

1. Understanding Constraints

A constraint is an object in a relational database that holds rules about the data inserted into a column of a table. Constraints are used to ensure the accuracy and consistency of data in a relational database. In a relational database, you handle database integrity by applying constraints on the table.

The constraints in SQL can be divided into 3 types:

table-related constraints It is a constraint in the table definition. The constraint can be defined as a part of a column definition, or as an element in a table definition. constraints defined at the table level can be applied to one or more columns.

asserts a constraint defined in the assertion definition. Assertions can be associated with one or more tables.

A constraint defined in a domain definition by a domain constraint . A domain constraint is related to any column defined in a specific domain.

Of these three constraints, the most common constraint is a table-dependent constraint that contains the most restrictive options. A table-related constraint can be divided into two subcategories, table constraints and column constraints. The constraints in these two subcategories are defined in the table definition. The column definition contains a column constraint, and the table element also contains a table constraint. Both column constraints and table constraints support a large number of different types of constraints, but the assertion that the Union and domain constraints are limited to only one type of constraint.

The following diagram outlines the types of constraints you can create:

2. Using a NOT NULL constraint

An empty can be thought of as an identifier (an identifier is a character, number, or bit that represents some sort of fact about a column). In the case of null values, if no value is provided for the column, then the identifier is set to indicate that the value is unknown. Each column has a nullability property that indicates whether the column accepts null values. By default, all columns accept null values. However, you can override this by using a NOT NULL constraint that indicates that the column will not accept null values.

When initially created, you must specify a data type for each column in the table. The data type of a column determines the data that can be entered into a column in a table. When a column is created, it can be defined as a NOT NULL constraint. When inserting a row of data into a table, the column is null if no value is entered for a column. If you specify a column not NULL, it means that you are not allowed to enter a null value in it.

Its creation syntax:

<column_name> {<data_type> | <domain>} not NULL

For example, to create a table with a NOT NULL constraint column, we create a table called "Salesman information," which contains the column "salesman number", "Salesman name", "Home Address" and "phone". After the table is created successfully, when you add data to the table, you do not allow the column "salesman number" and "salesman name" to be empty.

CREATE TABLE Salesman Information

(Salesman number INTEGER not NULL,

Salesman name VARCHAR (+) not NULL,

Home Address VARCHAR (50),

Phone VARCHAR (12)

)

3. Unique constraints

In SQL integrity constraint types, both column constraints and table constraints support unique constraints. There are two types of unique constraints in SQL, the unique constraint and the primary KEY constraint.

3.1.1UNIQUE constraints

Unique constraints can be used to enforce uniqueness within a set of columns. For a column in a unique constraint, two rows in the table are not allowed to contain the same non-null value. The primary key also enforces uniqueness, but the primary key is not allowed to be null, and there can be only one primary key in each table, but you can have more than one in a unique constraint.

If you violate UN ique constraint, the system pops up information that violates the constraint.

3.1.2PRIMARY key Constraint

The PRIMARY key constraint, like the unique constraint, is also a SQL unique constraint. Both constraints allow only the unique values in the specified column, both of which can be applied to one or more columns, and are also defined as column constraints and table constraints.

PRIMARY key constraints Identify columns or sets of columns that uniquely identify rows in a table. A PRIMARY KEY constraint can:

1. Created as part of the table definition, 2. Added to a table that does not already have a PRIMARY key constraint, 3. If you already have a PRIMARY key constraint, you can modify and delete it.

However, due to the role played by the primary key in the table, the PRIMARY key constraint has two limitations:

1. Columns defined with the PRIMARY KEY constraint cannot contain null values, even if not NULL is not specified on the column, the column must still contain a data value; 2. Only one primary KEY constraint can be defined for each column.

4. Use the Froeign key constraint

The Froeign key (foreign key) constraint is primarily used to maintain a consistency relationship between two tables. The foreign key is established mainly by adding the column containing the primary key in one table to another table, which is the foreign key of the other table. Therefore, the foreign key is the primary mechanism for referential integrity between relational database China-enhanced tables, and columns defined as foreign keys are used to refer to columns defined as primary keys in another table. In other words, a foreign key is a column of data that relies on a column in another table, which ensures that the corresponding data exists in all two tables.

When you create a froeign key constraint, you must follow these guidelines :

The referenced column must be a candidate key in the referenced table (usually the primary key);

The Froeign key constraint can be created as a table constraint or a column constraint. If you create a foreign key as a table constraint, you can include one or more columns;

Foreign keys in a reference table must include the same number of columns as the number of columns referenced, and each reference column must use a data type that corresponds to the referenced column. But the reference column name does not have to be the same as the name of the referenced column;

If the referenced column is not specified when the Froeign key constraint is defined, the Lieme that is defined in the primary key of the referenced table is considered to be the referenced column.

For example, specify the Froeign key constraint in a table to associate two tables:

Create two tables, where the table named "Operator Information" contains the column "operator number", "Operator name" and "Contact phone", and the column "operator number" is specified as the primary KEY constraint, and the other table for "Inbound information" contains the column "Inbound number", "Date of entry", and " Operator number, the column "Inbound number" is specified as the PRIMARY KEY constraint and is associated with the operator information table by the column operator number, which specifies that the column operator number is the Roreign key constraint.

First create the Operator information table with the following statements:

CREATE TABLE operator Information

(operator number INTEGER PRIMARY KEY,

Operator name VARCHAR (50),

Contact Phone VARCHAR (12)

)

Then, according to the above requirements, create the "Inbound information" table with the following statements:

CREATE TABLE Inbound Information

(Inbound number INTEGER PRIMARY KEY,

Inbound Date smalldatetime,

Operator number INTEGER,

CONSTRAINT fk_ operator number

FOREIGN KEY (operator number)

REFERENCES operator Information (operator number)

)

5.CHECK constraints

As we can see in the integrity constraint graph, a check constraint can be defined as a table constraint, a column constraint, a domain constraint, or it is defined in an assertion. A check constraint allows you to specify a value that can be included in a column. For example, you can define a list of values that are worthy of being enumerated, or some other conditions that accurately limit the allowable values in a column.

5.1 Defining a CHECK constraint in table constraints and column constraints

Syntax rules for creating column Constraints :<column_name> {<data_type> | <domain>} CHECK {<search_condition>}

Syntax rules for creating table Constraints : [CONSTRAINT <column_name>] CHECK {<search_condition>}

Where,<search_condition> is similar to the query condition in the SELECT statement, where a range of values is specified for the column during table creation.

For example, create a salesperson information table that contains number, name, work time, and contact phone, and requires that when data is added to a table, the column "Work hours" should be between January 1, 2015 and May 1, 2015.

The following is a comparison of two constraints, created separately:

Define the CHECK constraint as a column constraint in the statement that created the table:

CREATE Table Sales Person information

(number INTEGER not NULL,

Name VARCHAR (50),

Working hours smalldatetime not NULL,

CONSTRAINT Ck_ to attend working hours

CHECK

(participating in working hours between ' 2015-01-01 ' and ' 2015-05-01 ')

)

Define the CHECK constraint as a table constraint in the statement that created the table:

CREATE Table Sales Person information

(number INTEGER not NULL,

Name VARCHAR (50),

Working hours smalldatetime not NULL

CHECK

(participating in working hours between ' 2015-01-01 ' and ' 2015-05-01 ')

)

5.2 Defining assertions

An assertion is simply a check constraint that can be applied to more than one table, so an assertion must be created independently of the table definition. Use the following syntax when creating assertions:

CREATE Assertion <constraint_name> CHECK <search_condition>

Creating an assertion is very similar to a check constraint on a dress table, but you must provide the necessary qualification after the Check keyword, and the condition of the assertion must always be true.

For example, to recreate the table "Sales information," which contains columns that are still not "item number", "Trade name", "Sales price", and "date of sale", the following are the creation statements:

CREATE TABLE Sales Information

(Item number INTEGER PRIMARY KEY,

A UNIQUE product name, VARCHAR (

Sales price money is not NULL,

Sales Date smalldatetime not NULL

)

The check constraint is not specified in the preceding statement. If you sum the data in the column sales price in the table and want the sum to be greater than 10000, you can create an assertion that adds the data from the sales price and verifies that the sum is greater than 10000.

CREATE Assertion As_ Sales price

CHECK

((SELECT SUM (sales price) from sales information) > 10000)

A subquery (SELECT SUM (sales price) from sales information) is used in the statement, and the result of this subquery is compared to 10000. If the sum of the data added in the "Sales Price" column is 10000, an error message pops up.

5.3 Creating Domain and domain constraints

There is also a constraint check constraint is a domain constraint in the Insert domain definition. The definition of a domain constraint is similar to the definition of another, except that you do not have to join a domain constraint and a specific column constraint or table. The domain constraint uses the keyword value when it is necessary to represent a value in a defined column. The syntax for creating a domain is as follows:

CREATE DOMAIN <domain_name> [as] <data_type>

[DEFAULT <default_value>]                                                          

[CONSTRAINT <constrain_name;] CHECK (<search_condition>)

For example, after the "Sales Information" table in the 5.1 example was created, the reference column "Sales Date" creates a field of opportunity smalldatetime data type, and requires that all values must be between January 1, 2015 and May 1, 2015.

CREATE DOMAIN Sales Date as smalldatetime

CONSTRAINT Ck_ Sales Date

CHECK

(VALUE between ' 2015-01-01 ' and ' 2015-05-01 ')

In the preceding statement, add the value keyword, which represents the values in the columns defined by the Sales Information field. Therefore, the values to be inserted must be in "2015-01-01" and "2015-05-01".

6. Usage rules

A rule restricts values that can exist in a table or a user-defined data type. , it can use a variety of methods to complete the validation of data values, you can use the function to return the verification information, or you can use the keyword between, like, and in completion to enter data check.

A rule is one of the database objects that functions as part of a check constraint, and uses it to limit the range of values entered for a new value when inserting or updating data into a column of a table. The difference between a rule and a check constraint is:

A check constraint is specified when a table is built with the CREATE TABLE statement, and the rule needs to be implemented as a separate database object;

Only one rule can be used on a column, but multiple check constraints may be used;

Rules can be applied to multiple samples and can also be applied to user-defined data types, while check-about-Sue can only be applied to the columns it defines.

A rule is one way to implement domain integrity, which verifies that the data in a database is within a specified range of domains. When the data in the database is inserted or updated, you need to check whether the new value follows the rules, and if the rule is violated, the operation will fail.

6.1 Creating a rule

Create rules use the CREATE RULE statement, which has the following format:

CREATE RULE <rule_name>

As

<condition_expression>

Where,<rule_name> indicates that the rule name created,<condition_expression> represents the condition that defines the rule. A rule can be any valid expression in the where itself, and can include arithmetic operators, relational operators, and keywords such as in, like, between, and so on.

For example, in the database "Sales management system" to create a rule named "City _rule", limit the input value must be "Beijing", "Shanghai", "Guangzhou", "Nanjing", "Shenzhen" one.

CREATE RULE City _rule

As

@ Supplier city in (' Beijing ', ' Shanghai ', ' Guangzhou ', ' Nanjing ', ' Shenzhen ')

6.2 Binding Rules

To use a rule, you must first bind it to a column or to a user-defined data definition type. You can use the Sp_bindrule stored procedure to bind a rule. The syntax format for the sp_bindrule stored procedure is as follows:

sp_bindrule [@rulename =] <rule_name>

[@objname =] <object_name>

[, @futureonly =] <futureonly_flag>

where [Objname=] <object_name> represents the table and column or user-defined data type that the rule is bound to, [@futureonly =] <futureonly_flag> The rule is used only if it is bound to a user-defined data type.

For example, the rule created in the previous example, "City _rule", is bound to the supplier's city column on the Vendor information table.

Sp_bindrule City _rule, ' supplier information. Supplier City '

6.3 Delete a rule

For rules that are no longer needed, you can use the DROP rule statement to delete them. To delete a rule first to remove the binding to the change team, unbind the rule can use the Sp_unbindrule stored procedure, its syntax format is as follows:

sp_unbindrule [@objectname =] <object_name>

[, @futureonly =] <futureonly_flag>]

For example, to delete the rule "City _rule", first remove the binding from the column "Supplier City" of the Supplier Information table, and you can unbind it using the following statement.

Sp_unbindrule ' supplier information. The supplier's City '

After unbinding, the rule can be removed using the drop RULE statement below.

DROP RULE City _rule

You can also delete a rule in Enterprise Manager, but before you delete a rule, you must determine that the rule does not have any bindings, that is, to delete a rule, you must first unbind the rule.

Three. Learning Summary

As for the completeness of the database, this is the end of the list, and this section is particularly simple and personal in relation to the other chapters of the collation. Are some simple SQL statement exercises, in the previous study, for those who are already familiar with SQL statements, this chapter is particularly important, no rules inadequate surrounding area.

For their own collation, but also because of the practice in the database to complement each other. Practice more to improve the understanding of this rule.

SQL Application and Development: (eight) data integrity