Experiment 5 database integrity Technology

[PURPOSE]

1. Master the concept of integrity;

2. Familiar with SQL Server integrity technology.

3. Understand the integrity handling measures for SQL Server violations.

[Lab nature]

Comprehensive Experiment

[Experiment Guide]

1Integrity Overview

The correctness and compatibility of database integrity index data (logical rather than physical. To prevent non-semantic data in the database and prevent incorrect data input and output. The database integrity technology consists of two parts: integrity constraints and integrity checks. Integrity constraints mean to maintain the integrity of the database. DBMS provides semantic constraints attached to the database data and stores them in the database as part of the database mode. Integrity check means to check whether the database meets the integrity constraints.

Integrity constraints apply to three types of objects: relational, tuples, and columns. Column constraints are mainly constraints on column types, value ranges, precision, and sorting. The constraint of tuples is the constraint on the relationship between fields in tuples. A link constraint is a constraint on the links between several tuples, a link set, and a link. Integrity constraints involve these three types of objects, whose statuses can be static or dynamic.

Integrity constraints are generally classified into Entity integrity and reference integrity custom integrity. To define constraints on Object Integrity, you must consider modifying the master code in the link; defining constraints on the integrity of the reference should consider whether the external code can accept null values, and the issue of deleting tuples in the referenced relationship (cascading deletion or restricted deletion).

RDBMS generally provides define integrity constraints: Create a table when creating a table as part of the table definition (using create table); or add it to a table without integrity constraints (alter table ).

2,Ms SQL ServerIntegrity Control Technology.

For details, see "create and manage databases" in "Books Online"> "tables"> "design tables"> "Use constraints, default values, and null values", and "create and manage databases ". database> table> statements related to the topic "Create and modify table" and "Transact-SQL reference" of "online slave book.

Integrity in ms SQL Server is divided:

(1) entity integrity. Entity integrity defines a row as a unique entity of a specific table. Entity integrity forces the integrity of the table's identifier column or master code (by indexing, unique constraints, primary key constraints, or identity attributes ).

(2) domain integrity. Domain integrity refers to the input validity of a given column. Methods To force domain validity include: restriction type (by data type), format (by check constraints and rules) or the range of possible values (through foreign key constraints, check constraints, default definitions, not null definitions, and rules ).

(3) integrity of reference. When a record is input or deleted, the reference integrity maintains the defined relationship between the tables. In ms SQL Server, the integrity of a reference is based on the relationship between the external code and the primary code or between the external code and the unique code (through the foreign key and check constraints ). Integrity of references ensures that the key values are consistent in all tables. Such consistency requires that a nonexistent value cannot be referenced. If the key value changes, all references to the key value must be consistent throughout the database.

When you force reference integrity, ms SQL Server disables the user from performing the following operations:

If no associated record exists in the primary table, add the record to the relevant table.

Change the values in the primary table and isolate the records in the relevant table.

Delete a record from the primary table, but there are still records matching the record.

(4) User-Defined integrity. User-Defined integrity allows you to define specific business rules that do not belong to any other integrity classification. All integrity types support user-defined integrity (all column-level and table-level constraints, stored procedures, and triggers in create table ).

When designing a table, you need to identify the column's valid values and decide how to implement data integrity in the column. Ms SQL Server provides a variety of mechanisms to force data integrity in columns:

Ø primary key constraints

Ø foreign key constraint

Ø unique constraints

Ø check Constraints

Define default

Blank

3, Primary keyConstraints

A table often has a combination of columns or columns. Its values can uniquely identify each row in the table. Such a column or multiple columns are called the primary code of the table. It can be used to enhance the entity integrity of tabulation. When creating or changing a table, you can create a master code by defining the primary key constraint.

A table can have only one primary key constraint, and columns in the primary key constraint cannot accept null values. Because the primary key constraint ensures unique data, it is often used to define the ID column.

When you specify a primary key constraint for a table, the ms SQL Server enforces data uniqueness by creating a unique index for the primary code column. When the primary code is used in the query, the index can also be used to quickly access data.

If the primary key constraint is defined on more than one column, the values in the column can be repeated, but the values of the combination of all columns in the primary key constraint definition must be unique.

4, Foreign keyConstraints

An external code is one or multiple columns used to establish and enhance the link between two table data. You can create a link between two tables by adding one or more columns of the primary code value in the saved table to another table. This column becomes the external code of the second table. When creating or changing a table, you can create an external code by defining the foreign key constraint. The foreign key constraint is not only linked to the primary key constraint of another table, but can also be defined as a unique constraint that references another table. Although the main purpose of the foreign key constraint is to control the data stored in the external code table, it can also control the modification of the data in the main code table.

When a user tries to delete or update the code that the external code points to, cascading reference integrity constraints can define the action taken by the ms SQL Server.

5, UniqueConstraints

You can use the unique constraint to ensure that duplicate values are not entered in the non-primary code column. Although the unique constraint and the primary key constraint both force uniqueness, the unique constraint instead of the primary key constraint should be used to force the following uniqueness:

A column or column combination of non-primary codes. One table can define multiple unique constraints, but only one primary key constraint can be defined.

Ø columns with null values are allowed. Columns that allow null values can define the unique constraint, but cannot define the primary key constraint.

6, CheckConstraints

The check constraint forces the integrity of a field by limiting the value entered in the column. This is similar to the value in the foreign key constraint control column. The difference is how they determine which values are valid: the foreign key constraint obtains a list of valid values from another table, and the check constraint judges from the logical expression rather than data based on other columns.

Multiple check constraints can be used for a single column. Set the value according to the order in which the constraint is created. By creating a check constraint at the table level, you can apply this constraint to multiple columns.

7And defaultConstraints

Each column in The tuples must have a value, even if it is null. In this case, when a new row is loaded into the table, the value of a column may not be known, or the value does not exist. If this column allows null values, you can assign null values to this row. Because you do not want to have columns that can be empty, a better solution is to define the default definition for the column if appropriate.

When a row is loaded into a table that defines default for the row, if no column value is specified, it is implicit requirement that ms SQL server load the default value to the column

8And allow null values

Whether the column is empty determines whether null values are allowed in the table. Null or null is not equal to zero (0), empty, or zero-length string (such as ""). null means no input. Null usually indicates that the value is unknown or undefined. For example, the null value in the price column in the titles table of the pubs database does not indicate that the book has no price, but that the price is unknown or not set yet. In short, the null value will make the query and update more complex, and other column options, such as the primary key constraint, cannot use columns that allow null values. Therefore, avoid allowing null values.

If you insert a row but do not include a value for a column that allows null values, the ms SQL Server provides a null value (unless there is a default definition or a default object ).

Specify that a column does not allow null values, and ensure that a column in the row always contains data to maintain data integrity. If null values are not allowed, you must enter a value in the column when writing data to the table. Otherwise, the row will not be received from the database.

[Experiment content]

Use Qixin
Drop table employee
Drop table Department

Create Table Department
(
Department No. Char (4 ),
Name varchar (20) not null,
Manager name varchar (8 ),
Address varchar (50 ),
Phone number varchar (20 ),
Constraint pk_department No. Primary Key (Department No ),
Constraint U _ name unique (name)
)

Create Table employee
(
Employee ID char (4 ),
Name varchar (8) Not null,
Age int,
Position varchar (10 ),
Salary money,
Department No. Char (4 ),
Constraint pK _ employee ID primary key (employee ID ),
Constraint FK _ Department No. Foreign key (Department No.) References department (Department No.) on Delete cascade,
Constraint CK _ age check (age <= 60)
)

Insert into Department values ('20170101', 'Finance core', 'zhang san', 'hubei Automotive Industry College ', '20160301 ')
/* (1 )*/
-- If you re-insert into Department values ('20170101', 'Finance core', 'zhang san', 'hubei Automotive Industry College ', '20160301 '),
-- The primary key constraint 'pk _ Department _ 571df1d5 'is violated '. Duplicate keys cannot be inserted in the object 'department.
/* (2 )*/
-- If you execute the insert into Department values ('20170101', 'Finance core', 'zhang san', 'hubei Automotive Industry College ', '20160301 ')
-- The unique key constraint 'uq _ Department _ 5812160e 'is violated '. Duplicate keys cannot be inserted in the object 'department.
Insert into Department values ('20170101', 'academic service', 'lily', 'hubei automotive industry Emy ', '20160301 ')
Insert into Department values ('1234568', 'personnel key', 'wang 2', 'hubei automotive industry Emy ', '1234568 ')

Insert into employee values ('2017030', 'zhang wei', '30', 'kechang ', 0001, '2016030 ')
Insert into employee values ('000000', 'Li hong', '25', 'Deputy kechang ', 0002, '123 ')
Insert into employee values ('20170101', 'wang Qiang ', '33', 'kechang', 0003, '20160301 ')
Insert into employee values ('000000', 'zhaodong ', '34', 'Deputy kechang', 0004, '20170301 ')
Insert into employee values ('1234568', 'chen 3', '29', 'kechang ', 0005, '1234568 ')
Insert into employee values ('20170301', 'sunbo', '28', 'Deputy kechang ', 0006, '20160301 ')
-- If you execute insert into employee values ('20170901', 'chenhong', '70', 'Deputy kechang ', 0007, '123 ')
-- The insert statement conflicts with the column check constraint 'ck _ employee _ age _ 656c112c.
-- The conflict occurs in the database 'qixin', table 'employees', column 'age '.

Select * from department
Select * from employee

-- Cascade deletion when on Delete cascade is specified. When a department table record is deleted, related records in the employee table are also deleted.
Delete from department where Department No. = '20140901'
Delete from department where Department No. = '20140901'
Delete from department where Department No. = '20140901'

-- If you do not specify on Delete cascade, it is restricted by default. When you delete a department table record
-- The delete statement conflicts with the column reference constraint 'fk _ employee _ Department number _ 6d0d32f4.
-- The conflict occurs in the database 'qixin', table 'employees', and column 'department No '.