Constraints in SQL Server

Summary

The constraints in SQL Server are certainly not unfamiliar to everyone. However, not many people are quite clear about the true meaning of the constraints. This article describes in detail what constraints are and how to apply and use these constraints in database programming to achieve better programming results.(For some content in this article, refer to the SQL Server online manual)

Content

	Data Integrity Classification
	Entity integrity
	Domain integrity
	Integrity of reference
	User-Defined integrity
	Primary KeyConstraints
	DefaultConstraints
	CheckConstraints
	UniqueConstraints
	Foreign keyConstraints

Body

In the database management system, it is very important to ensure data integrity in the database. Data integrity refers to the consistency and correctness of the data stored in the database. The rule defining the allowed values in a column is a standard mechanism for forcible integrity. Use constraints take precedence over triggers, rules, and default values. The query optimizer also uses the constraint definition to generate a high-performance query execution plan.

In SQL Server books online, the data integrity is explained as follows:"All data values stored in the database are in the correct status. If an incorrect data value is stored in the database, the database is deemed to have lost data integrity."Forced Data integrity can ensure the quality of data in the database.

For example, if an employee whose value of employee_id is 123 is entered, the database should not allow other employees to use the same ID value. If you plan to set the value range of the employee_rating column to 1 to 5, the database should not accept 6. If the table contains a dept_id column that stores the employee's Department numbers, the database should only accept valid Department numbers in the company.

Data Integrity Classification

In SQL Server, data integrity can be divided into the following types based on the database objects and ranges used by the new data integrity measures.

	Entity integrity
	Domain integrity
	Integrity of reference
	User-Defined integrity

In SQL Server books online, "There are two important steps to plan a table: Identify the column's valid values and determine how to force data integrity in the column ."

Entity integrity

To put it simply, every row in the table is treated as an object. Entity integrity requires the integrity of the table's identifier column or primary key. Entity integrity can be implemented by creating unique indexes, primary key constraints, unique constraints, and column identity attributes.

Domain integrity

Domain integrity refers to the input validity of a given column. The data in the specified column in the table must have the correct data type, format, and valid data range. Methods To force domain validity include: restriction type (by data type), format (by check constraints and rules), or range of possible values. Domain integrity is achieved through foreign key constraints, check constraints, default definitions, not null definitions, and rules.

Integrity of reference

Integrity of reference is also called integrity of reference. The integrity of the reference to maintain data consistency between the referenced table and the referenced table, which is achieved through the primary key constraint and the foreign key constraint. 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. In the referenced table, when its primary key value is referenced by other tables, this row cannot be deleted or changed. In the reference table, the primary key value that does not exist cannot be referenced.

When integrity is forcibly referenced, SQL Server prohibits you from performing the following operations:

	When there are no associated records in the primary table, add the records 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 related records that match the record.

For example, for the sales and titles tables in the pubs database, the reference integrity is based on the relationship between the foreign key (title_id) in the sales table and the primary key (title_id) in the titles table.

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.

The purpose of establishing and using constraints is to ensure data integrity. constraints are application rules enforced by SQL Server. They can restrict the format and possible values of data stored in tables. Constraints are declared in the create table statement as part of the database definition, so they are also known as declarative integrity constraints. The constraint is independent from the table structure. You can add or delete a table by using the alter table statement without changing the table structure. When a table is deleted, all the constraint definitions contained in the table are also deleted.

Primary key constraint

In each database table, one or more columns are often used to uniquely identify each row in the table. It's like the ID card we usually use, which uniquely identifies everyone. Such a column or multiple columns are called the primary key. Using the primary key, you can force the Object Integrity of the table.

Each table has only one primary key constraint. To put it simply, it creates a unique index to ensure the entity integrity of the specified column. When the primary key constraint is used, the null attribute of the column must be defined as not null, that is, the column with the primary key cannot be blank.

Because the primary key constraint ensures unique data, it is often used to define the ID column. An identifier column is a column in the table that has the identity attribute assigned. The ID property generates a unique number.

Some technical enthusiasts who are new to SQL Server programming usually have such questions: why do we have to create a primary key in a table? The answer is obvious,Creating a primary key not only ensures the integrity of the data in the table, but also sets up a primary key for the table. Microsoft SQL server can create a unique index for the primary key to force the uniqueness of the data.

When a primary key is used in a query, that is, the column where the primary key is located is used as a keyword for query. This can also be used to quickly access data. If the index type is not specified in the primary key constraint, the index created by default is a cluster index. This index can only be deleted by deleting the primary key constraint or its related table methods, but cannot be deleted using the drop index statement. When the primary key constraint is referenced by the foreign key constraint of another table, the primary key constraint cannot be deleted. to delete the constraint, you must first Delete the foreign key constraint.

When we create a column constraint, we call it a column-level primary key constraint. When we apply it to multiple columns, we call it a table-level primary key constraint.

The column-level primary key constraint is defined in the following format:

