ALTER TABLE

version 11.3 (Modified)

ALTER TABLE sql_name

{ADD column_definition |

DROP sql_name |

ADD primary_key_definition |

DROP PRIMARY KEY |

ADD foreign_key_definition |

DROP CONSTRAINT sql_name |

SET SCHEMA sql_name}

The ALTER TABLE command is used to modify an existing table (sql_name). You can carry out one of the following actions:

Passing ADD column_definition adds a column to the table.

Passing DROP sql_name removes the column named sql_name from the table.

Passing ADD primary_key_definition adds a PRIMARY KEY to the table.

Passing DROP PRIMARY KEY removes the PRIMARY KEY of the table.

Passing ADD foreign_key_definition adds a FOREIGN KEY to the table.

Passing DROP CONSTRAINT sql_name removes the specified constraint from the table.

Passing SET SCHEMA sql_name transfers the table to the sql_name schema.

This example creates a table, inserts a set of values into it, then adds a Phone_Number column, adds another set of values and then removes the ID column:

   CREATE TABLE ACTOR_FANS
   (ID INT32, Name VARCHAR);
 
   INSERT INTO ACTOR_FANS
   (ID, Name)
   VALUES(1, 'Francis');

   ALTER TABLE ACTOR_FANS   
   ADD Phone_Number VARCHAR;

   INSERT INTO ACTOR_FANS
   (ID, Name, Phone_Number)
   VALUES (2, 'Florence', '01446677888');

   ALTER TABLE ACTOR_FANS
   DROP  ID;

column_definition, CREATE TABLE, DROP TABLE, foreign_key_definition, primary_key_definition.