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.