SQL Alter Table

The Sql Alter Table command is used to alter a table.


After you worked with the database for a while, you may realized that the tables' structure are not quite right or maybe even the design requirements have changed.

The Sql Alter Table command may be used to add or drop columns, and also to change column's name, datatype, size and status. You may be able to change the tables even when they are not empty, check the RDBMS' manual to check if this is available.

This process must be planned carefully. If you change a table's columns, check the impacts to any programs or codes that use the columns. If there is data already, check the impact of truncation or datatype change.

Adding a column to a table

The Sql Alter Table syntax to add a column to a table:

ALTER TABLE table_name
   ADD new_field data_type [NULL]

When a new column is added to a table, it must contain some value, usually it is set to 'null' value by default. That is why you must allow null in the alter table command.

Supposed that you need to add 'country' to the authors table that we previously created. Type this command:

ALTER TABLE authors
   ADD country NVARCHAR(40)

That will add country to the authors table and assign 'null' value as its content.

Deleting a column in a table

The Sql Alter Table syntax to delete a column:

ALTER TABLE table_name
   DROP COLUMN field_name

Supposed that you want to delete 'country' field that we previously created. Type this command:

ALTER TABLE authors
   DROP COLUMN country 

That will delete country field from authors table.

Changing Primary Column

In some other case, maybe a bit rare, you might need to change the primary key. A primary key column cannot be null. But, as I stated before you must allow null value for the new added column, so how do you do this?

There are several ways to do this. My preferred way, I think this is the easiest, is to create a new table with the new structure, then use SQL Insert command to dump the data from the old table into the new table.

Quick link. From here, you can learn the following:

Sql Create Database
Use this to create a database.

Sql Create Table
This Sql Create Table will be used very often throughout the tutorial. It is used to create a table, in our case, mainly to create sample tables.

Sql Drop Table
Used to delete a table, not just the content. This command is also used very often during the tutorial. So, you need to master this too.

Data Retrieval
All you need to know about Sql Select can be found here.

Perseverance: Cliffhanger
Buy at AllPosters.com
What the mind can conceive and believe, it can achieve.

Positive Resources

All of us have at least two or three things that excite us. We could talk about these subjects for hours. Good news!. Did you know that Site Built It! can turn this type of deep interest and enthusiasm into a revenue-generating business?

Review some of the Site Build It! case studies and you'll absolutely discover something that ignites more positive ideas into your mind.

SBI! Video Tour


Invest the most
important 30 minutes
you will spend online.

Click, sit back, and
understand why SBI!
makes you succeed.

alter table dbo.account_number_history alter column account_number varchar(36) alter table dbo.account_number_history alter column account_open_date_from smalldatetime