ALTER TABLE Examples
1. Add a column to an existing table:
ALTER TABLE emp_const ADD COLUMN location CHAR(10);
2. Drop a column from an existing table:
ALTER TABLE emp_const DROP COLUMN location RESTRICT;
3. Add a table-level constraint
ALTER TABLE t1 ADD CONSTRAINT pkey PRIMARY KEY(col1);
4. Drop a table-level constraint:
ALTER TABLE t2 DROP CONSTRAINT pkey RESTRICT;
5. Given the following two tables:
CREATE TABLE dept (
name CHAR(10) NOT NULL,
location CHAR(20),
CONSTRAINT dept_unique UNIQUE(name);
CREATE TABLE emp_const (
name CHAR(10) NOT NULL,
salary DECIMAL(10,2),
dept CHAR(10)
CONSTRAINT empref REFERENCES dept(name));
The following statement returns an error because there is a referential constraint that depends on dept_unique.
ALTER TABLE dept DROP CONSTRAINT dept_unique RESTRICT;
Dropping the empref constraint from table emp_const will work:
ALTER TABLE emp_const DROP CONSTRAINT empref RESTRICT;
6. Add a column to an existing table. Horizontally partition the column across two locations:
ALTER TABLE emp_const ADD COLUMN department CHAR(12) NOT NULL WITH DEFAULT
SET LOCATION=(data1, data2);
Note: If NOT NULL is specified the WITH DEFAULT is mandatory but no value can be specified.
7. Rename a table:
ALTER TABLE tbl RENAME TO newtbl
RENAME TABLE tbl2 TO newtbl2
8. Rename a table column:
ALTER TABLE newtbl RENAME COLUMN col1 TO col001