Loading lesson path
Concept visual
Start at both ends
Overview
statement is used to add, delete, or modify columns in an existing table.
statement is also used to add and drop various constraints on an existing table.
operations are:
Formula
Add column - Adds a new column to a table
Drop column - Deletes a column in a table
Rename column - Renames a column
Modify column - Changes the data type, size, or constraints of a column
Add constraint - Adds a new constraint
Rename table - Renames a table
ALTER TABLE - ADD ColumnTo add a column in a table, use the following syntax:
table_name
column_name datatype
;
The following SQL adds an "Email" column to the "Customers" table:ADD Email varchar(255);Formula
ALTER TABLE - DROP COLUMNTo delete a column in a table, use the following syntax (notice that some database systems don't allow deleting a column):
table_name
column_name
;
The following SQL deletes the "Email" column from the "Customers" table:DROP COLUMN Email;Formula
ALTER TABLE - RENAME COLUMNTo rename a column in a table, use the following syntax:
table_name
old_name to new_name
;To rename a column in a table in SQL Server, use the following syntax:
EXEC sp_rename ' table_name.old_name ', ' new_name
', 'COLUMN';Formula
ALTER TABLE - MODIFY DatatypeTo modify the data type, size or constraints of a column in a table, use the following syntax:
Formula
Syntax for SQL Server / MS Access:table_name
column_name new_datatype constraint
;table_name
column_name new_datatype constraint
;
The following SQL modifies the size of the "Email" column to varchar(100), and we also add a NOT NULLconstraint: