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

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

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. There is only a 'DROP FOREIGN KEY'.

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



  This article uses material from the Wikipedia page available here. It is released under the Creative Commons Attribution-Share-Alike License 3.0.

Structured_Query_Language/Alter_Table
 



 

Connect with defaultLogic
What We've Done
Led Digital Marketing Efforts of Top 500 e-Retailers.
Worked with Top Brands at Leading Agencies.
Successfully Managed Over $50 million in Digital Ad Spend.
Developed Strategies and Processes that Enabled Brands to Grow During an Economic Downturn.
Taught Advanced Internet Marketing Strategies at the graduate level.


Manage research, learning and skills at defaultLogic. Create an account using LinkedIn or facebook to manage and organize your Digital Marketing and Technology knowledge. defaultLogic works like a shopping cart for information -- helping you to save, discuss and share.

Visit defaultLogic's partner sites below:
PopFlock.com : Music Genres | Musicians | Musical Instruments | Music Industry
NCR Works : Retail Banking | Restaurant Industry | Retail Industry | Hospitality Industry

  Contact Us