Database Management System (DBMS) / SQL(STRUCTURED QUERY LANGUAGE)

ALTER operations in SQL

The ALTER TABLE command is used to:

  • ➕ Add new columns

  • ✏️ Modify existing columns

  • ❌ Drop columns

  • 🔐 Add / remove constraints

📌 Base Setup (Only ONE code block)

We will use this same table for ALL examples below:

-- Base Table
CREATE TABLE students (
    id INT IDENTITY(1,1) PRIMARY KEY,
    name VARCHAR(50),
    grade VARCHAR(2)
);

-- Sample Data
INSERT INTO students (name, grade) VALUES
('Raj', 'B'),
('Tina', 'B'),
('Anya', 'A'),
('Sara', 'A');

➡️ All ALTER operations below will be performed on this same students table.

➕ Add a New Column

ALTER TABLE students
ADD email VARCHAR(100);

✔️ Adds a new column named email

✏️ Modify Column Data Type

ALTER TABLE students
ALTER COLUMN grade VARCHAR(5);

✔️ Expands grade column size from VARCHAR(2) to VARCHAR(5)

❌ Drop a Column

ALTER TABLE students
DROP COLUMN email;

✔️ Removes the email column completely

🔐 Add PRIMARY KEY (example on same table structure idea)

If primary key DID NOT exist before

ALTER TABLE students
ADD CONSTRAINT pk_students PRIMARY KEY (id);

✔️ Makes id primary key

🔗 Add FOREIGN KEY

Assume we create another table:

ALTER TABLE students
ADD age INT;

Now add a foreign key to another table:

ALTER TABLE enrollments
ADD CONSTRAINT fk_student
FOREIGN KEY (student_id) REFERENCES students(id);

✔️ Ensures student_id must exist in students table first

📘 Summary Table

Operation

Example

Add Column

ADD column_name

Modify Column

ALTER COLUMN

Drop Column

DROP COLUMN

Add PK

ADD PRIMARY KEY

Add FK

ADD FOREIGN KEY