SQL server constraints

The concept of constraints: ensuring that valid values ​​are entered in columns and maintaining relationships between tables.

Primary key constraint

Function: primary key (primary key constraint), there can only be one in a table, There cannot be null values ​​or duplicate values.

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

Unique constraints
Function: unique (unique constraint), specifies that two rows in the same column cannot have the same value, and only one row can have a null value.

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

Default constraint

Function: When executing the insert statement, the default value is automatically inserted into the constraint location Column

Define constraints when creating a table: Field name Data type default Default value
Default Constraint notes
1. Only one default constraint can be defined for each column.
2. It cannot be used for columns with identity attributes.
3. If the defined default value is longer than the allowed length of its corresponding field, the default value entered into the table will be truncated.
4. For fields with default constraints, other values ​​can be inserted. If not inserted, the default value will be used as the record value, and the default constraints created later will have no impact on the existing data.

Check constraintFunction: check (check constraint), each time the Insert/update statement is executed, the constraint needs to verify the legality of the data.

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

Check constraint notes

1. Multiple check constraints can be defined in a table, but each field can only Ability to define a check constraint.
2. When executing the insert statement or update statement, the check constraint will verify the data.
3. If you add constraints to a table that already has data, you can use with nocheck to avoid checking previous data.

Foreign key constraints

Function: Ensure the relationship between the primary key (in the main table) and the foreign key (in the auxiliary 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 be the same as in the references clause The number of columns and data types match. And the fields of the associated
tables must be set as primary keys.

Modify constraintsAdd constraints when modifying the table

alter table 表名
add constraint 约束名 primary key(字段名) –-主键
add constraint 约束名 unique (字段名)    --唯一
add constraint 约束名 default(默认值) for 字段名 –默认
add constraint 约束名 check(条件) –检查
add constraint　约束名 foreign key(字段名) references 主键表(参照字段名)  --外键

Delete constraintsSyntax:

alter table 表名 drop 约束名

[Example] Delete the pk_xs constraint of the xs table.

alter table xs drop constraint  pk_xs

Note:

1. To delete the constrained column, you must first delete the constraint
2. When there is a foreign key constraint, to delete the primary key, you must first delete the corresponding foreign key. key

The above is the detailed content of SQL server constraints. For more information, please follow other related articles on the PHP Chinese website!