Shulou(Shulou.com)05/31 Report--

This article will explain in detail what constraints there are in SQL server. The editor thinks it is very practical, so I share it with you as a reference. I hope you can get something after reading this article.

The concept of constraints: ensure that you enter valid values in the column and maintain relationships between tables.

Primary key constraint

Function: primary key (primary key constraint), there can be only one table, no null values, no duplicate values.

Define constraints when creating a table: field name data type [length] primary key

Unique constraint

Function: unique (unique constraint), which specifies that two rows in the same column cannot have the same value, only one behavior null value.

Define constraints when creating a table: field name data type unique

Default constraint

Function: automatically insert the default value into the column where the constraint is located when executing the insert statement

Define constraints when creating a table: field name data type default default

Considerations for Default constraints

1. Only one default constraint can be defined per column.

2. Cannot be used for columns with identity attribute

3. If the default value defined is longer than the allowable length of its corresponding field, the default value entered into the table will be truncated.

4. For fields with default constraints, you can insert other values. If you do not insert them, the default values will be recorded, and the default constraints created later will match the

The existing data have no effect.

Check constraint

Function: check (check constraint), which verifies the validity of the data each time the Insert/update statement is executed.

Define constraints when creating a table: field name data type check (condition)

Considerations for Check constraints

1. Multiple check constraints can be defined in a table, but only one check constraint can be defined in each field.

2. When executing insert statements or update statements, check constraints validate the data.

3. If you add constraints to a table that already has data, you can use with nocheck to avoid checking the previous data.

Foreign key constraint

Function: ensure the relationship between the primary key (in the main table) and the external key (in the secondary table).

Define constraints when creating a table: field name data type foreign key (field name) references table name (field name)

Note: the number of columns and data types specified in the foreign Key clause must match the number of columns and data types in the references clause. And associated

The fields of the table must be set to the primary key.

Modify constraint

Add constraints when you modify a table

Alter table table name add constraint constraint name primary key (field name)-primary key add constraint constraint name unique (field name)-unique add constraint constraint name default (default) for field name-default add constraint constraint name check (condition)-check add constraint constraint name foreign key (field name) references primary key table (reference field name)-foreign key

Delete constraint

Syntax:

Alter table table name drop constraint name

[example] delete the pk_xs constraint of the xs table.

Alter table xs drop constraint pk_xs

Note:

1. To delete a constrained column, you must first delete the constraint

two。 When there is a foreign key constraint, to delete the primary key, you must first delete the corresponding foreign key

This is the end of this article on "what are the constraints in SQL server". I hope the above content can be helpful to you, so that you can learn more knowledge. if you think the article is good, please share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.