Structured Query Language/Alter Table

From Wikibooks, open books for an open world
Jump to: navigation, search

← Foreign Key | Drop 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 casted 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[edit]

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

Add a Column[edit]

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[edit]

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 enought 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[edit]

ALTER TABLE t1 ALTER COLUMN col_1 SET DATA TYPE NUMERIC;

Change the DEFAULT Clause[edit]

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

Change the NOT NULL Clause[edit]

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

Drop a Column[edit]

Columns can be dropped from existing tables.

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

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

Table Constraints[edit]

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

Add a Table Constraint[edit]

ALTER TABLE t1 ADD CONSTRAINT t1_col_1_unique UNIQUE (col_1);

Alter a Table Constraint[edit]

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[edit]

ALTER TABLE t1 DROP CONSTRAINT t1_col_1_unique;  -- MySQL: Not supported. There is only a 'DROP FOREIGN KEY'.

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

Exercises[edit]

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);