← SQL roadmap

Topic 12 of 52 · Intermediate

ALTER TABLE – Add & Drop Constraints

Add or remove PRIMARY KEY, UNIQUE, CHECK, FOREIGN KEY and DEFAULT rules on an existing table.

01ALTER TABLE – Add & Drop Constraints

Module 02 – Database & Table Management (DDL)

02What is it?

Constraints are rules the database enforces on every insert and update. ALTER TABLE lets you add them to a table that already exists — ADD CONSTRAINT — or remove them — DROP CONSTRAINT / DROP PRIMARY KEY / DROP FOREIGN KEY / DROP INDEX.

Defaults are changed with ALTER COLUMN col SET DEFAULT value or DROP DEFAULT.

03Why do we need it?

Tables are often created quickly (especially staging tables) and hardened later: once data is clean you add a UNIQUE email rule or a CHECK that salary is positive.

Naming constraints (CONSTRAINT uq_emp_email ...) makes error messages readable and lets you drop exactly that rule later.

04Syntax

ALTER TABLE t ADD [CONSTRAINT name] PRIMARY KEY (col, ...);
ALTER TABLE t ADD [CONSTRAINT name] UNIQUE (col, ...);
ALTER TABLE t ADD [CONSTRAINT name] CHECK (condition);
ALTER TABLE t ADD [CONSTRAINT name] FOREIGN KEY (col) REFERENCES parent(col);
ALTER TABLE t ALTER [COLUMN] col SET DEFAULT value;   -- or DROP DEFAULT

ALTER TABLE t DROP PRIMARY KEY;
ALTER TABLE t DROP CONSTRAINT name;      -- MySQL 8.0.19+ (any type)
ALTER TABLE t DROP INDEX name;           -- UNIQUE constraints
ALTER TABLE t DROP FOREIGN KEY name;
ALTER TABLE t DROP CHECK name;

MySQL 8.0.16+ enforces CHECK; DROP CONSTRAINT for any type needs 8.0.19+. Older versions use DROP INDEX / DROP FOREIGN KEY / DROP CHECK.

05Parameter explanation

CONSTRAINT name
Optional but recommended label, e.g. chk_salary_positive. Needed to drop the rule later.
PRIMARY KEY (cols)
Unique + NOT NULL identity of each row. Only one per table.
UNIQUE (cols)
No two rows may share the value(s). NULLs are allowed (many times).
CHECK (condition)
Each row must satisfy the condition (enforced in MySQL 8.0.16+).
FOREIGN KEY ... REFERENCES
Value must exist in the parent table's key column.

06Sample database

Every example and exercise below starts from a fresh copy of this data.

CREATE TABLE departments (
  dept_id   INT PRIMARY KEY,
  dept_name VARCHAR(50) NOT NULL
);
CREATE TABLE employees (
  emp_id    INT PRIMARY KEY,
  name      VARCHAR(60) NOT NULL,
  dept_id   INT,
  salary    DECIMAL(10,2),
  hire_date DATE
);
INSERT INTO departments VALUES (10,'Engineering'),(20,'Sales'),(30,'HR');
INSERT INTO employees VALUES
 (1,'Asha',10,85000,'2021-04-12'),
 (2,'Ravi',20,52000,'2022-01-03'),
 (3,'Meena',10,97000,'2019-09-20'),
 (4,'Karan',30,46000,'2023-06-15'),
 (5,'Divya',20,61000,'2020-11-30');
CREATE TABLE contacts (emp_id INT, email VARCHAR(80), phone VARCHAR(15));
INSERT INTO contacts VALUES (1,'asha@corp.in','9000000001'),(2,'ravi@corp.in','9000000002');

07Basic coding example

Basic example · runs on the real engine

08Step-by-step execution

  1. 1.MySQL scans contacts to confirm no two rows already share an email.
  2. 2.It creates a unique index named uq_contacts_email to enforce the rule.
  3. 3.From now on, inserting a duplicate email fails with 'Duplicate entry'.
  4. 4.SHOW INDEX FROM contacts lists the new unique key.

09Before and after output

Generated live by running the basic example against the sample database.

