SQL Server data Table simple operation

-- Create a data table --
[Use the database name to create the data table
Go
Create table name to be created
(
Field name data type [length] [null | NOT NULL] [primary key],
... ... ... ... ,
Field name data type [length] [null | NOT NULL]
)

go
create Table Customer Information sheet
(
customer name nvarchar (5) NOT NULL,
contact phone nvarchar (one) not NULL,
address nvarchar (+) null,
mailbox nvarchar (+)
)

-- Modify datasheet --
[use database name to modify data table
go]
alter table name to be modified
alter column to modify field name [Modified data type [(length)] [null | NOT NULL]] --modify field
--Add new Field
[drop column field name] --Delete field

Cases:
Use Commodity Management database
Go
ALTER TABLE Customer Information table
ALTER COLUMN mailbox varchar () NULL
Go

-- Delete data table --
[Use the database name to delete the data table
Go
drop table name [, table name,...] --Delete the data table and delete all data in the data table, as well as the association and constraints of the table with other tables in the database

-- constraint settings for data table --
-- Set PRIMARY KEY constraint (PK) --
[use The database name to set the primary key of the Data table field
go]
alter table data table name
add constraint constraint name --add constraint
primary Key (field name [, field name ...]) --set the primary key field name

Cases:
Use Commodity Management database
Go
ALTER TABLE Customer Information table
Add constraint pk_ Customer Information Form _ Customer ID
Primary key (customer number)

-- Set DEFAULT constraint (DF)--
ALTER TABLE data table name
Add constraint constraint name
Default value for field name

go
alter table Customer Information Form
add constraint df_ Customer Information table address
Span style= "font-family: ' Microsoft Yahei ';" >default ' Liaoning Shenyang ' for address

-- set UNIQUE constraint (UN) --
[use the name of the database to set the primary key of the Data table field
go]
alter table data table name
add constraint constraint name --add constraint
unique [Clustered | nonclustered] (field name [, field name ...]) --clustered | Nonclustere represents either clustered or nonclustered

go
alter Table Customer Information sheet
add constraint Un_ Customer Information table _ Mailbox
Span style= "font-family: ' Microsoft Yahei ';" >unique Clustered (e-mail)

-- set CHECK constraint (CK) --
[use the name of the database to set the primary key of the Data table field
go]
alter table data table name
add constraint constraint name --add constraint
check (constraint expression)

go
alter Table Customer Information sheet
add constraint Ck_ Customer Information table _ Mailbox
Span style= "font-family: ' Microsoft Yahei ';" >check (mailbox like ' _%@_%._% ')

-- set FOREIGN KEY constraint (FK) --
[use the name of the database to set the primary key of the Data table field
go]
alter table data table name
add constraint constraint name --add constraint
foreign key (field name) --foreign key specifies that the constraint be added as a foreign key constraint
references Main Table name (field name)

go
alter Table Commodity Information sheet
add constraint fk_ Commodity Type table _ Commodity Information Table _ Product type number
foreign key (item type number)

-- View constraints --
exec sp_help constraint name --Show name (name), owner, type (type), Create_datetime (time), general default constraints and CHECK constraints
exec sp_helptext constraint name --show constraint expressions, typically default constraints and CHECK constraints

Cases:
EXEC sp_help Ck_ Customer Information Form _ Mailbox
EXEC sp_helptext ck_ Customer Information Form _ Mailbox

-- Delete constraint --
ALTER TABLE name of the data table to delete the constraint
drop constraint constraint name [, constraint name, ...] --The name of the constraint to delete

Cases:
ALTER TABLE commodity Information sheet
Drop constraint Df_ Product information table _ Address

Note: "--" for the comment text

SQL Server data Table simple operation