ALTER TABLE Examples
Add and remove a table-level constraint and a column from the existing base table. The examples are based on the following table:
CREATE TABLE emp (
name CHAR(10) NOT NULL NOT DEFAULT,
salary DECIMAL(10,2)
dept CHAR(10),
age INTEGER NOT NULL NOT DEFAULT);
1. Add a check constraint to ensure that employee ages are correct:
ALTER TABLE emp ADD CONSTRAINT check_age CHECK (age > 0);
2. Drop the age-checking constraint and any dependent constraints:
ALTER TABLE emp DROP CONSTRAINT check_age CASCADE;
3. Add a column to an existing table:
ALTER TABLE emp ADD COLUMN location CHAR(10);
4. Drop a column from an existing table:
ALTER TABLE emp DROP COLUMN location RESTRICT;
5. Change the size of a character column:
ALTER TABLE emp ALTER COLUMN name CHAR(32);
6. Change the column from a non-Unicode data type to a Unicode data type:
ALTER TABLE emp ALTER COLUMN name NCHAR(32);
7. Change from one character data type to another. For example, from char to varchar:
ALTER TABLE emp ALTER COLUMN name VARCHAR(32) NOT NULL WITH DEFAULT;
8. Change a column from not null to null:
ALTER TABLE emp ALTER COLUMN name CHAR(32) WITH NULL;
9. Change the collation sequence of a column:
ALTER TABLE emp ALTER COLUMN name NCHAR(32) NOT NULL NOT DEFAULT COLLATE UNICODE_CASE_INSENSITIVE;
10. Change the identity characteristics of column c2 from ALWAYS to BY DEFAULT:
ALTER TABLE t1 ALTER COLUMN c2 SET GENERATED BY DEFAULT
11. Remove the identity attributes of column d1:
ALTER TABLE t2 ALTER COLUMN d1 DROP IDENTITY
12. Rename a table:
ALTER TABLE tbl RENAME TO newtbl
13. Rename a table column:
ALTER TABLE tbl RENAME COLUMN col1 TO col001