SQL Server basics: Constraints

To reduce input errors and ensure database data integrity, you can set constraints on fields. For example, the range of exam scores should be 0-100. Constraints are a set of mechanisms to ensure data integrity. Constraints include primary key constraints, foreign key constraints, Unique constraints, Check constraints, default values, and allowed null. Primary key constraint: This is not required

To reduce input errors and ensure database data integrity, you can set constraints on fields. For example, the range of exam scores should be 0-100. Constraints are a set of mechanisms to ensure data integrity. Constraints include primary key constraints, foreign key constraints, Unique constraints, Check constraints, default values, and allowed null. Primary key constraint: This is not required

To reduce input errors and ensure database data integrity, you can set constraints on fields. For example, the range of exam scores should be 0-100. Constraints are a set of mechanisms implemented to ensure data integrity,Constraints include:Primary key constraint, foreign key constraint, Unique constraint, Check constraint, default value, and allowed null.

Primary key constraint: Needless to say, setting the primary key is to ensure that the unique values in the column are not repeated.

Foreign key constraint: The purpose of the foreign key is to limit the value.

For example: category Table category (Nid-primary key, categoryName), Product table Product (Nid, ProductName, CategoryId-foreign key ), the value of CategoryId in the Product table can only be the value of Nid in the category table.
　　　　　

For example, if you insert an entry in the Product table: insert into Product values ('greenmy', 9), an error is returned:

The INSERT statement conflicts with the foreign key constraint "FK_Product_Product. This conflict occurs in the Database "Interview", table "dbo. Category", column 'nid '.
The statement has been terminated.

Unique constraints: Indicates that the data in this field Column cannot be repeated. For example, if the Department name in the department table cannot be repeated, set the "department name" in the department table to the Unique constraint.
The setting method is as follows: Right-click the blank area in the Table Designer pane and select the index and key option in the pop-up shortcut menu, click Add to add an option named IX _ table name in the box on the left, and select type in the box on the right as the unique key, [column] is set to the column to be constrained. Close and save.
Difference between a Unique constraint and a primary key: A Unique constraint can enter a Null value, but a primary key cannot. In a table, multiple fields with a Unique constraint can be defined, and only one primary key field can be defined.

Check Constraints: Check constraints can be used to limit the value in a field within a certain allowed range. For example, the input range of the birthday in the employee Address Book table is between 1940 and 1990.
You can use the following method to set the Check constraint: Expand the table option, right-click the constraint to create a constraint, and add a logical relationship to the expression, for example, (Member> 10) and (Member <20 ), close and save!

Default Value Constraints: The default value does not need to be explained, that is, the default value filled when data is not explicitly inserted.
The setting method is as follows: Open the table design, select a column, and enter the default value or binding in the column attributes below;

Allow empty Constraint: Whether or not the last entry of each table can be empty during table design.