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 0The 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.
Oracle White Papers: Fusion Middleware