Structured Query Language/Alter Table

__TOC__ 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. 100% 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'. 100% ALTER TABLE t1 ADD CONSTRAINT t1_col_3_fk FOREIGN KEY (col_3) REFERENCES person (id);