SQL ALTER TABLE Statement

The SQL ALTER TABLE statement is used to add new fields, modify fields, or delete fields in an existing database table.

Added a column with the same name to a table (run the alter script twice on purpose to test the rollback).

Example 1: I got an error stating that the column must be unique (since the column already added during the 1st execution of the script). Alternatively, I want the sql execution to show Failed and the transaction to be rolled back. The following is my code:

BEGIN TRANSACTION
 
ALTER TABLE dbo.tbl_name
ADD  [column1] [varchar] (20) NULL 
 
--Error handling
IF @@Error = 0  
    Begin
        COMMIT TRANSACTION
        print '-Success'   
    END
ELSE
    Begin
        ROLLBACK TRANSACTION
        print '-Failed'
    End
GO

The ALTER TABLE statement is an exception. When used to add or remove columns from a table, it may take longer. ALTER TABLE necessitates the rebuilding of every row in the table, making it a costly procedure. Even using Teradata's highly efficient, block-oriented technique, it can take hours to process a huge table.

When you see that an ALTER TABLE statement rollback is taking a long time but you can't stop it, the system is deferring a stop and will have to finish it.

To drop one or more columns from an existing table, use the ALTER TABLE DROP COLUMN statement.

A single ALTER statement can be used to drop numerous columns from a MySQL table. By separating the DROP keyword and the column name list with a comma, many columns can be eliminated from the MySQL table with a single ALTER command.

You may want to remove one or more unused columns from an existing table at times. You do it by using the ALTER TABLE command as follows:

ALTER TABLE table_name
DROP COLUMN column_name1,
[DROP COLUMN column_name2];

In this syntax:

• table_name is the name of the table which contains the columns that you are removing.
• column_name1, column_name2 are the columns that you are dropping.

Example 1: The following deletes the Address column of the Employee table.

ALTER TABLE dbo.Employee
DROP COLUMN Address;

The following deletes multiple columns of the Employee table.

ALTER TABLE dbo.Employee
DROP COLUMN Address, PhoneNo, Email;

Example 2: Let's begin by gathering the sample data. We'll make a table called sale_details and then insert rows into it. Finally, we'll use a single query to delete three columns from the sale_details table.

#create the table sale_details
CREATE TABLE sale_details (
    id INT ,
    sale_person_id VARCHAR(255) ,
    sale_person_name VARCHAR(255),
    no_products_sold INT,
    sales_department VARCHAR(255)
);

#insert data into table sale_details
INSERT INTO sale_details (id,sale_person_id,sale_person_name,no_products_sold,sales_department) 
 VALUES(1,"sd1","Henry",2000,"Kitchen Essentials"),
 (2,"sd1","Henry",5000,"Apparels"),
 (3,"sd1","Henry",40,"Medicines"),
 (4,"sd2","Richa",3000,"Kitchen Essentials"),
 (5,"sd2","Richa",500,"Apparels");

To view the snapshot of the table sale_details, we will execute:

SELECT * FROM sale_details;

We will now be deleting columns sale_person_id, no_products_sold, and sales_department from table sale_details. Observe the below query for the solution.

ALTER TABLE sale_details DROP sale_person_name,DROP no_products_sold, DROP sales_department;

To verify if the query dropped the columns along with data from table sale_details execute:

SELECT * FROM sale_details;

Example 3:

A) Dropping one column example

The following statement drops the email column from the persons table:

ALTER TABLE persons
DROP COLUMN email;

B) Dropping multiple columns example

The following statement drops the date_of_birth and phone columns:

ALTER TABLE persons
DROP COLUMN date_of_birth,
DROP COLUMN phone;

The ALTER TABLE command DROP CONSTRAINT removes the CHECK and FOREIGN KEY constraints from columns.

You may decide to remove a foreign key from the table after it has been created.

To remove an existing constraint with the name you specify, use the DROP CONSTRAINT clause.

Use the DROP CONSTRAINT keywords along with the constraint's identifier to remove an existing constraint. The constraint names must be separated by commas and divided by parentheses if you want to drop several constraints on the same table.

The mode of the limitation you drop can be ENABLED, DISABLED, or FILTERING.

Note: The DROP CONSTRAINT statement modifies the schema. See Online Schema Changes for additional information on how CockroachDB handles online schema modifications.

Syntax:

The syntax to drop a foreign key in SQL Server (Transact-SQL) is:

ALTER TABLE table_name
DROP CONSTRAINT fk_name;

Parameters or Arguments:-

• table_name: The name of the table where the foreign key has been created.
• fk_name: The name of the foreign key that you wish to remove.

Example 1: Let's look at an example of how to drop a foreign key in SQL Server (Transact-SQL).

For example, if you had created a foreign key as follows:

CREATE TABLE products
( product_id INT PRIMARY KEY,
  product_name VARCHAR(50) NOT NULL,
  category VARCHAR(25)
);

CREATE TABLE inventory
( inventory_id INT PRIMARY KEY,
  product_id INT NOT NULL,
  quantity INT,
  min_level INT,
  max_level INT,
  CONSTRAINT fk_inv_product_id
    FOREIGN KEY (product_id)
    REFERENCES products (product_id)
);

We've named our parent table the products table in this foreign key example. The product id field serves as the primary key for the products table.

Then, as the child table in this foreign key example, we established a second table called inventory. The Construct TABLE command was used to create a foreign key called fk_inv_product_id on the inventory table. The foreign key connects the inventory table's product_id column with the products table's product_id column.

