What is the concept of database integrity?

I. The concept of integrity is introduced to avoid data corruption or inconsistency among multiple tables during data addition, deletion, and modification operations. Data Integrity means that the data stored in the database is correct and consistent. Ii. Integrity type four types of integrity constraints: domain integrity constraints: entity integrity

I. The concept of integrity is introduced to avoid data corruption or inconsistency among multiple tables during data addition, deletion, and modification operations. Data Integrity means that the data stored in the database is correct and consistent. Ii. Integrity type four types of integrity constraints: domain integrity constraints: entity integrity

I. Concept of integrity

Data integrity is introduced to avoid data corruption or data inconsistency among multiple tables during operations such as adding, deleting, and modifying data. Data Integrity means that the data stored in the database is correct and consistent.

Ii. Integrity type

Four types of integrity constraints:

1. Domain integrity constraints:
2. Entity Integrity Constraint: PRIMARY KEY
3. Integrity constraints: foreign key, REFERENCE
4. User-Defined integrity constraints: CHECK, DEFAULT, NOT NULL

1) entity integrity

Entity: a record in a table. An entity is a record in a table.

Entity Integrity: there cannot be identical records in the table, and each record must have a non-empty and non-repeated primary key value.

Methods To achieve Object Integrity: Set primary keys, unique indexes, and unique constraints.

2) domain integrity

Domain integrity: the data added to the table must match the data type, format, and valid data length.

Methods To implement domain Integrity: CHECK constraints, foreign key constraints, default constraints, non-empty definitions, rules, and data types set during table creation.

3) integrity of reference

Integrity of reference: Also known as integrity of reference. It refers to two or more tables associated with the foreign key through the primary key. The values of related fields must be consistent.

Method for implementing entity Integrity: foreign key constraints.

4) User-Defined integrity

User-Defined Integrity: it is a specific rule defined by the user according to the constraints observed in a specific application field.

Iii. Constraints

Constraint: A method provided by SQL Server to automatically force data integrity. It maintains data integrity by defining the value rules of columns.

Common constraints: not null, CHECK, UNIQUE, primary key, foreign key, DEFAULT

1) primary key constraint: Define a primary key in the table to uniquely identify each row of records in the table

Features: Each table can only have one primary key, and the primary key can be one column or multiple columns. The primary key cannot be blank, and the primary key value cannot be repeated.

2) UNIQUE constraint: it is mainly used to limit that the values in non-primary key columns of a table cannot be repeated.

Features: A table can define multiple unique constraints.

3) not null constraint: it is used to set a column value to be NULL.

Feature: If a column is set to not null, data must be inserted when a record is added.

4) CHECK constraint: it uses a logical expression to limit the data values that can be accepted by columns in a table.

For example, if the score value is between 0 and, you can create a CHECK constraint for the score field so that the value is within the normal range.

5) DEFAULT constraint: it creates a DEFAULT value for a column in the table. When a record is added to the table, if no input value is provided, the column is automatically assigned with the DEFAULT value.

Feature: the default value can be a constant, function, or expression. You can use the default value to increase the speed of data input.

6) foreign key constraint

Foreign key: a column or column combination in a table. It is not the primary key of the table, but the primary key of the other table.

Features: Data Consistency between two tables.

When updating the database, the table cannot contain records that do not meet the integrity requirements, so as to ensure that the user is provided with correct and valid data. The most direct way to achieve this is to operate on each update when writing a database application.

Integrity check. However, such checks are often complex, repetitive, and inefficient.

SQL regards various integrity constraints as part of the definition of the database mode and is maintained by the database management system. This effectively prevents accidental damage to the database and improves the efficiency of Integrity Detection, it also reduces the burden on programmers.

Object Integrity and primary key: Object Integrity is achieved through the definition of the primary key. Once an attribute or attribute group is defined as a primary key, each attribute of the primary key cannot be null.

There cannot be two records with identical primary key values in the table.

The primary key can be defined using the primary key in the create table statement. There are two ways to define the primary key: one is to add a keyword after the attribute, and the other is to add an additional clause defining the primary key in the Attribute Table: primary key (primary key Attribute Table ).

(1) Add a keyword definition after the attribute:

CREATE TABLE StudentInfo(    StudentID       char(8)    PRIMARY KEY,    StudentName    varchar(10),    StudentSex      bit);

(2) Add an additional clause defining the primary key:

CREATE TABLE StudentInfo(    StudentID        char(8)，    StudentName    varchar(10),    StudentSex        bit,    PRIMARY KEY(StudentID));

If the table's primary key only contains a single attribute, the preceding two methods can be used. If a primary key consists of multiple attributes, you can only use the second method.

