When you create a table and you realize that you made a mistake, or
the requirements of the application changed, then you can drop the
table and create it again. But this is not a convenient option if
the table is already filled with data, or if the table is
referenced by other database objects (for instance a foreign key
constraint). Therefore EnterpriseDB
provides a family of commands to make modifications on existing
tables.
You can
Add columns,
Remove columns,
Add constraints,
Remove constraints,
Change default values,
Rename columns,
Rename tables.
All these actions are performed using the ALTER
TABLE command.
For example to add a column for storing the date of birth of each employee in the
emp table we would use the following command:
ALTER TABLE emp ADD birthdate DATE;
The new column will initially be filled with null values in the
existing rows of the table.
You can also define a constraint on the column at the same time,
using the usual syntax:
ALTER TABLE emp ADD birthdate DATE CHECK (birthdate > '01-JAN-1960');
A new column cannot have a not-null constraint since the column
initially has to contain null values. But you can add a not-null
constraint later. Also, you cannot define a default value on a
new column. According to the SQL standard, this would have to
fill the new columns in the existing rows with the default value,
which is not implemented yet. But you can adjust the column
default later on.
To remove a column, use this command:
ALTER TABLE emp DROP COLUMN birthdate;
To add a constraint, the table constraint syntax is used. For example:
ALTER TABLE emp ADD CHECK (ename <> '');
ALTER TABLE emp ADD CONSTRAINT unique_empno UNIQUE(empno);
ALTER TABLE emp ADD CONSTRAINT fk_deptno
FOREIGN KEY (deptno) REFERENCES dept(deptno);
To add a not-null constraint, which cannot be written as a table
constraint, use this syntax:
ALTER TABLE emp ALTER COLUMN empno SET NOT NULL;
The constraint will be checked immediately, so the table data must
satisfy the constraint before it can be added.
To remove a constraint you need to know its name. If you gave it
a name then that's easy. Otherwise the system assigned a
generated name, which you need to find out. The
EnterpriseDB PSQL describe command
with the tablename can be helpful over here;
other interfaces might also provide a way to inspect table
details. For example, we wish to remove the unique
empno constraint from the emp table we would use the following command:
ALTER TABLE emp DROP CONSTRAINT unique_empno;
To remove a constraint you need to know its name. If you gave it a name then that's easy.
Otherwise the system assigned a generated name, which you need to find out.
The EnterpriseDB PSQL command \d tablename can be helpful here; other interfaces might also
provide a way to inspect table details. Then the command is:
ALTER TABLE products DROP CONSTRAINT some_name;
This works the same for all constraint types except not-null constraints. To drop a not null constraint use:
ALTER TABLE products ALTER COLUMN product_no DROP NOT NULL;
(Recall that not-null constraints do not have names.)
To set a new default for a column, use a command like this:
ALTER TABLE emp ALTER COLUMN comm SET DEFAULT 0.00;
To remove any default value, use
ALTER TABLE emp ALTER COLUMN comm DROP DEFAULT;
It is not an error
to drop a default where one hadn't been defined, because the
default is implicitly the null value.
To rename a column:
ALTER TABLE emp RENAME COLUMN empno TO employee_number;
To rename a table:
ALTER TABLE emp RENAME TO employees;