Running on the SQL engine…

10Intermediate coding example

Intermediate example

11Real-world business scenario

After the first full load of a customer dimension, the team adds UNIQUE (customer_id, source_system) so a buggy re-run can never insert duplicates.

Before a bulk reload, engineers temporarily drop a foreign key, load parent and child in any order, then add the foreign key back — which re-validates all rows.

12Common mistakes

ALTER TABLE contacts ADD PRIMARY KEY (emp_id); -- table already has a PK

A table can have only one primary key ('Multiple primary key defined').

ALTER TABLE contacts DROP PRIMARY KEY, ADD PRIMARY KEY (emp_id);

ALTER TABLE contacts ADD UNIQUE (email); -- duplicates exist

Existing duplicate emails make the new rule fail immediately.

Find them with GROUP BY email HAVING COUNT(*) > 1, clean them, then add the constraint.

ALTER TABLE contacts DROP CONSTRAINT email;

You drop a constraint by its name, not the column name.

ALTER TABLE contacts DROP CONSTRAINT uq_contacts_email;

13Important comparisons

ConstraintAddDrop (MySQL)
PRIMARY KEYADD PRIMARY KEY (c)DROP PRIMARY KEY
UNIQUEADD CONSTRAINT n UNIQUE (c)DROP INDEX n
CHECKADD CONSTRAINT n CHECK (...)DROP CHECK n
FOREIGN KEYADD CONSTRAINT n FOREIGN KEY ...DROP FOREIGN KEY n
DEFAULTALTER COLUMN c SET DEFAULT vALTER COLUMN c DROP DEFAULT

14Practical exercises

Beginner1. Make contacts.phone unique with constraint name uq_phone.

Reveal solution (15)
Solution · run it

A named unique constraint is stored as a unique index.

Beginner2. Give contacts.email the default 'pending@corp.in'.

Reveal solution (15)
Solution · run it

Only the default changes; existing rows stay.

Beginner3. Add a primary key on contacts.emp_id.

Reveal solution (15)
Solution · run it

DESCRIBE shows PRI in the Key column.

Intermediate4. Add CHECK chk_salary_pos so employees.salary must be > 0, then show the definition.

Reveal solution (15)
Solution · run it

Every existing row already satisfies the check, so it succeeds.

Intermediate5. Add the unique email constraint, then try a duplicate email. What error do you see?

Reveal solution (15)
Solution · run it

The insert fails: a duplicate entry violates uq_email.

Intermediate6. Link employees.dept_id to departments with fk_emp_dept.

Reveal solution (15)
Solution · run it

All current dept_ids exist in departments.

Advanced7. Add uq_email, then remove it again, then insert a duplicate successfully.

Reveal solution (15)
Solution · run it

Dropping the index removes the rule.

Advanced8. Add CHECK chk_phone so phone has at least 10 characters, then drop it by name.

Reveal solution (15)
Solution · run it

DROP CONSTRAINT works for any named constraint in MySQL 8.0.19+.

16Key takeaways

  • Always name constraints — you need the name to drop them.
  • Adding a constraint validates existing rows first; clean data before hardening.
  • One PRIMARY KEY per table; UNIQUE can be many.
  • UNIQUE constraints are implemented as unique indexes.

17Knowledge check

1. How many PRIMARY KEY constraints can a table have?

2. What do you need to drop a CHECK constraint?

3. Adding UNIQUE (email) fails. Most likely?

Interview questions

UNIQUE vs PRIMARY KEY?

Both prevent duplicates. A table has one PRIMARY KEY, its columns are NOT NULL and it is the clustered index in InnoDB. You can have many UNIQUE keys and they allow NULLs.

Why would you drop a foreign key before a bulk load?

To load parent and child files in any order and speed up inserts. Re-adding the FK afterwards validates all rows, so integrity is still guaranteed.

Practise SQL with the AI mentor

Lesson test

Take an 8-question test on “ALTER TABLE – Add & Drop Constraints” — concepts, output prediction, bug spotting and interview questions.

Can't find a SQL topic you need for exams or placements? Request it and we'll add it.