Some commonly used alter statements in SQL Server

I recently studied sqlserver database .. I have not been familiar with it for a long time. I often encounter some alter statements, and I have learned some about it in my spare time. the following example uses the table structure createdatabaseText_DBgouseText_DBgocreatetableTeacher (

I recently studied sqlserver database .. I have not been familiar with it for a long time. I often encounter some alter statements, and I have learned some about it in my spare time. the following example uses the table structure create database Text_DB go use Text_DB go create table Teacher (

I have been learning SQL server databases recently.SomeAlterStatementI learned about it myselfSome.

About alter'sSomeUsage and syntax

The table structure is used in the following example.

CreatedatabaseText_DB

Go

UseText_DB

Go

CreatetableTeacher

(

TeaIdintnotnull,

TeaNamevarchar (20) notnull,

TeaAgeint,

TeaAddressvarchar (50)

) -- In order to see the effect more intuitively, no constraints are added here.

1. Alter is mainly used to add and modify deletion constraints for created tables.

1. Add constraints to the created tables

There are two methods: one is to create a constraint and define a name for the created constraint so that the operation can be performed again, such

Another method is to directly create constraints without naming them, but it is inconvenient to perform another operation.

First:

Altertable table name addconstraint constraint name constraint type (column name)

Example:

----- Add constraint (name )-----------

AltertableTeacheraddconstraintPK_1primarykey (TeaId) -- primary key constraint

AltertableTeacheraddconstraintUN_1unique (TeaName) -- unique constraint

AltertableTeacheraddconstraintCK_1check (TeaAge> 0) -- range Constraint

AltertableTeacheraddconstraintDE_1default '000000' forTeaAddress -- default Constraint

-- Foreign key constraints. The syntax is as follows:

Altertable table name addconstraint constraint name foreignkey (associated field) references main table (associated field)

Second:

Altertable table name add constraint (column name)

For example, add the primary key unique and range constraints to the Teacher table respectively.

AltertableTeacheraddprimarykey (TeaId) -- primary key

AltertableTeacheraddunique (TeaName) -- unique

AltertableTeacheraddcheck (TeaAge> 0) -- range Constraint

AltertableTeacheradddefault '000000' forTeaAddress -- default Constraint

Altertable table name addforeignkey (column name) references main table (column name) -- foreign key

2. Delete constraints: syntax

Altertable table name dropconstraint constraint name -- delete Constraint

Example: (delete the constraints in the Teacher table)

AltertableTeacherdropconstraintPK_1 -- delete the primary key constraint

AltertableTeacherdropconstraintUN_1 -- delete a unique constraint

AltertableTeacherdropconstraintCK_1 -- delete a range Constraint

AltertableTeacherdropconstraintDE_1 -- delete the default Constraint

Ii. Alter table: Use Alter table to modify the table structure, such as adding and deleting columns...

1. Add column syntax

Altertable table name ADD column Name Data Type

Example: (add a column of notes data type to the Teacher table as varchar (200 ))

AltertableTeacheraddnotesvarchar (200)

2. Delete the column syntax in the table

Altertable table name dropcolumn column name

Example: (delete the notes column in the table)

AltertableTeacherdropcolumnnotes

3. Modify the Data Type syntax in a column

Altertable table name altercolumn Name Data Type

Example: (change the notes column in the Teacher table to the int type)

AltertableTeacheraltercolumnnotesint