SQL Constraints Intro 🔒 📝

Welcome to our SQL Constraints Tutorial! In this lesson, we'll dive deep into understanding the role of constraints in databases, and learn about different types of SQL constraints. By the end, you'll be able to create robust and reliable databases for your projects. 🎯

Table of Contents 📝

Why SQL Constraints Matter?
Types of SQL Constraints
1. Primary Key Constraint
2. Unique Constraint
3. Foreign Key Constraint
4. Check Constraint
5. Default Constraint
Creating Constraints
Quiz: SQL Constraints

Why SQL Constraints Matter? 💡

Constraints play a crucial role in maintaining the integrity of your database. They ensure that the data entering your database adheres to specific rules, enhancing the overall quality and reliability of your data.

Types of SQL Constraints 📝

Primary Key Constraint 🔑

A primary key is a column or a set of columns in a table that uniquely identify each row. It is used to enforce the uniqueness of the data in the column or columns specified.

Example:

sql

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50)
);

In the example above, EmployeeID is the primary key column, ensuring that each employee in the table is uniquely identified.

Unique Constraint 🔒

A unique constraint guarantees that the values in a column or a set of columns are unique, but unlike the primary key, it can allow null values.

Example:

sql

CREATE TABLE Employees (
    EmployeeID INT,
    EmployeeEmail VARCHAR(100) UNIQUE,
    FirstName VARCHAR(50),
    LastName VARCHAR(50)
);

In this example, EmployeeEmail is a unique column, ensuring that no two employees have the same email address.

Foreign Key Constraint 🔗

A foreign key is a field in one table that refers to the primary key of another table. It ensures referential integrity by preventing the deletion or modification of data that would violate the foreign key's relationship with the primary key.

Example:

sql

CREATE TABLE Departments (
    DepartmentID INT PRIMARY KEY,
    DepartmentName VARCHAR(50)
);

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    DepartmentID INT,
    DepartmentID FOREIGN KEY REFERENCES Departments(DepartmentID)
);

In this example, DepartmentID is a foreign key that references the DepartmentID in the Departments table.

Check Constraint 🔒

A check constraint is used to verify that data in a column adheres to a specified condition.

Example:

sql

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    Age INT CHECK (Age >= 18 AND Age <= 65),
    FirstName VARCHAR(50),
    LastName VARCHAR(50)
);

In this example, the Age column must have a value between 18 and 65.

Default Constraint 💡

A default constraint assigns a default value to a column when no value is provided during an insert operation.

Example:

sql

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    HireDate DATE DEFAULT CURRENT_DATE,
    FirstName VARCHAR(50),
    LastName VARCHAR(50)
);

In this example, if no HireDate is provided during an insert, the current date will be used.

Creating Constraints 📝

You can create constraints when creating a table, or you can alter an existing table to add constraints.

sql

ALTER TABLE Employees ADD CONSTRAINT UK_EmployeeEmail UNIQUE (EmployeeEmail);

The above SQL statement adds a unique constraint to the EmployeeEmail column in the Employees table.

Quiz: SQL Constraints 📝

That's it for this lesson! In the next lesson, we'll dive deeper into each constraint type and see how to work with them in real-world scenarios. Happy learning! 🎯 🎉