Key Constraints

🎯 What are Key Constraints?

Key constraints ensure that certain attributes (columns) of a table hold unique and/or non-null values to correctly identify tuples (rows).

These constraints are usually enforced using Primary Keys, Candidate Keys, Unique Keys, and Foreign Keys.

🔍 Types of Key Constraints:

🏅 Primary Key Constraint

• Ensures that a column (or group of columns) uniquely identifies each row.

• Must be UNIQUE and NOT NULL

• Only one Primary Key per table

StudentID INT PRIMARY KEY

💎 Candidate Key Constraint

• Columns that could be the Primary Key because they uniquely identify records.

• Among these, one is selected as the Primary Key.

• The rest are called Alternate Keys

🔁 Alternate Key

• Candidate keys not chosen as Primary Key

• Still unique and minimal

🔐 Unique Key Constraint

• Ensures that a column (or combination) has unique values

• Can be NULL

• You can have multiple unique keys

Email VARCHAR(100) UNIQUE

🌐 Foreign Key Constraint

• A column in one table that references the Primary Key in another table.

• Maintains referential integrity

• Can be NULL if the relationship is optional

FOREIGN KEY (DeptID) REFERENCES Department(DeptID)

🧱 Why Are Key Constraints Important?

🧾 Example: Student Table with Key Constraints

CREATE TABLE Student (
  StudentID INT PRIMARY KEY,
  Name VARCHAR(50),
  Email VARCHAR(100) UNIQUE,
  DeptID INT,
  FOREIGN KEY (DeptID) REFERENCES Department(DeptID)
);

• StudentID → Primary Key (Unique & Not Null)

• Email → Unique Key

• DeptID → Foreign Key

✅ Summary Table