DBMS Part 4: SQL ALTER TABLE, NOT NULL, UNIQUE & Constraints

on

Namaste. So far every DBMS post has worked with a table that was already built, Students, with its columns fixed from the start. Real projects aren't that tidy, requirements change, someone decides halfway through the year that the register also needs an email column. ALTER TABLE is how you change a table's structure after it already exists, without losing the data that's already in it. Alongside it, we're covering constraints properly, the rules that stop bad data from ever getting in to begin with.

What's in this post

  1. ALTER TABLE, changing structure after the fact
  2. What constraints actually are
  3. NOT NULL
  4. UNIQUE
  5. DEFAULT
  6. A first look at FOREIGN KEY
  7. Alter a live table and break its rules (try it!)
  8. Example 1 (Easy): Adding a column
  9. Example 2 (Medium): Adding a constrained column
  10. Example 3 (Harder): Removing a column safely
  11. Example 4 (Hero level): Designing with constraints from day one
  12. Common mistakes
  13. Practice tasks

1. ALTER TABLE, changing structure after the fact

alter_add.sql
ALTER TABLE Students
ADD COLUMN Email VARCHAR(50);

ALTER TABLE Students
DROP COLUMN Email;
What happens to existing rows when you ADD a column? Every row that already existed gets the new column too, filled with an empty value (or a DEFAULT, which we'll cover shortly) until you update it. Nothing gets lost.

2. What constraints actually are

Analogy: a constraint is a bouncer at the door of your table. Before any row is allowed in, or allowed to change, the bouncer checks it against a rule, no ID, no entry. You've already met one constraint, PRIMARY KEY, which enforces "no duplicates allowed." The ones in this post work the same way, just checking for different things.

3. NOT NULL

not_null.sql
ALTER TABLE Students
ADD COLUMN Guardian VARCHAR(50) NOT NULL;

NOT NULL simply refuses to let a row be saved if that column is left empty. Useful for anything genuinely required, a name, a roll number, a guardian's name on a school record.

4. UNIQUE

unique.sql
ALTER TABLE Students
ADD COLUMN Email VARCHAR(50) UNIQUE;
UNIQUE vs PRIMARY KEY: both reject duplicates, but a table can only have one PRIMARY KEY, while it can have several UNIQUE columns. RollNo should be the primary key, but Email could sensibly be UNIQUE too, two different students genuinely shouldn't share either one.

5. DEFAULT

default.sql
ALTER TABLE Students
ADD COLUMN Status VARCHAR(20) DEFAULT 'Active';

If a new row doesn't specify a value for Status, it automatically becomes "Active" instead of being left empty. Small thing, saves a lot of repetitive typing.

6. A first look at FOREIGN KEY

Analogy: imagine a separate Sections table listing A, B, and C properly, with details about each one, its class teacher, its room number. A FOREIGN KEY in the Students table would mean "the Section value here must actually exist in that Sections table," it's how two tables stay honestly connected to each other, instead of just hoping the data matches by coincidence.
foreign_key.sql
CREATE TABLE Sections (
    SectionID CHAR(1) PRIMARY KEY,
    ClassTeacher VARCHAR(50)
);

CREATE TABLE Students (
    RollNo INT PRIMARY KEY,
    Name VARCHAR(50),
    SectionID CHAR(1),
    FOREIGN KEY (SectionID) REFERENCES Sections(SectionID)
);

We'll go much deeper into connecting tables like this when we cover JOINs later in the series, for now, just hold onto the idea, a foreign key is a promise that a value in one table genuinely points to something real in another.

7. Alter a live table and break its rules

This sandbox starts with the same Students table, but now you can actually reshape it. Add a column with constraints, then try to insert data that breaks those constraints, and watch it get rejected, exactly like a real database would.

Try it: ALTER, then INSERT, and watch constraints work

Run these one at a time, in order, to see the full story.

1. Add Email (UNIQUE) 2. Insert with Email 3. Insert duplicate Email (fails) 4. Insert missing Name (fails) 5. Drop Email column

Current table structure and data

8. Example 1 (Easy): Adding a column

add_simple.sql
ALTER TABLE Students
ADD COLUMN Phone VARCHAR(15);

9. Example 2 (Medium): Adding a constrained column

add_constrained.sql
ALTER TABLE Students
ADD COLUMN Email VARCHAR(50) UNIQUE;

10. Example 3 (Harder): Removing a column safely

drop_safe.sql
ALTER TABLE Students
DROP COLUMN Phone;
You generally cannot drop a PRIMARY KEY column while other things might depend on it, which is exactly why the sandbox above refuses it too, this mirrors how a real database behaves.

11. Example 4 (Hero level): Designing with constraints from day one

good_design.sql
CREATE TABLE Students (
    RollNo INT PRIMARY KEY,
    Name VARCHAR(50) NOT NULL,
    Email VARCHAR(50) UNIQUE,
    Section CHAR(1) DEFAULT 'A'
);

Notice all of this could have been written directly into the original CREATE TABLE from Part 1, ALTER TABLE exists for the real world, where you don't always get to plan everything perfectly from the very first day.

12. Common mistakes

  1. Adding NOT NULL to a column after rows already exist, without a DEFAULT, existing rows won't have a value, which can cause problems depending on the database.
  2. Confusing UNIQUE with PRIMARY KEY, remember, a table allows only one of the latter.
  3. Trying to drop a column other data depends on, like a primary key.
  4. Forgetting that constraints apply going forward, adding UNIQUE to a column that already has duplicate values in it will itself fail, the duplicates would need cleaning up first.

13. Practice tasks

  1. In the sandbox, add a column called Hostel with a DEFAULT value of 'No'.
  2. Try adding a second UNIQUE column, then try inserting a row that breaks only that constraint.
  3. Write out, on paper, a CREATE TABLE statement for a "Books" table with sensible NOT NULL and UNIQUE constraints of your own choosing.
  4. Challenge: explain, in your own words, why a Phone column probably should not be UNIQUE in a Students table, but an Email column probably should be. Think about families sharing one landline.

Constraints are what separates a table that merely stores data from one that actively protects it. Next in the rotation, we're back to Python for Part 6, functions. After that in DBMS, we'll finally connect two tables together properly with JOINs.

— Diwakar

← DBMS Part 3: INSERT, UPDATE, DELETE DBMS Part 5: JOINs (coming soon) →

Comments

Post a Comment