If we wanted to remove the fk_inv_product_id foreign key, we might use the following command:

ALTER TABLE inventory
DROP CONSTRAINT fk_inv_product_id;

The ALTER TABLE statement is used in this foreign key example to remove the constraint fk_inv_product_id from the inventory table.

Example 2: Here is an example of dropping a constraint:

ALTER TABLE manufact DROP CONSTRAINT con_name; 

On the orders table, the illustration removes both a referential and a check constraint:

ALTER TABLE orders DROP CONSTRAINT (con_ref, con_check); 

The DROP CONSTRAINT statement is not available in the Informix® SQL implementation. However, if the DROP CONSTRAINT command existed, this clause of the ALTER TABLE statement would give functionality similar to that of that statement.

When you use the DROP TABLE statement to eliminate a table, it automatically removes any constraints from it.

Use the after command to add columns to an existing table at a specific location.

Syntax :

ALTER TABLE yourTableName
ADD COLUMN yourColumnName data type AFTER yourExistingColumnName;

Example 1: Let us explore more about how to add column at specific locations in the table.

ALTER TABLE tablename ADD columnname INT AFTER anothercolumn

The above script will throw following error:

The explanation for this is simple: the aforementioned syntax works in MySQL but not in SQL Server. The following syntax is valid in SQL Server:

ALTER TABLE tablename ADD columnname INT

A user, on the other hand, wished to insert the column between two other columns. The relational engine SQL Server is. In any T-SQL operation, the order of the columns should not matter. In most circumstances, it makes no difference (except when the table is extra large, and it has many NULL columns it impacts the size of the table). In actuality, if a user wishes to add a column to a table, he or she should simply add the column and then utilise column names to retrieve the column in a specific order in the table.

Example 2: Let's start by making a table to better understand the syntax. The following is the query to construct a table.

mysql> create table changeColumnPosition
-> (
-> Id_Position1 int,
-> Name_Position2 varchar(100),
-> Address_Position4 varchar(200)
-> );

Now you can check the description of existing table using desc command.

The syntax is as follows -

desc yourTableName;

The following is the query to check the description.

desc changeColumnPosition;

Output:

+-------------------+--------------+------+-----+---------+-------+
| Field             | Type         | Null | Key | Default | Extra |
+-------------------+--------------+------+-----+---------+-------+
| Id_Position1      | int(11)      | YES  |     | NULL    |       |
| Name_Position2    | varchar(100) | YES  |     | NULL    |       |
| Address_Position4 | varchar(200) | YES  |     | NULL    |       |
+-------------------+--------------+------+-----+---------+-------+

I have three columns and I want to add another column after Name_Position2 Field. The query is as follows.

alter table changeColumnPosition
-> add Age_Position3 int after Name_Position2;

After Name Position2, we successfully added column Age_Position3. Examine the existing table for the changes we made earlier.

desc changeColumnPosition;

Output:

 Field                 Type         Null    Key  Default Extra 

| Id_Position1      | int(11)      | YES  |     | NULL    |       |
| Name_Position2    | varchar(100) | YES  |     | NULL    |       |
| Age_Position3     | int(11)      | YES  |     | NULL    |       |
| Address_Position4 | varchar(200) | YES  |     | NULL    |       |

To add multiple columns to a table, use the ALTER TABLE statement in SQL.

The syntax to add multiple columns to an existing table in SQL Server is:

ALTER TABLE table_name
  ADD column_1 column_definition,
      column_2 column_definition,
      ...
      column_n column_definition;

• tbl_name: provide the name of the table to which a new column will be added.
• Column_name_1 datatype: select the data type and column name When adding additional columns, a comma must be used to separate each one (,)

Example 1: I want to add a new column named first_name to tblPatients. To add the column, we run the following query:

ALTER TABLE tblpatients ADD first_name VARCHAR(50)

The tblPatients table now has many columns, including the middle_name and last_name columns. We do that by running the following query:

ALTER TABLE tblpatients ADD middle_name VARCHAR(50),last_name VARCHAR(50)

Example 2: For example, this statement adds two columns color and note to the vehicles table:

ALTER TABLE vehicles
ADD color VARCHAR(50),
ADD note VARCHAR(255);

Example 3: The following statement creates a new table named candidates:

CREATE TABLE candidates (
    id INT PRIMARY KEY,
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE
);

To add three columns: home address, date of birth, and linkedin account to the candidates table, you use the following statement:

ALTER TABLE candidates
ADD COLUMN home_address VARCHAR(255),
ADD COLUMN dob DATE,
ADD COLUMN linkedin_account VARCHAR(255);

Example 4: Let's look at an example of how to use the ALTER TABLE statement in SQL Server to add multiple columns to a table.

ALTER TABLE employees
  ADD last_name VARCHAR(50),
  first_name VARCHAR(40);

This SQL Server ALTER TABLE example will add two columns, last_name as a VARCHAR(50) field and first_name as a VARCHAR(40) field to the employees table.

The ALTER TABLE ALTER COLUMN statement can be used to modify the table's column type.

1. The syntax to change the column type is following:

ALTER TABLE [tbl_name] ALTER COLUMN [col_name_1] [DATA_TYPE]

In the syntax,

• Tbl_name: Specify the table name
• Col_name: Enter the name of the column whose datatype you want to modify. After the ALTER COLUMN keyword, the col name must be supplied.
• Datatype: Specify the new datatype and length of the column

2. SQL query to change the column type in MySQL Server