In addition to the primary key, SQL provides a description method similar to the candidate code, which is defined by the keyword UNIQUE. (unlike the candidate code, attributes defined as UNIQUE can be defined as null values, however, only one record can record the value of this attribute as NULL, indicating that the value of this attribute (or attribute group) cannot be repeated.

A table can have only one primary key, but can have multiple "UNIQUE" definitions.

(1) Description of external code Constraints

There are two methods to describe the external code:

Add the keyword REFERENCES directly after the description of this attribute (attribute name and type), followed by the description of the primary key of the corresponding table.

Format: REFERENCES <父表名> ( <属性名> )

It indicates that StudentID in the RelationInfo table is an external code and the reference relationship is StudentInfo.

CREATE TABLE RelationInfo(    RelationID    int identity(1, 1),    StudentID    char(8) REFERENCES StudentInfo(StudentID),    DepartID     char(4)   );

In create table? After the statement attribute list, add the description clause of the external code

Format: FOREIGN ( <属性名表> ) REFERENCES <父表名> ( <属性名表> )

CREATE TABLE RelationInfo(    RelationID    int identity(1, 1),    StudentID    char(8) ,    DepartID     char(4) ,    FOREIGN KEY (StudentID) REFERENCES StudentInfo(StudentID)  );

(2) implementation strategies based on integrity constraints

When your operation violates the preceding rules? Two optional solutions are provided for database Implementers: RESTRICT (Restriction Policy) and CASCADE (CASCADE policy );

<1> restriction policy

The restriction policy is the default SQL policy. Any update that violates the integrity of the reference is rejected by the system.

<2> cascade policies

When a user deletes or updates a key pointed to by a foreign key, SQL provides another scheme, that is, cascade policy.

Add the on delete? And? On update clause implementation:

[ ON DELETE { CASCADE | NO ACTION } ]

[ ON UPDATE { CASCADE | NO ACTION } ]

If the on delete? Or? On update, the default value is no action.

ON DELETE NO ACTION

If you try to DELETE a row that contains a key referenced by the foreign key in the existing row of other tables, an error is generated and the DELETE operation is rolled back.

ON UPDATE NO ACTION

If you try to update the key value in a row, and the row contains the key referenced by the foreign key in the existing row of other tables, an error is generated and rolled back? UPDATE.

CASCADE? Allows you to delete or update cascading key values between tables. The foreign key relationships of these tables can be traced back to the tables for modification. Cannot be any? Timestamp? Specifies the foreign key and primary key of the column? CASCADE.

ON DELETE CASCADE

Specify that if you try to delete a row that contains the Foreign keys referenced by the existing rows of other tables, all rows that contain the Foreign keys will also be deleted. If cascade references are also defined in the target table, cascade operations are also specified for the rows deleted from those tables.

ON UPDATE CASCADE

If you try to update the key value in a row, and the key value of this row is referenced by the foreign key in the existing row of other tables, all foreign key values will also be updated to the new value specified by the key. If cascade reference is also defined on the target table, the specified cascade operation is also performed on the key values updated in those tables.

N User-Defined integrity constraints: SQL provides non-empty constraints, CHECK constraints on attributes, CHECK constraints on tuples, and triggers to meet user integrity requirements.

<1> attribute-based CHECK Constraints

The CHECK clause ensures that the attribute value meets certain prerequisites. The general format of the CHECK clause is:

CHECK <条件>

The CHECK constraint of an attribute can be followed by the attribute definition, or an additional clause can be added to the definition statement to describe it.

Set the age value in the StudentInfo table to no less than 18 or greater than 65. You only need to describe the age property as follows:

Age int CHECK (age> = 18 and age <= 65)

<2> tuples-based constraints

CREATE TABLE salary(    Eno char(4),    Basepay decimal(7, 2),    Insure decimal(7, 2),    Fund decimal(7, 2),    CHECK (Insure + Fund < Basepay));

In the preceding example, the CHECK constraint involves multiple attributes in the table, which are tuples.

? Constraint update: the constraint is the same as that of tables and views in the database. You can add, delete, or modify an update. To update a CONSTRAINT, You need to name the CONSTRAINT when defining the CONSTRAINT, and add the keyword CONSTRAINT and the name of the CONSTRAINT before the CONSTRAINT.

For example, to describe the primary key in the StudentInfo table, name it PK_StudentInfo_ID

CREATE TABLE StudentInfo(    StudentID        char(8),    StudentName    varchar(10),    StudentSex        bit,    CONSTRAINT PK_StudentInfo_ID  PRIMARY KEY(StudentID));

You can use the alter table statement to update attributes or TABLE-related constraints.

(1) Delete constraints:

Alter table drop constraint? Constraint name

(2) Add Constraints

Alter table add constraint? Constraint name constraint definition.

Integrity during update

1. Modify/delete constraints: When a sub-table has a corresponding record, the row of the parent table cannot be updated.
2. Delete cascade: delete a row from the parent table and delete the corresponding row from the child table.
3. Update cascade: the parent table updates a row and the child table updates the row.
4. Null Value and default value: Modify/Delete the value of the parent table. The corresponding value of the sub-table is null or the default value.