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
14. Add the MASKED attribute to the salary column of the employee table:
ALTER TABLE employee ALTER COLUMN salary FLOAT MASKED AS 0;
15. Add a sampled min-max index on one column of an X100 table:
ALTER TABLE sales_fact ADD MINMAX value WITH MINMAX_SAMPLES;
Last modified date: 04/26/2024