The SQL Alter command in SQL can perform various changes on a table, view, or even the entire database. It is employed as part of Data Definition Language (DDL) instructions and works with multiple Relational Database Management Systems (RDBMS).
For example, it has been proven compatible with Oracle DBMS, Microsoft SQL Server, MySQL, etc. This function allows us to add constraints, columns, and indexes; modify them; delete them if necessary to suit our purpose without affecting CRUD operations.
How to Use Alter Table Command?
This article will look at different ways you can use the ALTER command. We use SQL Server as our database server, but the syntax for many other RDBMS, such as Oracle and Microsoft SQL, is similar.
Here are the below operations we can do with the Alter Table Command:
- Add Column: Adds a column to a table.
- Add Primary Key: Adds a primary key to a table
- Add Foreign Key: Adds a foreign key to a table
- Add Constraint: Adds a check constraint to a table.
- Change Column Type: Changes the data type of a column.
- Drop Column: Drops a column from a table.
- Drop Constraint: Drops a check constraint from a table.
- Drop Primary Key: Drops a primary key from a table.
- Drop Foreign Key: Drops a foreign key from a table.
- Rename Table: Renames a table.
- Rename Column: Renames a column.
Now let us discuss each of these actions in detail with examples, which will help you understand the use of Alter command. But to perform the above action, we should have a table, and for that, we will use the employee table looks like the below:
CREATE TABLE Employee ( EmployeeID int IDENTITY (1,1) NOT NULL, EmployeeName nvarchar (50) NOT NULL );
SQL Alter Table Add Column
You can use the ALTER TABLE ADD command to add a new column to a database table. And the syntax will be like below:
ALTER TABLE table_name ADD column_name datatype;
We can use the ALTER command to add new columns to the Employee table, which we created in the Create Table section and is shown above.
ALTER TABLE Employee
ADD Address VARCHAR(100),
City VARCHAR(25),
PinCode integer;
With Alter Table Command’s help, we added three more columns to the employee table: Address, City, and Pincode.
Adding Primary Key Constraint Using ALTER Command in SQL
In the below example, we will try to add Primary Key to an existing table. In the current employee table, let’s try to add a primary key constraint into the employee table.
The syntax for adding a primary key constraint
ALTER TABLE <table_name> ADD CONSTRAINT <primarykey_name> PRIMARY KEY (<column_names>);
Let us implement the above script
ALTER TABLE Employee ADD CONSTRAINT PK_Employee_EmployeeID PRIMARY KEY (EmployeeID);
After adding the primary key, if someone wants to verify whether the primary key is added or not, the user can verify by using the SSMS or views. You can use the INFORMATION_SCHEMA.TABLE_CONSTRAINTS and INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE view what’s inside the view.
You can run the below scripts to see the details of the views:
select * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS;
select * from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE;
Or you can use the below SQL script to see the Primary Key column name using the below script.
select C.COLUMN_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS T JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE C ON C.CONSTRAINT_NAME=T.CONSTRAINT_NAME WHERE C.TABLE_NAME='Employee' and T.CONSTRAINT_TYPE='PRIMARY KEY'
Adding Foreign Key Constraint Using ALTER Command in SQL
We can use the ALTER command in SQL to create a relationship between two tables by adding a foreign key constraint. For example, we could add a foreign key to the Employee table for the DepartmentID column to refer to the DepartmentID of another table called ‘Department’.
ALTER TABLE Employee ADD CONSTRAINT FK_Employee_Department FOREIGN KEY (DepartmentID) REFERENCES Department (DepartmentID);
Change Column Field Size Using Alter Table Command
Sometimes there is a requirement to update the size of the field. In that case, we can use the Alter table command to increase or decrease the size of the Field. But before that, let us see the current size of the Fields, and you can use the below script to see the details.
EXEC sp_help 'dbo.Employee';
From the above image, you can notice the length of the City field is 25, but we will increase the field’s size from 25 to 50. and to do that, you can check the below script:
ALTER TABLE Employee ALTER COLUMN City VARCHAR(50); EXEC sp_help 'dbo.Employee';
Be careful while decreasing the size of a column where the data exist. It will raise an error if the new size is less than any of the data in that column.
Change Column Data Type Using Alter Table Command
EXEC sp_help 'dbo.Employee';
To Change the data type of pincode field from Integer to Varchar, we can use the below script.
ALTER TABLE Employee ALTER COLUMN PinCode Varchar(10); EXEC sp_help 'dbo.Employee';
A column must be empty while changing the type of a column; otherwise, it will raise an error.
SQL Drop Column
To drop a column of an existing column, you can check the below script:
ALTER TABLE table_name DROP column_name1, column_name2...;
To Delete the columns of the table, you can use the below script:
ALTER TABLE Employee DROP COLUMN Address; ALTER TABLE Employee DROP (City, PinCode); --deletes multiple columns
SQL Drop Primary Key
To Delete the Primary key constraint from an existing table, you can check the below syntax:
ALTER TABLE table_name DROP CONSTRAINT constraint_name;
In the below example, we will try to remove the Primary Key from the employee table. Check the below image to get the details before deleting the Primary key constraint.
ALTER TABLE Employee DROP CONSTRAINT PK_Employee_EmployeeID;
After executing the above script, the primary key constraint is dropped from the database table. To check that you can execute the below script so that you can get the details of the constraints
select * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS select * from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
Drop Foreign Key In SQL
Using the ALTER TABLE statement, you can remove a foreign key from a table in SQL Server (Transact-SQL). Should you decide that you no longer require the foreign key after it has been created, this option is available.
We have added the key details image before deleting the foreign key constraint
Now we will find out how to remove the foreign key from the employee table. To Remove the foreign key, you can use the below script:
ALTER TABLE Employee DROP CONSTRAINT FK_Employee_Department;
SQL Alter Table
If there is a requirement to change the table’s name, then you can use the stored procedure called sp_rename. Here is the script to change the table name:
exec sp_rename 'Employee', 'empl';
The above script will change the name of the Employee table to empl. But the above statement will give you a warning message.
Caution: Changing any part of an object name could break scripts and stored procedures.
SQL Alter Column Name | Rename Column Name
We can Rename or Alter the column names also with the help of the sp_rename stored procedure, and to do so, you can check the below script.
EXEC sp_rename 'dbo.Employee.Address', 'EmployeeAddress', 'COLUMN';
But when you execute the above script, you will get the below warning message:
Caution: Changing any part of an object name could break scripts and stored procedures.
Conclusion:
In this article, you gained an understanding of the SQL ALTER command in SQL and its various uses. You can now apply what you have learned to your tables and databases! Knowledge of the ALTER commands is essential if managing large databases is part of your work. This tutorial provides just one basic concept; for further information on related topics, please check out our comprehensive SQL Tutorial For Beginners and Experienced. If you still have any questions, you can contact us by commenting in the comment section.
