The ALTER TABLE statement in Microsoft SQL Server (MSSQL) and PostgreSQL

The ALTER TABLE statement in Microsoft SQL Server (MSSQL) and PostgreSQL is used to modify the structure of an existing table. It allows you to:

• Add, drop, or modify columns
• Add or drop constraints
• Rename tables or columns

Feature	MSSQL Example	PostgreSQL Example
Add column	ADD	ADD COLUMN
Drop column	DROP COLUMN	DROP COLUMN
Modify column	ALTER COLUMN	ALTER COLUMN ... TYPE
Rename column	sp_rename	RENAME COLUMN
Rename table	sp_rename	RENAME TO
Add Primary Key	ADD CONSTRAINT ... PRIMARY KEY	ADD CONSTRAINT ... PRIMARY KEY
Add Foreign Key	ADD CONSTRAINT ... FOREIGN KEY REFERENCES	ADD CONSTRAINT ... FOREIGN KEY REFERENCES

---

🔄 ALTER TABLE Statement: MSSQL vs PostgreSQL with Flag

Operation	MSSQL Example	PostgreSQL Example	Same?
Add a Column	ALTER TABLE Employees ADD Email VARCHAR(100);	ALTER TABLE Employees ADD COLUMN Email VARCHAR(100);	❌
Drop a Column	ALTER TABLE Employees DROP COLUMN Email;	ALTER TABLE Employees DROP COLUMN Email;	✅
Modify Column Type	ALTER TABLE Employees ALTER COLUMN Name NVARCHAR(150);	ALTER TABLE Employees ALTER COLUMN Name TYPE VARCHAR(150);	❌
Rename a Column	EXEC sp_rename 'Employees.Name', 'FullName', 'COLUMN';	ALTER TABLE Employees RENAME COLUMN Name TO FullName;	❌
Rename a Table	EXEC sp_rename 'Employees', 'Staff';	ALTER TABLE Employees RENAME TO Staff;	❌
Add Primary Key	ALTER TABLE Employees ADD CONSTRAINT PK_EmpID PRIMARY KEY (EmployeeID);	ALTER TABLE Employees ADD CONSTRAINT PK_EmpID PRIMARY KEY (EmployeeID);	✅
Drop Primary Key	ALTER TABLE Employees DROP CONSTRAINT PK_EmpID;	ALTER TABLE Employees DROP CONSTRAINT PK_EmpID;	✅
Add Foreign Key	ALTER TABLE Orders ADD CONSTRAINT FK_Orders_Emp FOREIGN KEY (EmployeeID) REFERENCES Employees(EmployeeID);	ALTER TABLE Orders ADD CONSTRAINT FK_Orders_Emp FOREIGN KEY (EmployeeID) REFERENCES Employees(EmployeeID);	✅
Drop Foreign Key	ALTER TABLE Orders DROP CONSTRAINT FK_Orders_Emp;	ALTER TABLE Orders DROP CONSTRAINT FK_Orders_Emp;	✅

---

✅ Summary:

• Identical Syntax for: Add/Drop Primary Key, Add/Drop Foreign Key, Drop Column.
• Different Syntax for: Add Column, Modify Column Type, Rename Column/Table.