Modify the structure of a table - ALTER TABLE

Modify the structure of a table - ALTER TABLE

ALTER TABLE is the command used to add, delete or modify columns in an existing table. This command is also used to add and remove various constraints on an existing table.

ALTER TABLE - ADD

Add a column

The basic syntax of an ALTER TABLE command to add a new column to an existing table is as follows

Syntax :
                                ALTER TABLE table_name
                                    ADD (column1  data_type,
                                    column2  data_type,
                                    ...
                                    columnN  data_type);
                            
Example 1 :

To add a column "address" to the table employees we use the following query

                                ALTER TABLE Employees ADD Address Varchar(100);
                            
Add a constraint

ADD can also be used to create a constraint on table columns

Syntax :
                                ALTER TABLE table_name 
                                    ADD CONSTRAINT constraint_name constraint
                            
Example 1 :

The following query, will add a CHECK constraint to the Age column.

                                ALTER TABLE Employes
                                    ADD CONSTRAINT ageConstraint CHECK(Age >= 18);
                            

ALTER TABLE - DROP

Drop a column

DROP COLUMN is used to drop a column from a table. Remove unwanted columns from the table.

Syntax :
                                ALTER TABLE table_name
                                    DROP COLUMN columnName;
                            
Example 1 :

To remove the Age column from the Employees table, you can use the following query:

                                ALTER TABLE Employees
                                    DROP COLUMN Age;
                            
Remove a constraint

DROP CONSTRAINT can also be used to drop a constraint on table columns

Syntax :
                                ALTER TABLE table_name 
                                    DROP CONSTRAINT constraint_name;
                            
Example 1 :

To remove the ageConstraint constraint on the Age column of the Employees table, you can use the following query:

                                ALTER TABLE Employees
                                    DROP CONSTRAINT ageConstraint;
                            

ALTER TABLE - MODIFY

It is used to modify existing columns in a table. Several columns can also be modified at the same time.

Syntax :
                                ALTER TABLE table_name
                                    MODIFY columnName data_type;
                            
Example 1 :

Suppose we want to change the data type of the Age column from int to Date. You can do this using the following query:

                                ALTER TABLE Employes
                                    MODIFY Age Date;
                            
                                    +---------+---------------+------+-----+---------+-------+
                                    | Field   | Type          | Null | Key | Default | Extra |
                                    +---------+---------------+------+-----+---------+-------+
                                    | Id      | int(11)       | NO   | PRI | NULL    |       |
                                    | Nom     | varchar(20)   | NO   |     | NULL    |       |
                                    | Age     | Date          | NO   |     | NULL    |       |
                                    | Salaire | decimal(18,2) | YES  |     | NULL    |       |
                                    +---------+---------------+------+-----+---------+-------+
                            

Share this course with your friends :

This course is written by M. ESSADDOUKI Mostafa

Many people realize their hearts desires late in life. Continue learning, never stop striving and keep your curiosity sharp, and you will never become too old to appreciate life.

0 Comment(s)

To leave a comment you must have an account Sign up, or Sign in