To modify the datatype of a column, we can use the ALTER TABLE MODIFY COLUMN statement. The following is the syntax for changing the datatype of a column.

ALTER TABLE [tbl_name] MODIFY COLUMN [col_name_1] [DATA_TYPE], 
    MODIFY [col_name_2] [data_type], 
    MODIFY [col_name_3] [data_type]

In the syntax,

• Tbl_name: Specify the table name that contains the column that you want to change.
• Col_name: Enter the name of the column whose datatype you want to modify. After the MODIFY COLUMN keyword, the col name must be supplied. Multiple columns can have their data types changed. Each column must be separated with a comma when changing the datatype of multiple columns (,)
• Datatype: Specify the column's new datatype and length. After the column name, the datatype must be given.

Example 1: MySQL Modify datatype:

alter table Student modify(address varchar(30));

The above command will modify address column of the Student table.

Example 2: For demonstration, I have created a table named tblactor in DemoDatabase. The code to create the table is the following:

create table tblactor
    (
    actor_id int,
    first_name varchar(500),
    first_name varchar(500),
    address varchar(500),
    CityID int,
    lastupdate datetime
    )

3. SQL query to change the datatype of one column

The address column's column type should be changed from varchar(500) to TEXT datatype. To alter the datatype, run the following query.

ALTER TABLE tblActor MODIFY address TEXT

Run the following query to verify the changes:

describe tblactor

As you can see, the datatype of the address column has been changed to TEXT.

Example 3: For demonstration, I have created a table named tblStudent.

CREATE TABLE [dbo].[tblstudent]
    (
       [id]                [INT] IDENTITY(1, 1) NOT NULL,
       [student_code]      [VARCHAR](20) NOT NULL,
       [student_firstname] [VARCHAR](250) NOT NULL,
       [student_lastname]  [VARCHAR](10) NOT NULL,
       [address]           [VARCHAR](max) NULL,
       [city_code]         [VARCHAR](20) NOT NULL,
       [school_code]       [VARCHAR](20) NULL,
       [admissiondate]     [DATETIME] NULL,
       CONSTRAINT [PK_ID] PRIMARY KEY CLUSTERED ( [id] ASC )
    )

Let's say you wish to modify [address ] type from varchar(max) to nvarchar (1500). To alter the column type, use the following query.

Alter table tblstudent alter column address nvarchar(1500)

Verify the changes by running following script.

use StudentDB
go
select TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,DATA_TYPE, CHARACTER_MAXIMUM_LENGTH 
from INFORMATION_SCHEMA.COLUMNS where table_name='tblStudent'

Example 4: For example, this statement adds two columns color and note to the vehicles table:

ALTER TABLE vehicles
ADD color VARCHAR(50),
ADD note VARCHAR(255);

In MySQL, you may regularly need to adjust column size or field length. These commands can be used to increase or decrease the size of a column in MySQL.

To reduce the size of a column, make sure that all of the data in the column fits inside the new size.

Here's how to make a MySQL field longer. Consider a VARCHAR column with a length of 20 that you want to increase to 255.

Syntax:

ALTER TABLE table_name
MODIFY column_name
varchar(new_length);

You must supply table_name whose column you want to update, column_name of column whose length you want to change, and new_length, new size number in the above command.

Example 1: For example, we try to shorten the size of the phone column down to 12 characters:

ALTER TABLE accounts 
MODIFY phone VARCHAR2( 12 );

SQl Database issued the following error:

To fix this, first, we should remove the international code from the phone numbers:

UPDATE
    accounts
SET
    phone = REPLACE(
        phone,
        '+1-',
        ''
 );

The REPLACE() method replaces one substring with another. It replaces the '+1-' with an empty string in this case.

And then shorten the size of the phone column:

ALTER TABLE accounts 
MODIFY phone VARCHAR2( 12 );

Example 2: Here is an example to increase column size in MySQL:

create table sales(
       id int, 
       product_name varchar(20), 
       order_date date
       );

describe sales;

Output:

+--------------+-------------+------+-----+---------+-------+
| Field        | Type        | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| id           | int(11)     | YES  |     | NULL    |       |
| product_name | varchar(20) | YES  |     | NULL    |       |
| order_date   | date        | YES  |     | NULL    |       |
+--------------+-------------+------+-----+---------+-------+

The ALTER TABLE statement in SQL allows you to drop a primary key.

When you use ALTER TABLE... ALTER PRIMARY KEY to update a primary key, the old primary key index becomes a secondary index. You can use DROP CONSTRAINT/ADD CONSTRAINT instead of DROP CONSTRAINT/ADD CONSTRAINT if you don't want the former primary key to become a secondary index while replacing a primary key.

Syntax:

The syntax to drop a primary key in SQL is:

ALTER TABLE table_name
  DROP PRIMARY KEY;

table_name: The name of the table that has to be changed. You want to remove the primary key from this table.

Example 1: Let us now go over the entire scenario. We'll start by making a table with a primary key. The primary key will then be successfully dropped using SQL Server's right syntax.

CREATE TABLE Table1(
Col1 INT NOT NULL,
Col2 VARCHAR(100)
CONSTRAINT PK_Table1_Col1 PRIMARY KEY CLUSTERED (
Col1 ASC)
)
GO

/* For SQL Server/Oracle/MS ACCESS */
ALTER TABLE Table1
DROP CONSTRAINT PK_Table1_Col1
GO

/* For MySql */
ALTER TABLE Table1
DROP PRIMARY KEY
GO

