8️⃣ 🛑 MySQL Constraints & Keys – PRIMARY, FOREIGN, UNIQUE, NOT NULL

In MySQL, constraints and keys are critical tools for maintaining data validity, enforcing relationships, and preserving integrity in your database schema. They help ensure that only valid data enters your tables and that your application logic remains consistent at the database level.

---

🧲 Introduction – Why Use Constraints in MySQL?

Constraints are rules applied to columns and tables that restrict the kind of data allowed. They help:

• ✅ Validate data (e.g., NOT NULL)
• 🔁 Enforce relationships (e.g., FOREIGN KEY)
• 🔐 Prevent duplicates (e.g., UNIQUE, PRIMARY KEY)

Without constraints, databases can become unreliable, inconsistent, and prone to bugs.

---

📘 Topics Covered

🔢 Topic	📄 Description
🚫 MySQL Constraints Overview	Covers NOT NULL, DEFAULT, CHECK constraints for data validation
🔐 MySQL Keys	Covers PRIMARY KEY, UNIQUE, and FOREIGN KEY usage and syntax
🧩 MySQL Composite & Alternate Keys	Describes multi-column and alternate key definitions and usage

---

🚫 1. NOT NULL – Prevent Empty Values

Ensures that a column must contain a value.

Syntax:

column_name data_type NOT NULL

Example:

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(50) NOT NULL,
  email VARCHAR(100)
);

🔎 Explanation: username NOT NULL ensures every record has a username.

---

🔑 2. PRIMARY KEY – Unique + Not Null

Uniquely identifies each record. Each table can only have one primary key.

Syntax:

PRIMARY KEY (column1 [, column2])

Single Column Example:

CREATE TABLE employees (
  emp_id INT PRIMARY KEY,
  name VARCHAR(100)
);

Composite Key Example:

CREATE TABLE course_enrollment (
  student_id INT,
  course_id INT,
  PRIMARY KEY (student_id, course_id)
);

---

🔐 3. FOREIGN KEY – Maintain Relationships Between Tables

Used to link tables and enforce valid references.

Syntax:

FOREIGN KEY (column) REFERENCES parent_table(column)
[ON DELETE action] [ON UPDATE action]

Example:

CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  customer_id INT,
  FOREIGN KEY (customer_id) REFERENCES customers(id)
);

With Cascading:

FOREIGN KEY (customer_id) REFERENCES customers(id)
ON DELETE CASCADE
ON UPDATE CASCADE

---

🆔 4. UNIQUE – Enforce Non-Duplicate Values

Prevents duplicate entries in one or more columns.

Syntax:

UNIQUE (column1 [, column2])

Example:

CREATE TABLE employees (
  email VARCHAR(100) UNIQUE,
  phone VARCHAR(15)
);

⚠️ Allows multiple NULL values unless otherwise restricted.

---

🧩 5. Composite & Alternate Keys

• Composite Key: Combines multiple columns as a unique identifier.
• Alternate Key: A candidate key that’s not used as the primary key, enforced using UNIQUE.

---

📘 Constraints Summary Table

Constraint	Description	Common Use Case
NOT NULL	Disallows null values	Mandatory fields
PRIMARY KEY	Unique + Not Null	Row identifiers
UNIQUE	All values must be distinct	Emails, usernames
FOREIGN KEY	Reference another table’s column	Joins and relational enforcement

---

🧱 Add Constraints to Existing Tables

NOT NULL:

ALTER TABLE users MODIFY username VARCHAR(50) NOT NULL;

UNIQUE:

ALTER TABLE users ADD UNIQUE (email);

FOREIGN KEY:

ALTER TABLE orders
ADD CONSTRAINT fk_customer
FOREIGN KEY (customer_id) REFERENCES customers(id);

Drop Constraint:

ALTER TABLE users DROP INDEX email;
ALTER TABLE orders DROP FOREIGN KEY fk_customer;

---

🧪 Constraint Violations – What Causes Errors?

Violation Type	Example
Insert NULL into NOT NULL	INSERT INTO users (username) VALUES (NULL);
Duplicate PRIMARY KEY	Inserting same ID twice
Foreign key mismatch	Referencing a non-existent ID in a child table
Duplicate UNIQUE value	Repeating the same email address

---

🚀 Real-World Use Cases

Scenario	Constraint Used	Why It’s Important
Track orders by customer	FOREIGN KEY	Link orders to valid customers
Ensure usernames are unique	UNIQUE	Prevent duplicate registrations
Disallow empty passwords	NOT NULL	Enforce authentication security
Identify each invoice	PRIMARY KEY	Guarantee unique records

---

📘 Best Practices

✅ Tip	💡 Reason
Use NOT NULL + UNIQUE for logins	Validates data and avoids duplicates
Use CASCADE cautiously	Prevents unintended data loss
Name your FOREIGN KEY constraints	Easier maintenance and debugging
Prefer surrogate keys	Better join performance than composite keys
Test constraint rules in staging	Avoid production-level data issues

---

📌 Summary – Recap & Next Steps

MySQL constraints help secure your database from invalid data and ensure relational integrity across tables. By applying proper constraints and keys, you create a strong, reliable foundation for your data systems.

🔍 Key Takeaways

• Use NOT NULL to enforce required data
• Define PRIMARY KEY for unique row identity
• Use UNIQUE to avoid repeated data
• Leverage FOREIGN KEY for valid table relations

⚙️ Real-World Relevance
Constraints are vital for data quality and are used in CRMs, accounting systems, inventory apps, and any multi-table environment that requires accurate and linked data.

---

❓ FAQ – MySQL Constraints & Keys

❓ Can I define more than one PRIMARY KEY in a table?

❌ No. Only one PRIMARY KEY is allowed, but it can be composite (multi-column).

---

❓ Can UNIQUE allow NULL values?

✅ Yes, it can. Multiple rows can have NULLs in a UNIQUE column.

---

❓ How do I drop a FOREIGN KEY?

Use the constraint name:

ALTER TABLE orders DROP FOREIGN KEY fk_customer;

---

❓ PRIMARY KEY vs. UNIQUE?

• PRIMARY KEY = UNIQUE + NOT NULL
• UNIQUE allows multiple NULLs and is not necessarily the main identifier.

---

❓ Can FOREIGN KEY reference non-primary column?

✅ Yes, as long as it references a UNIQUE or PRIMARY key with a matching type.

---

Share Now :