SQL Server Constraints Introduction

Constraint definitions

For a database, the integrity constraints of the basic table are divided into column-level constraints and table-level constraints:

Column-level constraint conditions

A column-level constraint is a constraint on a particular column, which is included in the column definition and can be separated by a space, without specifying a column name, directly following the other definition of the column.

Table-level constraint conditions

table-level constraints and column definitions are independent of each other, and are not included in the column definition, and are typically used to constrain two or more than two columns. Constraint introduction

In the database management system, it is very important to ensure the data integrity in the database. The so-called data integrity refers to the consistency and correctness of data stored in the database. Constraints define rules about allowable values in a column, which is a standard mechanism for enforcing integrity. Using constraints takes precedence over the use of triggers, rules, and default values. The query optimizer also uses constraint definitions to generate high-performance query execution plans.

Data integrity Classification

In SQL Server, data integrity can be divided into the following, depending on the database objects and scopes that are acting on the new data integrity measures:

Entity integrity

Entity integrity The simple thing is to think of each row in a table as an entity. Entity integrity requires the integrity of the table's identifier columns or primary keys. Entity integrity can be enforced by establishing unique indexes, PRIMARY key constraints, unique constraints, and the identity properties of the columns.

Domain integrity

Domain integrity refers to the input validity of a given column. Requires that the data for the specified column in the table have the correct data type, format, and valid data range. The methods for enforcing domain validity are: The restriction type (by data type), the format (through CHECK constraints and rules), or the range of possible values. Domain integrity is implemented by FOREIGN KEY constraints, CHECK constraints, DEFAULT definitions, not NULL definitions, and rules.

Referential integrity

Referential integrity is also known as referential integrity. Referential integrity maintains data consistency between the referenced table and the reference table, which is implemented through primary key (PRIMARY key) constraints and foreign key (FOREIGN key) constraints. Referential integrity ensures that key values are consistent across all tables. Such conformance requirements cannot refer to values that do not exist, and if the key value changes, all references to the key value in the entire database will be changed in a consistent way. In the referenced table, the row cannot be deleted or changed when its primary key value is referenced by another table. Referencing a non-existent primary key value is not allowed in the reference table.

Constraint classification

There are five types of constraints in SQL Server, namely the PRIMARY key constraint, the FOREIGN key constraint, the unique constraint, the default constraint, and the check constraint. View or create constraints that you want to use to Microsoft SQL Server Managment Studio.

PRIMARY key Constraint

There is often a combination of one or more columns in a table whose values uniquely identify each row in the table, such that one or more columns become the primary key (PrimaryKey) of the table.

A table can have only one primary key, and a column in a PRIMARY KEY constraint cannot be a null value.

Only the primary key column can be created as a foreign key for other tables.

FOREIGN key Constraint

FOREIGN KEY constraints are used to strengthen the connection between two tables (the primary table and the table) between one column or more columns of data.

The order in which foreign key constraints are created is to define the primary key for the primary table, and then to define a foreign key constraint from the table.

Only the primary key of the main table can be used as a foreign key from the table, and the constrained columns from the table may not be primary keys, and the primary table restricts the actions that are updated and inserted from the table.

Unique constraint

A unique constraint ensures that a column of data in a table does not have the same value.

Like a primary KEY constraint, a unique constraint also enforces uniqueness, but a unique constraint is used for a combination of one or more columns of a non-primary key, and a table can define multiple unique constraints.

Default constraint

If a default value constraint is defined in a table, when a user inserts a new data row, if the row does not have the specified data, the system assigns the default value to the column, and the system defaults to NULL if we do not set the default value .

A CHECK constraint is used to limit the range of values in a column.

A check constraint uses a logical expression to determine the validity of the data, to restrict the range of values entered into one or more columns, and to update the data in the column, the content to be entered must meet the criteria of the Check constraint, otherwise it will not be entered correctly.

If you define a CHECK constraint on a single column, the column only allows a specific value.

If a CHECK constraint is defined on a table, the constraint restricts the value in a specific column.

Constraints pros and cons

Advantages:

1, maintain database integrity.

2, guarantee the uniqueness of the data in the column.

3, INSERT, UPDATE, delete strict scope check mechanism.

4, fast.

5. You can refer to other columns.

6, occurs before the command executes.

7, follow the ANSI standard.

Disadvantages:

1, INSERT, UPDATE, delete need to check the rules more trouble.

2, each table must be redefined.

3. You cannot reference another table.

4. Cannot bind to data type.

SQL Server Constraints Introduction