Example 2: Let's look at an example of how to drop a primary key using the ALTER TABLE statement in SQL.

ALTER TABLE suppliers
  DROP PRIMARY KEY;

We've removed the primary key from the suppliers table in this example. We don't need to define the main key's name because a table can only have one primary key.

Example 3: Assume you wish to add name to the users table's composite primary key.

SHOW CREATE TABLE users;

  table_name |                      create_statement
-------------+--------------------------------------------------------------
  users      | CREATE TABLE users (
             |     id UUID NOT NULL,
             |     city VARCHAR NOT NULL,
             |     name VARCHAR NULL,
             |     address VARCHAR NULL,
             |     credit_card VARCHAR NULL,
             |     CONSTRAINT "primary" PRIMARY KEY (city ASC, id ASC),
             |     FAMILY "primary" (id, city, name, address, credit_card)
             | )
(1 row)

First, add a NOT NULL constraint to the name column with ALTER COLUMN.

ALTER TABLE users ALTER COLUMN name SET NOT NULL;

Then, in the same transaction, DROP the old "primary" constraint and ADD the new one:

BEGIN;
ALTER TABLE users DROP CONSTRAINT "primary";
ALTER TABLE users ADD CONSTRAINT "primary" PRIMARY KEY (city, name, id);
COMMIT;

NOTICE: Asynchronous primary key modifications are completed; additional schema changes on this table may be limited until the job is finished.

SHOW CREATE TABLE users;

  table_name |                          create_statement
-------------+---------------------------------------------------------------------
  users      | CREATE TABLE users (
             |     id UUID NOT NULL,
             |     city VARCHAR NOT NULL,
             |     name VARCHAR NOT NULL,
             |     address VARCHAR NULL,
             |     credit_card VARCHAR NULL,
             |     CONSTRAINT "primary" PRIMARY KEY (city ASC, name ASC, id ASC),
             |     FAMILY "primary" (id, city, name, address, credit_card)
             | )
(1 row)

Using ALTER PRIMARY KEY, a UNIQUE secondary index called users city_id_key would have been constructed. For the primary key constraint, there is only one index.

You may decide to remove a foreign key from the table after it has been created. In SQL Server, you can do this with the ALTER TABLE statement.

Syntax:

Remove a foreign key reference to another table's key. Use the following syntax for an unnamed FOREIGN KEY REFERENCES table constraint:

The syntax to drop a foreign key in SQL Server (Transact-SQL) is:

ALTER TABLE table_name
DROP CONSTRAINT fk_name;

Drop the foreign key constraint on the column named column column_2 in table table_3.

ALTER TABLE table_1 DROP FOREIGN KEY (column_2) REFERENCES table_3;

Parameters or Arguments:

• table_name: The name of the table that contains the foreign key.
• fk_name: The name of the foreign key you'd like to uninstall.

Example 1: Let's look at an example of how to drop a foreign key in SQL Server (Transact-SQL).

For example, if you had created a foreign key as follows:

CREATE TABLE products
( product_id INT PRIMARY KEY,
  product_name VARCHAR(50) NOT NULL,
  category VARCHAR(25)
);

CREATE TABLE inventory
( inventory_id INT PRIMARY KEY,
  product_id INT NOT NULL,
  quantity INT,
  min_level INT,
  max_level INT,
  CONSTRAINT fk_inv_product_id
    FOREIGN KEY (product_id)
    REFERENCES products (product_id)
);

We've made our parent table the products table in this foreign key example. The product id field serves as the primary key for the products table.

Then, as the child table in this foreign key example, we've established a second table called inventory. The Construct TABLE statement was used to create the fk inv product id foreign key on the inventory table. The product id column in the inventory table and the product id column in the products table are linked by the foreign key.

If we then wanted to remove the fk inv product id foreign key, we might use the command below:

ALTER TABLE inventory
DROP CONSTRAINT fk_inv_product_id;

This foreign key example would use the ALTER TABLE statement to drop the constraint called fk_inv_product_id from the inventory table.

Example 2: We want to remove the foreign key named fk_student_city_id from the table student.

ALTER TABLE student
DROP CONSTRAINT fk_student_city_id;

Discussion:

To drop a foreign key from a table, use the ALTER TABLE clause with the name of the table (in our example, student) followed by the clause DROP CONSTRAINT with the name of the foreign key constraint. In our example, the name of this constraint is fk_student_city_id.

If the constraint for the foreign key was generated by the database, you can find this name in the database. However, each database server has a different way to name constraints. In SQL Server, you can check it by selecting data from sys.key_constraints in the given database. In PostgreSQL, you select the conname column from the pg_constraint table. Oracle stores this data in the user_constraints table, and MySQL allows you to get names from CONSTRAINT_NAME column in information_schema.TABLE_CONSTRAINTS.

To add, edit, or drop/delete columns in a table, use the SQL ALTER TABLE statement. A table can also be renamed with the SQL ALTER TABLE statement.

1. Examples of ALTER TABLE ADD Column statement in SQL

The following two SQL examples will show you how to use the ALTER TABLE statement to add single and many columns to an existing table:

Let's take an example of a table named Cars:

Table: Cars

Car Name	    Car Color	Car Cost
Hyundai Creta	White	    10,85,000
Hyundai Venue	White	    9,50,000
Hyundai i20	    Red	        9,00,000
Kia Sonet	    White	    10,00,000
Kia Seltos	    Black	    8,00,000
Swift Dezire	Red	        7,95,000

