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.
Dig Deeper on Oracle and SQL
Related Q&A from Rudy Limeback
Read an example of an SQL case expression from our SQL expert Rudy Limeback. Continue Reading
Read SQL expert Rudy Limeback's advice for counting combinations in a table with SQL's GROUP BY clause Continue Reading
Read about the Mimer Validator, a tool used to verify your SQL code, in this tip from SQL expert Rudy Limeback. Continue Reading
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.