SQL ALTER TABLE command

SQL ALTER TABLE command

The SQL ALTER TABLE command is used to add, remove, or change columns from an existing data table.

You can also use the ALTER TABLE command to add or remove constraints on existing data sheets.

Grammar:

The basic syntax for adding new columns to an existing data table using ALTER TABLE is as follows:

ALTER TABLE table_name ADD column_name datatype;

The basic syntax for removing columns from an existing data table using ALTER TABLE is as follows:

ALTER TABLE table_name DROP COLUMN column_name;

The basic syntax for using ALTER TABLE to change the data types of columns in an existing data table is as follows:

ALTER TABLE table_name MODIFY COLUMN column_name datatype;

The basic syntax for adding NOT NULL constraints to a column using ALTER TABLE is as follows:

ALTER TABLE table_name MODIFY column_name datatype NOT NULL;

The basic syntax for adding unique constraints to a data table using ALTER TABLE is as follows:

ALTER TABLE table_name 
ADD CONSTRAINT MyUniqueConstraint UNIQUE(column1, column2...);

The basic syntax for adding CHECK constraints to a data table using ALTER TABLE is as follows:

ALTER TABLE table_name 
ADD CONSTRAINT MyUniqueConstraint CHECK (CONDITION);

The basic syntax for adding primary key constraints to a data table using ALTER TABLE is as follows:

ALTER TABLE table_name 
ADD CONSTRAINT MyPrimaryKey PRIMARY KEY (column1, column2...);

The basic syntax for removing constraints from a data table using ALTER TABLE is as follows:

ALTER TABLE table_name 
DROP CONSTRAINT MyUniqueConstraint;

If you're using MySQL, the code should look like this:

ALTER TABLE table_name 
DROP INDEX MyUniqueConstraint;

The basic syntax for removing primary key constraints from the data table using ALTER TABLE is as follows:

ALTER TABLE table_name 
DROP CONSTRAINT MyPrimaryKey;

If you're using MySQL, the code should look like this:

ALTER TABLE table_name 
DROP PRIMARY KEY;

Example:

Consider the CUSTOMERS table, which is recorded as follows:

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

The following example shows how to add a new column to an existing table:

ALTER TABLE CUSTOMERS ADD SEX char(1);

Now that the CUSTOMERS has been changed, the output of the SELECT statement should look like this:

+----+---------+-----+-----------+----------+------+
| ID | NAME    | AGE | ADDRESS   | SALARY   | SEX  |
+----+---------+-----+-----------+----------+------+
|  1 | Ramesh  |  32 | Ahmedabad |  2000.00 | NULL |
|  2 | Ramesh  |  25 | Delhi     |  1500.00 | NULL |
|  3 | kaushik |  23 | Kota      |  2000.00 | NULL |
|  4 | kaushik |  25 | Mumbai    |  6500.00 | NULL |
|  5 | Hardik  |  27 | Bhopal    |  8500.00 | NULL |
|  6 | Komal   |  22 | MP        |  4500.00 | NULL |
|  7 | Muffy   |  24 | Indore    | 10000.00 | NULL |
+----+---------+-----+-----------+----------+------+

The following example shows how to remove the SEX column from the CUSTOMERS table:

ALTER TABLE CUSTOMERS DROP COLUMN SEX;

Now that the CUSTOMERS has been changed, the output of the SELECT statement should look like this:

+----+---------+-----+-----------+----------+
| ID | NAME    | AGE | ADDRESS   | SALARY   |
+----+---------+-----+-----------+----------+
|  1 | Ramesh  |  32 | Ahmedabad |  2000.00 |
|  2 | Ramesh  |  25 | Delhi     |  1500.00 |
|  3 | kaushik |  23 | Kota      |  2000.00 |
|  4 | kaushik |  25 | Mumbai    |  6500.00 |
|  5 | Hardik  |  27 | Bhopal    |  8500.00 |
|  6 | Komal   |  22 | MP        |  4500.00 |
|  7 | Muffy   |  24 | Indore    | 10000.00 |
+----+---------+-----+-----------+----------+