Let's say you want to add a new column to the above table called Car Model. You must type the following query in SQL to accomplish this:

ALTER TABLE Cars ADD Car_Model Varchar(20);

This statement will add the Car_Model column to the Cars table.

2. Examples of ALTER TABLE MODIFY Column statement in SQL

Let's take an example of a table named Employee:

Table: Employee

Emp_Id	Emp_Name	Emp_Salary	Emp_City
201	    Abhay	    25000   	Goa
202	    Ankit	    45000   	Delhi
203	    Bheem	    30000   	Goa
204	    Ram	        29000   	Goa
205	    Sumit	    40000   	Delhi

Let's say you wish to change the datatypes of the aforementioned Employee table's two columns, Emp ContactNo. and Emp EmailID. You must type the following query in SQL to accomplish this:

ALTER TABLE Employee ADD ( Emp_ContactNo. Int, Emp_EmailID varchar(80) ;  

3. Examples of ALTER TABLE DROP Column statement in SQL

Let's take an example of a table named Employee:

Table: Employee

Emp_Id	Emp_Name	Emp_Salary	Emp_City
201	    Abhay	    25000	    Goa
202	    Ankit	    45000	    Delhi
203	    Bheem	    30000   	Goa
204	    Ram	        29000   	Goa
205	    Sumit	    40000	    Delhi

Let's say you want to get rid of the Emp_Salary and Emp_City columns in the Employee table. You must type the following two SQL queries to accomplish this:

ALTER TABLE Cars DROP COLUMN Emp_Salary ;  
ALTER TABLE Cars DROP COLUMN Emp_City;

4. Examples of ALTER TABLE RENAME Column statement in SQL

Let's take an example of a table named Employee:

Table: Employee

Emp_Id	Emp_Name	Emp_Salary	Emp_City
201	    Abhay   	25000   	Goa
202	    Ankit   	45000   	Delhi
203	    Bheem   	30000   	Goa
204	    Ram	        29000   	Goa
205	    Sumit	    40000   	Delhi

Let's say you wish to rename the Emp_City column in the Employee table above. You must type the following query in SQL to accomplish this:

ALTER TABLE Employee RENAME COLUMN Emp_City to Emp_Address;

Only add a column to a MySQL table if it doesn't already exist with this script.

Syntax:

ALTER TABLE [ IF EXISTS ] name ADD COLUMN [ IF NOT EXISTS ] column_name data_type 
[ COMMENT comment ] [ WITH ( property_name = expression [, ...] ) ]

If the column already exists, the optional IF NOT EXISTS clause will conceal the error.

Example 1: If table users exists but column zip does not, add the following to the users table:

ALTER TABLE IF EXISTS users ADD COLUMN IF NOT EXISTS zip varchar;

Example 2: Let's begin by creating a sample table with the create table script.

CREATE TABLE IF NOT EXISTS sale_details (
    id INT auto_increment,
    sale_person_name VARCHAR(255),
    no_products_sold INT,
    sales_department VARCHAR(255),
    primary key (id)
);

To view the columns of the table, let us execute:

DESC sale_details;

Only four columns are currently present in the table. Only after verifying if the column named sale_person_designation with VARCHAR datatype already exists will we attempt to add it to the sale_details table.

DELIMITER $$
DROP PROCEDURE IF EXISTS addColumnToTable $$
CREATE PROCEDURE addColumnToTable()
BEGIN
IF 
NOT EXISTS( (SELECT * FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE()
        AND COLUMN_NAME='sale_person_designation' AND TABLE_NAME='sale_details') ) 
THEN
    ALTER TABLE sale_details ADD sale_person_designation varchar(255);
END IF;
END $$
CALL addColumnToTable() $$
DELIMITER ;

Procedure addColumnToTable is created and called successfully.

Explanation:-

• The procedure named addColumnToTable is created by the script above.
• Within the NOT EXISTS() function, IF determines whether the column sale_person_designation exists in the table sale_details. If the table sale details contains the column sale person designation, the data is fetched from information schema.
• The Database() function gets the current MySQL schema from the server.
• The line after THEN is evaluated if the IF condition is not true.
• The new column will be added to the table using the ALTER TABLE query.
• CALL When we wish to run the procedure addColumnToTable, we call addColumnToTable().
• We'll run the DESC sale details statement once more to see if the column was added.

DESC sale_details;

The table will be updated with a new column called sale person designation.

What if the table sale_details already contains the column? Let's run the addColumnToTable() function once more.

CALL addColumnToTable();

The MySQL server throws no errors, indicating that the condition was checked properly before calling the ALTER TABLE query. We'll try to ADD the same column without testing the condition to make sure.

ALTER TABLE sale_details ADD sale_person_designation VARCHAR(255);

The ALTER command is a DDL command that allows you to change the structure of existing database tables by adding, changing, renaming, or eliminating columns and constraints. You can rename column names with the ALTER TABLE RENAME command.

RENAME TABLE gives you more options. It enables you to rename numerous tables in one command.

One or more tables are renamed with RENAME TABLE. The previous table requires ALTER and DROP access, but the new table requires CREATE and INSERT privileges.

Note: A single rename command cannot rename several columns. To rename numerous columns, use different rename scripts.

Syntax:

MySQL offers two ways to rename tables.

1. The first one uses the ALTER TABLE syntax:

ALTER TABLE old_table_name RENAME new_table_name;

2. The second way is to use RENAME TABLE:

RENAME TABLE old_table_name TO new_table_name;

3. You can use RENAME TABLE to move a table from one database to another:

RENAME TABLE current_db.tbl_name TO other_db.tbl_name;

4. RENAME TABLE, unlike ALTER TABLE, can rename multiple tables within a single statement:

RENAME TABLE old_table1 TO new_table1,
             old_table2 TO new_table2,
             old_table3 TO new_table3;

Renaming is done from the left to the right. To switch two table names, execute the following (assuming there isn't already a table with the intermediary name tmp_table):

RENAME TABLE old_table TO tmp_table,
             new_table TO old_table,
             tmp_table TO new_table;

Example 1: For the demo purpose, consider the following Employee table.

EmpId	FirstName	LastName	Email	PhoneNo	Salary	Address	PinCode

1. The following SQL script will rename PinCode to ZipCode in the Employee table in Oracle, MySQL, PostgreSQL, SQLite database.

ALTER TABLE Employee RENAME COLUMN PinCode TO ZipCode;

2. The following RENAME script renames multiple column names.

ALTER TABLE Employee 
RENAME COLUMN FirstName TO First_Name;

ALTER TABLE Employee 
RENAME COLUMN PhoneNo TO Phone;

Example 2: RENAME TABLE tbl_name TO new_tbl_name changes internally generated and user-defined CHECK constraint names that begin with the string “tbl_name_chk_” to reflect the new table name. MySQL interprets CHECK constraint names that begin with the string “tbl_name_chk_” as internally generated names. Example:

mysql> SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `i1` int(11) DEFAULT NULL,
  `i2` int(11) DEFAULT NULL,
  CONSTRAINT `t1_chk_1` CHECK ((`i1` > 0)),
  CONSTRAINT `t1_chk_2` CHECK ((`i2` < 0))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.02 sec)

mysql> RENAME TABLE t1 TO t3;
Query OK, 0 rows affected (0.03 sec)

mysql> SHOW CREATE TABLE t3\G
*************************** 1. row ***************************
       Table: t3
Create Table: CREATE TABLE `t3` (
  `i1` int(11) DEFAULT NULL,
  `i2` int(11) DEFAULT NULL,
  CONSTRAINT `t3_chk_1` CHECK ((`i1` > 0)),
  CONSTRAINT `t3_chk_2` CHECK ((`i2` < 0))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

How to alter null to not null in table

An existing column's nullability can be changed using the Alter Table query.

However, if a NULL column already contains null values, SQL Server will not change it to NOT NULL.

We may use our ALTER statement to alter the column so that no NULL values are allowed in future additions if there are no NULL values any longer.

Before making any modifications to your database, look over what data may (and cannot) be supplied within an existing column that you want to modify to NOT NULL, to ensure that no record has a NULL value in that column.

Most importantly, before the ALTER command can be successfully used and the column set NOT NULL, all existing NULL values within the column must be modified to a non-null value. Any effort to set the column to NOT NULL while it still contains NULL data will result in an error and no modification.

Syntax:

ALTER TABLE table_name ALTER COLUMN col_name data_type NOT NULL;

Replace table_name, col_name and data_type with table name, column name and data type respectively.

Example 1: Unnullifying Existing Column Data:

To make sure our column doesn't have any NULL values, we'll use a simple UPDATE statement that only applies to rows where the value is currently NULL. We have a simple table of client information including name, email, and phone number, for instance. We don't want to allow a NULL phone value in a handful of the records right now:

clientsID     name              email                     phone
1             Neville Estes     Quisque@diamProin.com          1-843-863-2697
2             Flynn Fry         velit@senectus.net
3             Wyatt Schmidt     nibh.dolor@sit.co.uk           1-950-895-1847
4             Oleg Hill         vitae.sodales@Morbi.edu        1-173-344-1578
5             Randall Bullock   eu@lacus.net

As a result, we can use the following line to enter a default value for all of the phone entries that are now NULL:

UPDATE
  clients
SET
  phone = '0-000-000-0000'
WHERE
  phone IS NULL;

Now our NULL values have all been replaced with the value we consider the default, 0-000-000-0000:

clientsID   name            email                        phone
1           Neville Estes   Quisque@diamProin.com           1-843-863-2697
2           Flynn Fry       velit@senectus.net              0-000-000-0000
3           Wyatt Schmidt   nibh.dolor@sit.co.uk            1-950-895-1847
4           Oleg Hill       vitae.sodales@Morbi.edu         1-173-344-1578
5           Randall Bullock eu@lacus.net                    0-000-000-0000

Example 2: Alter the Column Data Structure

Since we’re altering the phone column in this example, the statement will look something like this:

ALTER TABLE
  clients
ALTER COLUMN
  phone
    NVARCHAR(20) NOT NULL;

Verify Altered Nullability

It's a good idea to conduct a basic INSERT test and try to insert a new record with the NULL value in the updated column after you've made the change to your column:

INSERT INTO
  clients(name, email, phone)
VALUES
  ('John Doe', 'jdoe@domain.com', NULL);

If all went according to plan, SQL Server will issue an error stating that the column doesn’t allow NULL values:

Cannot insert the value NULL into column 'phone', table 'library.dbo.clients'; column does not allow nulls. INSERT fails. [SQL State=23000, DB Errorcode=515]

Example 3: SQL query to change amount column from NULL to NOT NULL.

For MySQL

ALTER TABLE sales
MODIFY COLUMN amount int NOT NULL;

For SQL Server/PostgreSQL

ALTER TABLE sales
ALTER COLUMN amount int NOT NULL;

Similarly, here are the SQL queries to change order_date column from NULL to NOT NULL

For MySQL

ALTER TABLE sales
MODIFY COLUMN order_date date NOT NULL;

For SQL Server/PostgreSQL

ALTER TABLE sales
ALTER COLUMN order_date date NOT NULL;

Also read: How to List databases and tables in PostgreSQL

We verify the above change by running the describe table command in MySQL.

describe sales;

+------------+---------+------+-----+---------+-------+
| Field      | Type    | Null | Key | Default | Extra |
+------------+---------+------+-----+---------+-------+
| id         | int(11) | YES  |     | NULL    |       |
| amount     | int(11) | NO   |     | NULL    |       |
| order_date | date    | NO   |     | NULL    |       |
+------------+---------+------+-----+---------+-------+

You'll see that the columns amount and order_date have no value for the NULL column, suggesting that they can't store NULL values.

To apply a particular constraint on a table column, we use the ALTER TABLE ADD CONSTRAINT command.

To handle database data by declaring the status of certain data in a specified column. When using PostgreSQL, numerous constraints are applied to data. PRIMARY KEY constraints, FOREIGN KEY constraints, CHECK constraints, UNIQUE constraints, and NOT NULL constraints are among them. When designing and maintaining the link between data in distinct tables in a database, constraints like the PRIMARY KEY and the FOREIGN KEY are useful.

Note: The ADD CONSTRAINT statement alters the schema.

When creating a table, the primary key should be explicitly defined. ADD CONSTRAINT... PRIMARY KEY can be used to replace a primary key that already exists.

ALTER COLUMN is used to deal with the DEFAULT and NOT NULL constraints.

Tip: This command can be used in a single sentence with other ALTER TABLE commands.

1.Required privileges

The user must have the CREATE privilege on the table.

2. Parameters

• The ALTER COLUMN command is used to add/change a DEFAULT constraint.
• Adding/changing the PRIMARY KEY of a table is not possible.
• ALTER TABLE is only available during the table creation process.

1. Viewing schema changes

This schema change statement is registered as a job. You can view long-running jobs with SHOW JOBS.

Changing primary keys with ADD CONSTRAINT ... PRIMARY KEY

The former primary key index becomes a secondary index when you alter a primary key with ALTER TABLE... ALTER PRIMARY KEY. ALTER PRIMARY KEY creates a secondary index that uses node memory and can delay write performance in a cluster. You can replace the previous primary index without generating a secondary index if you don't have any queries that filter on the primary key you're substituting.

If one of the following conditions is true, ADD CONSTRAINT... PRIMARY KEY can be used to add a primary key to an existing table:

At the time of table creation, no primary key was specified explicitly. The table is constructed in this example using rowid as the primary key. The ADD CONSTRAINT... PRIMARY KEY command replaces the default primary key with a new one.

In the same transaction, a DROP CONSTRAINT statement comes before the ADD CONSTRAINT... PRIMARY KEY declaration. See the example below for how to drop and add the primary key constraint.

TABLE CHANGE... ADD ALTER TABLE... ADD CONSTRAINT... PRIMARY KEY is an alias for PRIMARY KEY.

Note: ALTER TABLE ... ADD PRIMARY KEY is an alias for ALTER TABLE ... ADD CONSTRAINT ... PRIMARY KEY.

Example 1: When using the CHECK constraint, all of the values in a column must evaluate to TRUE in a Boolean statement.

ALTER TABLE rides ADD CONSTRAINT check_revenue_positive CHECK (revenue >= 0);

Columns that were generated earlier in the transaction can have check constraints added to them. For example:

BEGIN;
ALTER TABLE users ADD COLUMN is_owner STRING;
ALTER TABLE users ADD CONSTRAINT check_is_owner CHECK (is_owner IN ('yes', 'no', 'unknown'));
COMMIT;
BEGIN
ALTER TABLE
ALTER TABLE
COMMIT

Note: In the following situations, the entire transaction, including any newly added columns, will be rolled back:

If a value that violates the new constraint is recognized in an existing column.

If a new column contains a default value or is a calculated column, values that violate the new constraint would be present.

2. Add the foreign key constraint with CASCADE

To add a foreign key constraint, use the steps shown below.

Given two tables, users and vehicles, without foreign key constraints:

SHOW CREATE users;

  table_name |                      create_statement
-------------+--------------------------------------------------------------
  users      | CREATE TABLE users (
             |     id UUID NOT NULL,
             |     city VARCHAR NOT NULL,
             |     name VARCHAR NULL,
             |     address VARCHAR NULL,
             |     credit_card VARCHAR NULL,
             |     CONSTRAINT "primary" PRIMARY KEY (city ASC, id ASC),
             |     FAMILY "primary" (id, city, name, address, credit_card)
             | )
(1 row)

SHOW CREATE vehicles;

  table_name |                                       create_statement
-------------+------------------------------------------------------------------------------------------------
  vehicles   | CREATE TABLE vehicles (
             |     id UUID NOT NULL,
             |     city VARCHAR NOT NULL,
             |     type VARCHAR NULL,
             |     owner_id UUID NULL,
             |     creation_time TIMESTAMP NULL,
             |     status VARCHAR NULL,
             |     current_location VARCHAR NULL,
             |     ext JSONB NULL,
             |     CONSTRAINT "primary" PRIMARY KEY (city ASC, id ASC),
             |     FAMILY "primary" (id, city, type, owner_id, creation_time, status, current_location, ext)
             | )
(1 row)

You can determine what happens when a foreign key is modified or destroyed using a foreign key action.

When ON DELETE CASCADE is used, all dependent items are also removed when the referred row is deleted.

Warning: CASCADE does not provide a list of the objects it drops or updates, so proceed with caution.

ALTER TABLE vehicles ADD CONSTRAINT users_fk FOREIGN KEY (city, owner_id) 
REFERENCES users (city, id) ON DELETE CASCADE;

Note: Referenced columns are required to be in the same database as the referencing foreign key column. Set the sql.cross_db_fks.enabled cluster setting to true to allow cross-database foreign key references.

Example 2: Let’s start by creating a books table with book_id and the book_name columns using the following statement:

CREATE TABLE books (
   book_id SERIAL,
   book_name VARCHAR
);

Let’s display the table structure and view it before we alter any state:

articles=# \d books;

Table "public.books"

  Column   |       Type        | Collation | Nullable |                Default
-----------+-------------------+-----------+----------+----------------------------------------
 book_id   | integer           |           | not null | nextval('books_book_id_seq'::regclass)
 book_name | character varying |           |          |

Then, we will add the primary key constraint to the book_id column as shown below:

ALTER TABLE books
ADD PRIMARY KEY (book_id);

The following changes appear after executing the above command:

articles=# \d books;

Table "public.books"

  Column   |       Type        | Collation | Nullable |                Default
-----------+-------------------+-----------+----------+----------------------------------------
 book_id   | integer           |           | not null | nextval('books_book_id_seq'::regclass)
 book_name | character varying |           |          |

Indexes:

"books_pkey" PRIMARY KEY, btree (book_id)

3. Adding FOREIGN KEY constraint

A foreign key in a table is a field or group of fields that uniquely identifies another row in another table. A foreign key in one table usually refers to a primary key in the other.

The ALTER TABLE statement is used to create foreign keys in an existing table.

The following syntax is used:

ALTER TABLE child_table
ADD CONSTRAINT constraint_name FOREIGN KEY (c1) REFERENCES parent_table (p1);

The foreign key will be stored in the child_table, whereas the main keys will be stored in the parent_table, as seen in the above syntax. The columns C1 and p1 are from the child_table and parent_table columns, accordingly. In this example, we'll add a foreign key to an existing table:

ALTER TABLE books
ADD FOREIGN KEY (book_id) REFERENCES library (book_id);

Output:

articles=# \d books;

Table "public.books"

  Column   |       Type        | Collation | Nullable |                Default
-----------+-------------------+-----------+----------+----------------------------------------
 book_id   | integer           |           | not null | nextval('books_book_id_seq'::regclass)
 book_name | character varying |           |          |

Indexes:

"books_pkey" PRIMARY KEY, btree (book_id)

Foreign-key constraints:

"books_book_id_fkey" FOREIGN KEY (book_id) REFERENCES library

4. ADD CHECK CONSTRAINT

The CHECK constraint checks whether the data in the column meets a set of criteria.

Syntax to add check constraint:

ALTER TABLE TABLE _name
ADD CONSTRAINT constaint_name  CHECK (CONSTRAINT);

Example:

ALTER TABLE student
ADD CONSTRAINT age_constraint CHECK (age >= 10);

Output:

articles=# \d student;

Table "public.student"

 Column |         Type          | Collation | Nullable |               Default
--------+-----------------------+-----------+----------+-------------------------------------
 id     | integer               |           | not null | nextval('student_id_seq'::regclass)
 name   | character varying(50) |           | not null |
 age    | integer               |           |          |
 gender | character varying(50) |           | not null |
 marks  | double precision      |           |          |

Indexes:

"student_pkey" PRIMARY KEY, btree (id)

Check constraints:

"age_constraint" CHECK (age >= 10)

5. ADD UNIQUE constraints

When we want to make sure that data like email addresses or usernames are unique across all table rows, we utilize the UNIQUE constraint.

Syntax to add a UNIQUE constraint to a given column:

ALTER TABLE TABLE_NAME
ADD CONSTRAINT constraint_name UNIQUE (column1, column2, ... column_n);

Example:

ALTER TABLE teacher_details
ADD CONSTRAINT email_unique UNIQUE (email);

Below are the results of adding unique constraint in our teacher_details table:

articles=# \d teacher_details;

Table "public.teacher_details"

       Column        |       Type        | Nullable |                       Default
---------------------+-------------------+-----------+----------+----------------------------------
 teacher_id          | integer           | not null | nextval('teacher_details_teacher_id_seq'::regclass)
 teacher_name        | character varying | not null |
 registration_number | character varying |          |
 email               | character varying |          |
 class_id            | character varying |          |
 phone_number        | character varying |          |

Indexes:

"teacher_details_pkey" PRIMARY KEY, btree (teacher_id)
"email_unique" UNIQUE CONSTRAINT, btree (email)

6. NOT NULL Constraint

NOT NULL Constraint indicates that a column should not accept null values. Null indicates that the field is empty. It is not, however, equal to an empty string or zero. This type of constraint may have appeared in prior commands.

Syntax to set NOT NULL constraint on an existing table column :

ALTER TABLE TABLE_NAME
ALTER COLUMN column_name SET NOT NULL;

Example 1:

ALTER  TABLE teacher_details
ALTER COLUMN email SET NOT NULL;

Example 2: Let's look at the example. Assuming our starting point is the Customer table created in the CREATE 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.