SQL Server constraints

The concept of constraints: Ensure that valid values are entered in the columns and maintain relationships between the tables.

Primary key Constraint

Function: Primary KEY (PRIMARY KEY constraint), one table can only have one, cannot have null value, cannot have duplicate value.

Defining constraints when creating a table: Field name data type [length] primary key

Unique constraint
Features: Unique (UNIQUE constraint), specifies that two rows in the same column cannot have the same value, and that only one row is null.

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

Default constraint

Function: Automatically inserts the default value into the column where the constraint is inserted when an INSERT statement is executed

Defining constraints when creating tables: Field name data type default defaults
Default Constraint Considerations
1. You can define only one default constraint per column.
2. Cannot be used for columns with an 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, the field with the default constraint, you can insert other values, if not inserted, the default value is a record value, and the default constraint created after the
There is no effect on the existing data.

Check Constraint
Functions: Check (checking constraints), which verifies the legitimacy of the data each time the Insert/update statement is executed.

Defining constraints when creating a table: Field name data type check (conditional)

Check Constraint considerations
1. You can define multiple check constraints in one table, but only one check constraint per field.
2. When an INSERT statement or an UPDATE statement is executed, the CHECK constraint validates the data.
3. If you add a constraint to a table that already has data, you can use the WITH Nocheck to avoid checking for previous data.

Foreign key Constraint

Function: Ensure the relationship between the primary key (in the primary table) and the Foreign keyword (in the secondary table).
Defining 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 field of the table must be set as the primary key.

modifying constraints
To add a constraint when modifying 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 (defaults) 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
Grammar:
ALTER TABLE name DROP constraint name

"Example" Removes the PK_XS constraint for the XS table.
ALTER TABLE XS DROP constraint Pk_xs

Attention:
1. To delete a constrained column, you must first delete the constraint
2. When a foreign key constraint exists, the corresponding foreign key must be deleted before deleting the master

SQL Server constraints