Q
Problem solve Get help with specific problems with your technologies, process and projects.

The ALTER TABLE statement

How do I add a column to an existing table? What is the SQL syntax for that?

How will I add a column to an existing table, for example if I have one table name DEPARTMENT and I want to add...

one column name DEPT_LOCATION to it, what is the SQL syntax rule for that?

This requires the ALTER TABLE statement.

alter 
 table department
   add column dept_location 
              integer not null default 0

The ALTER TABLE statement syntax may vary slightly from one database system to another. Check your specific product manual.

One interesting point about adding a column is that it cannot be declared NOT NULL unless it also has a DEFAULT value. During the execution of the ALTER statement, every row will have to be updated to add the new column. Thus, a value needs to be assigned by the ALTER statement.

(If NULL is specified, a DEFAULT may or may not be allowed by your particular database system. If NULL is specified and DEFAULT is specified, it will be a little trickier to insert a NULL. Note that this nuance is a rarity; DEFAULT doesn't make much sense for a NULL column.)

So if we add a new NOT NULL column with a DEFAULT value, the default will be applied to all existing rows. But we can then ALTER the table again, to change the column to NULL, with no DEFAULT, which means that NULLs are now valid. Existing rows would be left alone. Again, the specific ALTER TABLE syntax to change an existing column will vary from one database system to another. Check your specific product manual.

This was last published in May 2007

Dig Deeper on Oracle and SQL

PRO+

Content

Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close