[Constraint constraint_name] Primary Key [clustered | nonclustered] [With [fillfactor = fillfactor] [On {filegroup | default}]

Table-level primary key constraint definition style:

[Constraint constraint_name] Primary Key [clustered | nonclustered] {(Column [,... N])} [With [fillfactor = fillfactor] [On {filegroup | default}]

In the preceding primary key constraint definition, the with clause is set to the page fill level of the index created by the primary key constraint. The on clause specifies the name of the database file group that stores the index. Separating index files and table data files into data files on different hard drives in the database helps reduce the load on a single hard disk.

Default Constraint

Use the default constraint. If the data is not displayed as a column when you Insert a new row, the system will assign the data to the column by default. For example, in the PAYTERMS column of a table, the database server can fill in "???" when the user does not enter the data "???" Or "fill in later ". The default value constraints provide the default value constraints. The default values can be constants, functions, system 0-in functions, null values, and so on. The zero-entry functions include current_timestamp, system_user, CURRENT_USER, user, and session_user. The default value constraints are defined in the following format:

[Consreaint constraint_name] Default constant_expression

The constraint_name parameter specifies the default constraint name. The constant_expression expression provides the default value for the column. When using the default constraint, pay attention to the following two points:

1.	Each column can have only one default constraint.
2.	The constraint expression cannot refer to other columns in the table and other tables, views, or stored procedures.

Check Constraints

The check constraint is mainly used to restrict the possible values that are input into one or more columns, so as to ensure the domain integrity of data in the SQL Server database. For example, you can force a user to have more than 10 passwords when creating a user's database. Multiple check constraints can be created for each label. The check constraint can contain search conditions, but cannot contain subqueries.

Similarly, we can create constraints for each column in the Table. Each column can have multiple check constraints. However, if you use the create table statement, you can only create one check constraint for each column. If a check constraint is applied to multiple columns, it must be defined as a table-Level Check constraint.

In an expression, you can enter a search condition, which can contain a connector such as "and" or. The column-Level Check constraint can only refer to the constrained column, while the table-Level Check constraint can only refer to the column in the table. It cannot refer to other columns in the table.

For example, we use the following statement to add a zip_code column and corresponding check constraints to the tb_check_constraint table:

Alter table tb_check_constraint Add Zip_code char (6) null Constraint ch_zip_code check (Zip_code like '[0-9] [0-9] [0-9] [0-9] [0-9] [0-9]')

Similarly, we can use check or nocheck to open or close a constraint. For example, the following statement will disable the ch_zip_code constraint created above:

Alter table tb_check_constraint Nocheck constraint ch_zip_code

If you want to use the editor to create a constraint relationship, right-click the table that contains the constraint in the database relationship diagram and select the constraint command from the shortcut menu. You can also open the table designer that contains constraints, right-click the table designer, and select the "constraint" command.

Unique constraints

This constraint is applied to non-primary key columns in the table. The unique constraint ensures the integrity of questions in one or more columns and ensures that duplicate values are not entered. For example, the username column in the table is the primary key, but it also includes the ID card number column. Because all ID card numbers cannot be duplicated, you can create a unique constraint on this column to ensure that duplicate ID card numbers are not entered.

Unlike the primary key constraint, the unique constraint can be created on multiple columns, while the primary key constraint can have only one table.

To create a unique constraint, you can use the following method:

1.	In the database relationship diagram, right-click the table that contains the constraints and select the "attribute" command from the shortcut menu.
	-Or-	Open the table designer for a table that contains constraints, right-click the table designer, and select the "attribute" command from the shortcut menu.
2.	Select the index/key tab.
3.	Select the "new" command. The name assigned by the system appears in the index Name box.
4.	Expand the column list under "column name" and select the column to which the constraint is attached. To attach constraints to multiple columns, select other columns in subsequent rows.
5.	Select the create unique check box.
6.	Select the constraint option.

When a table or graph is saved, the unique constraint is created in the database.

To delete a unique index, follow these steps:

1.	In the database graph, right-click the table that contains the constraint column and select the "index/Key" command from the shortcut menu.
	-Or-	Open the table designer for a table that contains constraints, right-click the table designer, and select the "index/Key" command from the shortcut menu.
2.	Select a unique constraint from the "selected index" list.
3.	Select "delete.

Similarly, for a column's unique constraint, we call it a column-level unique constraint. For a multi-column unique constraint, we call it a table-level unique constraint. The following is the Definition Format of the column-level unique constraint:

[Constraint constraint_name] Unique [clustered | nonclustered] [With [fillfactor = fillfactor] [On {filegroup | default}]

When using the unique constraint, you also need to note that if you want to make Uniqueness for the powers that allow null values. You can allow columns with null values to be appended with the unique constraint, but you can only attach the primary key constraint to columns without null values. However, the unique constraint does not allow the table to be empty when more than one row of the restricted column is in the table.

For example, the following statement adds a unique constraint to the tb_unique_constraint table:

Alter table tb_unique_constraint Add Constraint un_phone unique (username, phone)

Foreign key constraint

The foreign key constraint provides data integrity references for one or more columns of data in a table. It is usually used together with the primary key constraint or unique constraint.

For example, the author_id column and the title_id column in the bookstores table refer to the author_id column in The authors table and the title_id column in the titles table respectively. When you Insert a new row or modify the data in the bookstores table, the data values of these two columns must already exist in the authors table and titles table. Otherwise, you cannot insert or modify the data.

When using the foreign key constraint, pay attention to the following points:

1.	A table can only reference up to 253 different data tables, and each table can have up to 253 foreign key constraints.
2.	The foreign key constraint cannot be applied to temporary tables.
3.	When implementing the foreign key constraint, you must have at least the select or references permission for the reference columns in the reference table.
4.	The foreign key constraint can also be referenced by other columns in the table.
5.	The foreign key constraint can only refer to a table in the database itself, but not the tables in other databases. Cross-database references can only be implemented through triggers.