SQL - Alter Table

In SQL, the ALTER TABLE statement is used to modify the structure of an existing table. It allows you to add, modify, or delete columns, constraints, and other attributes of a table.

Adding a Column:

To add a new column to an existing table, use the ALTER TABLE statement followed by the ADD keyword and the column definition.

Example: Adding a column named Email of type VARCHAR(50) to a table named Customers:

ALTER TABLE Customers
ADD Email VARCHAR(50);

Modifying a Column:

To modify an existing column in a table, use the ALTER TABLE statement with the ALTER COLUMN clause, followed by the column definition.

Example: Modifying the data type of a column named Age from INT to SMALLINT in a table named Employees:

ALTER TABLE Employees
ALTER COLUMN Age SMALLINT;

Deleting a Column:

To remove a column from a table, use the ALTER TABLE statement with the DROP COLUMN clause, followed by the column name.

Example: Deleting a column named Phone from a table named Suppliers:

ALTER TABLE Suppliers
DROP COLUMN Phone;

Adding a Primary Key Constraint:

To add a primary key constraint to a table, use the ALTER TABLE statement with the ADD CONSTRAINT clause and the PRIMARY KEY keyword.

Example: Adding a primary key constraint on a column named ID in a table named Orders:

ALTER TABLE Orders
ADD CONSTRAINT PK_Orders PRIMARY KEY (ID);

Adding a Foreign Key Constraint:

To add a foreign key constraint to a table, use the ALTER TABLE statement with the ADD CONSTRAINT clause, the FOREIGN KEY keyword, and the REFERENCES keyword.

Example: Adding a foreign key constraint on a column named CustomerID in a table named Orders that references the Customers table:

ALTER TABLE Orders
ADD CONSTRAINT FK_Orders_Customers FOREIGN KEY (CustomerID)
REFERENCES Customers (ID);

Modifying a Constraint:

To modify an existing constraint, use the ALTER TABLE statement with the ALTER CONSTRAINT clause and the constraint definition.

Example: Modifying the name of a primary key constraint named PK_Employees to PK_Emp in a table named Employees:

ALTER TABLE Employees
ALTER CONSTRAINT PK_Employees
RENAME TO PK_Emp;

Renaming a Table:

To rename an existing table, use the ALTER TABLE statement with the RENAME TO clause and the new table name.

Example: Renaming a table named OldTable to NewTable:

ALTER TABLE OldTable
RENAME TO NewTable;

Adding a Default Value to a Column:

To add a default value to an existing column, use the ALTER TABLE statement with the ALTER COLUMN clause and the SET DEFAULT keyword.

Example: Adding a default value of 'Unknown' to a column named Country in a table named Customers:

ALTER TABLE Customers
ALTER COLUMN Country SET DEFAULT 'Unknown';

Removing a Default Value from a Column:

To remove the default value from a column, use the ALTER TABLE statement with the ALTER COLUMN clause and the DROP DEFAULT keyword.

Example: Removing the default value from a column named Discount in a table named Products:

ALTER TABLE Products
ALTER COLUMN Discount DROP DEFAULT;

Modifying the Size of a Column:

To change the size or length of an existing column, use the ALTER TABLE statement with the ALTER COLUMN clause and specify the new size.

Example: Modifying the length of a column named Description to 200 characters in a table named Items:

ALTER TABLE Items
ALTER COLUMN Description VARCHAR(200);

Adding a Unique Constraint:

To add a unique constraint to a column or set of columns, use the ALTER TABLE statement with the ADD CONSTRAINT clause, the UNIQUE keyword, and the column(s) involved.

Example: Adding a unique constraint to a column named Username in a table named Users:

ALTER TABLE Users
ADD CONSTRAINT UQ_Users_Username UNIQUE (Username);

Removing a Constraint:

To remove a constraint from a table, use the ALTER TABLE statement with the DROP CONSTRAINT clause and the constraint name.

Example: Removing a unique constraint named UQ_Orders_OrderNumber from a table named Orders:

ALTER TABLE Orders
DROP CONSTRAINT UQ_Orders_OrderNumber;

Remember to exercise caution when using the ALTER TABLE statement, as it can modify the structure of your database and potentially affect existing data. Make sure to backup your database before making any significant changes.