SQL ALTER TABLE

SQL > ALTER TABLE

Once a table is created in the database, there are many occasions where one may wish to change the structure of the table. In general, the SQL syntax for ALTER TABLE is,

ALTER TABLE "table_name"
[alter specification];

[alter specification] is dependent on the type of alteration we wish to perform. We list a number of common changes below:

• Add Column


• Modify Column


• Rename Column


• Drop Column


• Add Index


• Drop Index


• Add Constraint


• Drop Constraint

For all cases, examples are provided for MySQL, Oracle, and SQL Server.

SQL > SQL ALTER TABLE > Add Column SyntaxSometimes we wish to add a column to a table. This can be achieved in SQL. To do this, we specify that we want to change the table structure via the ALTER TABLE command, followed by the ADD command to tell the RDBMS that we want to add a column. The SQL syntax for ALTER TABLE Add Column is, ALTER TABLE "table_name" ADD "column_name" "Data Type"; Let's look at the example. Assuming our starting point is the Customer table created in theCREATE TABLE section: Table Customer Column Name Data Type First_Name char(50) Last_Name char(50) Address char(50) City char(50) Country char(25) Birth_Date datetime Our goal is to add a column called "Gender". To do this, we key in: MySQL: ALTER TABLE Customer ADD Gender char(1); Oracle: ALTER TABLE Customer ADD Gender char(1); SQL Server: ALTER TABLE Customer ADD Gender char(1); The resulting table structure is: Table Customer Column Name Data Type First_Name char(50) Last_Name char(50) Address char(50) City char(50) Country char(25) Birth_Date datetime Gender char(1) Note that the new column Gender becomes the last column in the Customer table. It is also possible to add multiple columns. For example, if we want to add a column called "Email" and another column called "Telephone", we will type the following: MySQL: ALTER TABLE Customer ADD (Email char(30), Telephone char(20) ); Oracle: ALTER TABLE Customer ADD (Email char(30), Telephone char(20) ); SQL Server: ALTER TABLE Customer ADD (Email char(30), Telephone char(20) ); The table now becomes: Table Customer Column Name Data Type First_Name char(50) Last_Name char(50) Address char(50) City char(50) Country char(25) Birth_Date datetime Gender char(1) Email char(30) Telephone char(20) SQL > SQL ALTER TABLE > Modify Column SyntaxSometimes we need to change the data type of a column. To do this, we use the ALTER TABLE Modify Column command. For Oracle and MySQL, the SQL syntax for ALTER TABLE Modify Column is, ALTER TABLE "table_name" MODIFY "column_name" "New Data Type"; For SQL Server, the syntax is, ALTER TABLE "table_name" ALTER COLUMN "column_name" "New Data Type"; Let's look at the example. Assuming our starting point is the Customer table created in theCREATE TABLE section: Table Customer Column Name Data Type First_Name char(50) Last_Name char(50) Address char(50) City char(50) Country char(25) Birth_Date datetime Our goal is to alter the data type of the "Address" column to char(100). To do this, we key in: MySQL: ALTER TABLE Customer MODIFY Address char(100); Oracle: ALTER TABLE Customer MODIFY Address char(100); SQL Server: ALTER TABLE Customer ALTER COLUMN Address char(100); Resulting table structure: Table Customer Column Name Data Type First_Name char(50) Last_Name char(50) Address char(100) City char(50) Country char(25) Birth_Date datetime SQL > ALTER TABLE > Rename Column SyntaxSometimes we want to change the name of a column. To do this in SQL, we specify that we want to change the structure of the table using the ALTER TABLE command, followed by a command that tells the relational database that we want to rename the column. The exact syntax for each database is as follows: In MySQL, the SQL syntax for ALTER TABLE Rename Column is, ALTER TABLE "table_name" Change "column 1" "column 2" ["Data Type"]; In Oracle, the syntax is, ALTER TABLE "table_name" RENAME COLUMN "column 1" TO "column 2"; Let's look at the example. Assuming our starting point is the Customer table created in theCREATE TABLE section: Table Customer Column Name Data Type First_Name char(50) Last_Name char(50) Address char(50) City char(50) Country char(25) Birth_Date datetime To rename "Address" to "Addr", we key in, MySQL: ALTER TABLE Customer CHANGE Address Addr char(50); Oracle: ALTER TABLE Customer RENAME COLUMN Address TO Addr; SQL Server: It is not possible to rename a column using the ALTER TABLE statement in SQL Server. Use sp_rename instead. The resulting table structure is: Table Customer Column Name Data Type First_Name char(50) Last_Name char(50) Addr char(50) City char(50) Country char(25) Birth_Date datetime SQL > ALTER TABLE > Drop Column SyntaxSometimes we will wish to delete a column from an existing table in SQL. To do this, we specify that we want to change the table structure via the ALTER TABLE command, followed by a specification indicating that we want to remove a column. The detailed syntax for each database is as follow: In MySQL, the syntax for ALTER TABLE Drop Column is, ALTER TABLE "table_name" DROP "column_name"; In Oracle and SQL Server, the syntax for ALTER TABLE Drop Column is, ALTER TABLE "table_name" DROP COLUMN "column_name"; Let's look at the example. Assuming our starting point is the Customer table created in theCREATE TABLE section: Table Customer Column Name Data Type First_Name char(50) Last_Name char(50) Address char(50) City char(50) Country char(25) Birth_Date datetime Our goal is to drop the "Birth_Date" column. To do this, we key in: MySQL: ALTER TABLE Customer DROP Birth_Date; SQL Server: ALTER TABLE Customer DROP COLUMN Birth_Date; Oracle: ALTER TABLE Customer DROP COLUMN Birth_Date; The resulting table structure is: Table Customer Column Name Data Type First_Name char(50) Last_Name char(50) Address char(50) City char(50) Country char(25) SQL > ALTER TABLE > Add Index SyntaxSometimes after a table has been created in a database, we find that it is advantageous to add an index to that table to speed up queries involving this table (to understand what an index is and how it can help speed up SQL queries, please see the INDEX section). To do this in SQL, we specify that we want to change the table structure via the ALTER TABLE command, followed by the ADD INDEX command to tell the RDBMS that we want to add an index. The syntax for adding an index to an existing table is, ALTER TABLE "table_name" ADD INDEX "index_name" (column_name); Let's look at the example. Assuming our starting point is the Customer table created in theCREATE TABLE section: Table Customer Column Name Data Type First_Name char(50) Last_Name char(50) Address char(50) City char(50) Country char(25) Birth_Date datetime Assume we want to add an index on the "Country" column. To do this, we type in the following: ALTER TABLE Customer ADD INDEX IDX_COUNTRY (Country); Please note that using ALTER TABLE to add an index is supported in MySQL but not in Oracle or SQL Server. SQL > ALTER TABLE > Drop Index Syntax Sometimes we may decide that a particular index is no longer needed for a table. In those cases, that index should be removed to free up storage. To drop an index in SQL, we specify that we want to change the table structure via the ALTER TABLE command, followed by theDROP INDEX command. The SQL syntax to drop an index from a table is, ALTER TABLE "table_name" DROP INDEX "index_name"; Let's look at the example. Assuming our starting point is the Customer table created in theCREATE TABLE section: Table Customer Column Name Data Type First_Name char(50) Last_Name char(50) Address char(50) City char(50) Country char(25) Birth_Date datetime Assume we want to drop the index created in the ALTER TABLE ADD INDEX section. To do this, we type in the following: ALTER TABLE Customer DROP INDEX IDX_COUNTRY; Please note that using ALTER TABLE to drop an index in supported in MySQL but not in Oracle or SQL Server. SQL > ALTER TABLE > Add Constraint Syntax Sometimes we may decide to add a new constraint to an existing table (to see what are the different types of constraints that can be placed on a database table, please refer to theCONSTRAINT section). The syntax for adding a constraint in SQL is, ALTER TABLE "table_name" ADD [CONSTRAINT_NAME] [CONSTRAINT_TYPE] [CONSTRAINT_CONDITION]; Let's look at the example. Assuming our starting point is the Customer table created in theCREATE TABLE section: Table Customer Column Name Data Type First_Name char(50) Last_Name char(50) Address char(50) City char(50) Country char(25) Birth_Date datetime Assume we want to add a UNIQUE constraint to the "Address" column. To do this, we type in the following: MySQL: ALTER TABLE Customer ADD CONSTRAINT Con_First UNIQUE (Address); Oracle: ALTER TABLE Customer ADD CONSTRAINT Con_First UNIQUE (Address); SQL Server: ALTER TABLE Customer ADD CONSTRAINT Con_First UNIQUE (Address); where Con_First is the name of the constraint. SQL > ALTER TABLE > Drop Constraint Syntax Constraints can be placed on a table to limit the type of data that can go into a table. Since we can specify constraints on a table, there needs to be a way to remove this constraint as well. In SQL, this is done via the ALTER TABLE statement. The SQL syntax to remove a constraint from a table is, ALTER TABLE "table_name" DROP [CONSTRAINT|INDEX] "CONSTRAINT_NAME"; Let's look at the example. Assuming our starting point is the Customer table created in theCREATE TABLE section: Table Customer Column Name Data Type First_Name char(50) Last_Name char(50) Address char(50) City char(50) Country char(25) Birth_Date datetime Assume we want to drop the UNIQUE constraint on the "Address" column, and the name of the constraint is "Con_First." To do this, we type in the following: MySQL: ALTER TABLE Customer DROP INDEX Con_First; Note that MySQL uses DROP INDEX for index-type constraints such as UNIQUE. Oracle: ALTER TABLE Customer DROP CONSTRAINT Con_First; SQL Server: ALTER TABLE Customer DROP CONSTRAINT Con_First;