Tutorial 5 - SQL Keys and Constraints

SQL constraints protect data quality. They make sure every row has an ID, and things like email addresses don’t accidentally repeat, so your database stays consistent, reliable, and easy to query. 

Primary Key 

• The primary key uniquely identifies each record. 
• A primary key column can’t be empty (NOT NULL) and can’t repeat values. 

Here, student_id makes sure no two students have the same ID. 

You can also insert data in students table and see the results by using the following commands:

INSERT INTO students (student_id, name) VALUES (1, 'Alice'), (2, 'Bob');

SELECT * FROM students;

Practice: Create Table Orders with the primary key as order_number, insert some data in it and see the results.

Foreign Key 

• It connects one table to another by referencing the primary key of the other table. 
• A foreign key is like saying: “this student belongs to this class.” 

Now, every class must be linked to some students that exists in the students table. 

You can also insert data in classes table and see the results by using the following commands:

INSERT INTO classes (class_id, class_name, student_id) VALUES (101, 'Mathematics', 1), (102, 'Physics', 2);

SELECT * FROM classes;

Practice: Create Table OrderItems that includes order_number from Orders table as foreign key, insert some data in it and see the results.

SQL UNIQUE Constraint 

UNIQUE ensures that no two rows have the same value in that column. 

This ensures that email will be unique for every row in the database and no two rows will have same email.

You can also insert data in users table and see the results by using the following commands:

INSERT INTO users (user_id, email) VALUES (1, 'alice@company.com'), (2, 'bob@company.com'), (3, 'charlie@company.com'), (4, 'david@company.com');

SELECT * FROM users;

Practice: Create Table persons with a field 'name' as VARCHAR(250) and Unique, insert some data and see the results.

SQL CHECK Constraint 

CHECK constraint makes sure a column’s values meet certain conditions. 

This shows that we have a check for the salary that it should always be more than zero. 

You can also insert data in paycheck table and see the results by using the following commands:

INSERT INTO paycheck (emp_id, amount) VALUES (1, 5200.00), (2, 4100.50), (3, 3650.75), (4, 2999.99), (5, 6500.00);

SELECT * FROM paycheck;

Practice: Create Table family with a field 'members' and with a check that they are greater than 2, insert some data and see the results.

SQL DEFAULT Constraint 

DEFAULT gives a column a value automatically when no value is provided. 

If you don’t enter a status, it will be set to “Pending”. 

You can also insert data in tasks table and see the results by using the following commands:

INSERT INTO tasks (task_id, status) VALUES (1, 'Pending'), (2, 'In Progress');

INSERT INTO tasks (task_id) VALUES (4);

SELECT * FROM tasks;

Practice: Create Table shirts with 'size' fields and keep the Default value as 'Medium', insert some data and see the results.

Common Errors Beginners Face 

1. Trying to insert duplicate values in a primary/unique column 
   Fix: Use new values or check before inserting. 
2. Foreign key error (e.g., adding a student to a class that doesn’t exist) 
   Fix: Insert the parent row first (the class), then the child (student). 
3. NOT NULL error (trying to insert without a required column) 
   Fix: Always provide the value or set a default. 

FAQs 

1. What is the difference between Primary Key and Unique constraint? 
A Primary Key enforces both uniqueness and non-null values for a column (or set of columns). A Unique constraint also enforces uniqueness but allows a single NULL value (depending on the database). 

2. Can a table have multiple Primary Keys? 
No. A table can have only one Primary Key, but that key can consist of multiple columns (called a composite key). 

3. What happens if you try to insert a record that violates a constraint? 
The database will reject the operation and return an error. Constraints act as rules that protect the data from being inconsistent, duplicated, or invalid. 

4. Are constraints mandatory in SQL? 
No. Constraints are optional, but they are strongly recommended. Without them, the database won’t enforce data integrity automatically, and it becomes the developer’s job to manually check for errors. 

5. What is the difference between Default and Check constraints? 

• Default provides an automatic value when none is supplied. 
• Check enforces a rule that values must satisfy. 
  Together, they can ensure that missing values are handled correctly and invalid values are rejected.