Structured Query Language/Alter Table

The ALTER TABLE command modifies column definitions and table constraints 'on the fly'. This means existing definitions are extended, changed, or deleted, or existing data is cast to a different type, or existing data is evaluated against the new definitions.

-- change column definitions
ALTER TABLE <table_name> { ADD | ALTER } [ COLUMN ] <column_name> <column_definition>;
ALTER TABLE <table_name> { DROP        } [ COLUMN ] <column_name>;

-- change table constraints
ALTER TABLE <table_name> { ADD | ALTER } CONSTRAINT <constraint_name> <constraint_definition>;
ALTER TABLE <table_name> { DROP        } CONSTRAINT <constraint_name>;

The following examples are based on the test table t1.

CREATE TABLE t1 (
  id         NUMERIC  PRIMARY KEY,
  col_1      CHAR(4)
);

Columns

The syntax of the ADD COLUMN and ALTER COLUMN phrases are similar to the one shown in the create table page.

Add a Column

Existing tables can be extended by additional columns with the ADD COLUMN phrase. Within this phrase, all options of the original Create Table statement are available: data type, default value, NOT NULL, Primary Key, Unique, Foreign Key, Check.

-- add a new column with any characteristic
ALTER TABLE t1 ADD COLUMN col_2 VARCHAR(100) CHECK (length(col_2) > 5);  -- Oracle: The key word 'COLUMN' is not allowed.

Alter the Characteristic of a Column

With the ALTER COLUMN phrase some characteristics of an existing column can be changed

• data type
• DEFAULT clause
• NOT NULL clause.

The new definitions must be compatible with the old existing data. If you change, for example, the data type from VARCHAR to NUMERIC, this action can only be successful if it is possible to cast all existing VARCHAR data to NUMERIC - the casting of 'xyz' will fail. Casting in the direction from NUMERIC to VARCHAR will be successful as long as the width of the VARCHAR is large enough to store the result.

Hint: Concerning the change of the characteristics of columns, some implementations ignore the syntax of the SQL standard and use other keywords like 'MODIFY'.

Change the Data Type

ALTER TABLE t1 ALTER COLUMN col_1 SET DATA TYPE NUMERIC;

Change the DEFAULT Clause

ALTER TABLE t1 ALTER COLUMN col_1 SET DEFAULT 'n/a';

Change the NOT NULL Clause

ALTER TABLE t1 ALTER COLUMN col_1 SET  NOT NULL;
ALTER TABLE t1 ALTER COLUMN col_1 DROP NOT NULL;

Drop a Column

Columns can be dropped from existing tables.

ALTER TABLE t1 DROP COLUMN col_2;    -- Oracle: The key word 'COLUMN' is mandatory.

Hint: As an extension to the SQL standard, some implementations offer a RENAME or SET INVISIBLE command.

Table Constraints

Table constraints can be added, modified, or dropped. The syntax is similar to that shown on the create table page.

Add a Table Constraint

ALTER TABLE t1 ADD CONSTRAINT t1_col_1_unique UNIQUE (col_1);

Alter a Table Constraint

ALTER TABLE t1 ALTER CONSTRAINT t1_col_1_unique UNIQUE (col_1);

Hint: Concerning the change of table constraints, some implementations ignore the syntax of the SQL standard and use other keywords like 'MODIFY'.

Drop a Table Constraint

ALTER TABLE t1 DROP CONSTRAINT t1_col_1_unique;  -- MySQL: Not supported. Use 'DROP FOREIGN KEY' or 'DROP INDEX' instead.

Hint: As an extension to the SQL standard, some implementations offer an ENABLE / DISABLE command for constraints.

Exercises

Add a column 'col_3' to the table 't1': numeric, not null.

Click to see solution

ALTER TABLE t1 ADD COLUMN col_3 NUMERIC NOT NULL;

Add a Foreign Key from table 't1' column 'col_3' to table 'person' column 'id'.

Click to see solution

ALTER TABLE t1 ADD CONSTRAINT t1_col_3_fk FOREIGN KEY (col_3) REFERENCES person (id);