SQL Constraints and Table modification statements

1. Binding effect:

The purpose of the constraint is to ensure the integrity of the data in the table

2. Common types of constraints are as follows

PRIMARY KEY constraint: (Primary key constraint) requires a primary key column to be unique and is not allowed to be empty
Unique constraint: (unique Constraint) requires the column to be unique, allowed to be empty, but only one null value can appear
Check constraints: (check Constraint) a column value range limits, formatting restrictions, and so on. If the age limit
Default constraint: (default Constraint) a column defaults, such as our male students more, gender default is male
FOREIGN KEY constraint: (Foreign key Constraint) is used to establish a relationship between two tables, you need to specify which column to reference the main table

3. Adding constraint statements

Alter Table Table Name
ADD Constraint constraint name constraint type specific constraint type
Example:
---Add a PRIMARY KEY constraint
Alter Tabletable_name
ADD ConstraintPk_table_name_fieldPrimary Key (Field)
---add a unique constraint
Alter Tabletable_name
ADD ConstraintUq_table_name_fieldUniqueField)
---add a DEFAULT constraint
Alter Tabletable_name
ADD ConstraintDf_table_name_fieldDefault (DefaultValue) forField
---add a check constraint
Alter Tabletable_name
ADD ConstraintCk_table_name_fieldCheckFieldBetweenStartvalueandEndvalue)
---adding FOREIGN key constraints
Alter Tabletable_name
ADD ConstraintFk_table_nameFOREIGN KEY (Field) References ParentTable (Field)

4. Delete a constraint

Alter Table Table Name
Drop Constraint Constraint name

5. Table modification Statements

Modify data table name ALTER TABLE old_table_name RENAME to new_table_name Modify datasheet

Modify data table ALTER TABLE table_name RENAME COLUMN old_column_name to new_column_name

Modifying the data type of a column alter TABLE table_name MODIFY column_name new_datatype

Insert Column ALTER TABLE table_name ADD column_name DATATYPE

Delete Column ALTER TABLE table_name DROP column column_name

Add description information for the table EXECUTE sp_addextendedproperty n ' ms_description ', 'Description', n ' user ', n ' dbo ', n ' t Able ', N 'table_name', NULL, NULL

Add descriptive information for the field EXECUTE sp_addextendedproperty n ' ms_description ', 'Description', n ' user ', n ' dbo ', N ' tab Le ', n 'table_name', n ' column ', n 'column_name'

Update the Description property of the column in the table EXEC sp_updateextendedproperty ' ms_description ', 'Description', ' user ', dbo, ' table ', 'table_ NAME', ' column ', N 'column_name'

SQL Constraints and Table modification statements