1 integrity of the dataConstraints are used to ensure the accuracy and consistency of data. The integrity of the data is a guarantee of the accuracy and consistency of the data. Data integrity (Integrity) refers to the accuracy (accuracy) and reliability (reliability) of data. Divided into the following four categories:
1) Entity integrity: Specifies that each row of the table is the only entity in the table.
2) Domain integrity:Refers to a column in a table that must satisfy a particular data type constraint, where the constraints include the range of values, precision, and so forth.
3) Referential integrity: Refers to the data of the primary and external keywords of two tables should be consistent, ensure the consistency of data between tables, prevent data loss or meaningless data spread in the database.
4) User-defined integrity: Different relational database systems, depending on their application environment, often require some special constraint conditions. User-defined integrity is a constraint on a particular relational database that reflects the semantic requirements that a particular application must meet.
Types of integrity constraints:Can be divided into three types: table-related constraints, domain constraints, assertions (assertion)
1) Constraints related to the table:is a constraint defined in the table. You can define the constraint when the column is defined, which is called a column constraint, or you can define a constraint at the table definition, called a table constraint.
2) domain constraint:A constraint defined in a domain definition that is related to any column defined in a particular domain.
3) Assertion (assertion):A constraint defined at the time of an assertion definition that can be associated with one or more tables.
I. Constraints related to the table:Includes column constraints (table Constraints +not NULL) and table constraints (PRIMARY key, foreign key, check, UNIQUE).
(1) NOT NULL (non-NULL) constraint:Only used to define column constraints. The syntax is as follows: Colunm_name datatype | Domain NOT NULL instance: CREATE TABLE Employee (emp_id int not null,emp_name varchar (40) is not null,address varchar,) after creation, if you go to table E Insert null value in Mployee table, insert into Employee values (1,null, ' Neimeng ') will be faulted.
(2) unique (unique) constraint: Used to indicate that the value on the column that created the unique constraint must be unique. The syntax is as follows: Colunm_name datatype | Domain Unique instance: CREATE TABLE EmployeeInfo (emp_id int not null,emp_name varchar (TEN) not Null,phone char (one) unique,address varchar (40), when inserting data to EmployeeInfo, if two records of the phone inflexible one, insert into employeeinfo values (1, ' abcdwxc ', ' Neimeng ', ' 13612345678 ') insert into employeeinfo values (2, ' Terry ', ' Neimeng ', ' 13612345678 ') will cause an error. In addition to adding a unique constraint when defining a column, you can also add a unique constraint as a table constraint. This is the element that is defined as a table. The syntax is as follows: [CONSTRAINT constraint_name] Unique (column1,column2,.....) Example: Create TABLE employeeinfo (emp_id int not null,emp_name varchar (TEN) not Null,phone char (one) address varchar (+), Constra int P_uniq Unique (phone))
(3) primary key (primary key) constraint:Used to define the primary key of the base table, with a unique identifier, whose value cannot be null or duplicated to guarantee the integrity of the entity. The syntax is as follows: Colunm_name datatype | Domain primary key instance: Drop table Employeeinfocreate table EmployeeInfo (emp_id int primary key,emp_name varchar (TEN) NOT NULL , phone char (one), address varchar (40), an error occurs if the emp_id inserted into the EmployeeInfo table is repeated or if emp_id is a null value when inserted. You can create a PRIMARY KEY constraint when you create a table, or create a primary key after the table is finished, for example: ALTER TABLE EMPLOYEEINFOADD constraint E_prim primary KEY (emp_id) primary key with Unique differences: 1. In a table, you can define only one primary KEY constraint, but you can define multiple unique constraints. 2. For a column or combination of columns that are specified as primary key, none of the columns can have null values, and NULL is allowed for the unique key constrained by unique, except that there is at most one null value.
(4) foreign key (foreign key) constraint: Defines the connection of data in one table to data in another table. The FOREIGN KEY constraint specifies a column or set of columns as the foreign key, where the table that contains the foreign key is called a child table, and the table that contains the primary key referenced by the foreign key is called the parent table. The system guarantees that the value of the table on the foreign key is either a primary key in the parent table or a null value, which guarantees the connection between the two tables, ensuring referential integrity of the entity. The syntax is as follows: Colunm_name Datetype | Domain references table_name (column) [Match full|partial|simple]//NOTE: SQL Server does not support. [referential triggered action] Description: TABLE_NAME is the table name of the parent table, and column is the primary key value in the parent table corresponding to the foreign key. [Match Full|partial|simple] is an optional clause that sets how to handle null values in foreign keys. [referential triggered action] is also an optional clause that is used to set guidelines for updating and deleting foreign key columns. You can create a foreign key constraint for one or more columns of a table, and if you create a FOREIGN KEY constraint for multiple columns, you will correspond to the corresponding primary key in the primary table, respectively. Example: Create TABLE EmployeeInfo (emp_id int primary key,emp_name varchar (TEN) not Null,account char (4) Primary Key,phone char (1 1) address varchar (+), CREATE table emp_sal (emp_id int, account CHAR (4), Salary DECIMAL (5,1), CONSTRAINT e_sal FOREIGN KEY (Emp_id,account) REFERENCES EmployeeInfo (Emp_id,account)) can also be added to a table after the table is created. As follows: Create TABLE emp_sal (emp_id int, emp_name varchar (ten) not Null,account CHAR (4), Salary DECIMAL (5,1), ALTER TABLE EMP_SA Ladd CONSTRAINT e_sal FOREIGN KEY (emp_id,account) REFERENCES employeeiNFO (Emp_id,account) The role of the foreign key: Ensure that each emp_id column of the table emp_sal corresponds to the corresponding employeeinfo in table emp_id. At this point, the table EmployeeInfo is the parent table, and the table emp_sal is the child table. The emp_id column of the child table refers to the emp_id column of the parent table. If you want to insert a value in the emp_id column of the child table, the first emp_id column of the parent table must exist, or the insertion fails. If you want to delete a value from the parent table's emp_id, you must not delete all the corresponding values in the emp_id column of the child table. (Note: The value on the Foreign key column can be null).
Potential Problems: Because a null value is available on the Foreign key column, the DBMS skips the check for the foreign key constraint, so if you insert emp_sal the following data: INSERT into emp_sal values (6,null,null) is inserted into Emp_ Sal, but the related columns of its primary table do not exist.
Workaround:(1) Adds a NOT NULL constraint to the column of the Union foreign key, but this restricts some of the user's operations. (2) The match clause is used. (SQL Server does not support).
Update, delete operation rules:A loss of matching integrity is caused when a row with a primary key value is deleted or updated, and the value matches one or more of the values in the foreign key of the child table. In the foreign key creation syntax, an optional on update and an ON DELETE clause are provided, which is the [referential triggered action] above. You can use this to keep referential integrity. On update/on DeleteNo action|cascade|restrict|set null|set defaultno Action: When you update or delete data in a parent table, the action is disabled if the foreign key in the child table violates referential integrity. However, under certain conditions, temporary can occur, but in the final state of the data, the referential integrity of foreign keys cannot be violated. Cascade: When the data of the referenced column in the parent table is updated or deleted, the corresponding data in the child table is also updated or deleted. Restrict: Unlike the no action rule, only the data in the reference column can never violate the referential integrity of the foreign key, nor can it be temporary. Set NULL: When the parent table data is updated or deleted, the corresponding data in the child table is set to a null value, provided that the corresponding column in the child table allows null values. Set default: When the parent table data is updated or deleted, the data in the child table is set to the defaults. The precondition is that the corresponding column in the child table is set with a default value.
(5) Check (check) constraint:Used to check the allowable range of field values. Each time the DBMS executes the Delete,insert or UPDATE statement, it filters the constraint. If true, execution is performed. Otherwise, cancel execution and prompt for an error. The column definition syntax is as follows: Column Datetype | The domain check (search condition) Table constraint syntax is as follows: The constraint constraint_name check (search condition) instance is as follows: Create TABLE Emp_sal (EMP _id int, account CHAR (4), Salary DECIMAL (5,1), constraint validsal check (Salary >=1000 and salary<=10000)) if at this time, Inserting the following statement into the table will make an error: (Because salary is not satisfied with a constraint greater than or equal to 1000.) ) insert into emp_sal values (8, ' 12324343 ', 800.0)
Two, domain constraints: (SQL Server does not support the following syntax: Create domain domain_name as data type[default default_value][constraint constraint_name] Check ( Value condition expression) For example: Create domain valid_no as intconstraint constraint_no Check (value between and 999) and then create table , use the Valid_no domain. CREATE TABLE TestDomain (emp_id valid_no,emp_name varchar (10),)
third, assert the constraint:It is not necessary to bind to a particular column, which can be understood as a check constraint that can be applied to multiple tables, so assertions must be created independently of the table definition. The syntax is as follows: Create assertion Constraint_namecheck Search Condition Example: Create assertion Namecheck (emp_sal.emp_id in (select Emp_ ID from EmployeeInfo where emp_name was not null) after an assertion is added, the search condition in the assertion is evaluated whenever an attempt is made to add or modify data in the Emp_sal table, or False to cancel execution, giving a hint
From Baidu Encyclopedia.
Database Foundation 2 (Integrity constraints)
