SQL Tutorial

🔍 Types of Constraints

SQL provides several types of constraints that can be applied to columns in a table:

• PRIMARY KEY - Ensures that each value in a column is unique and not NULL. It uniquely identifies each record in a table.
• FOREIGN KEY - Ensures that values in a column match values in another table's primary key, maintaining referential integrity.
• UNIQUE - Ensures all values in a column are unique but allows NULL values.
• CHECK - Ensures that all values in a column satisfy a specific condition.
• NOT NULL - Ensures that a column cannot have NULL values.
• DEFAULT - Provides a default value for a column when no value is specified.

🔑 Example of Constraints

Let’s take a look at an example of how to apply constraints to a table:

CREATE TABLE Employees (
  employee_id INT PRIMARY KEY,
  employee_name VARCHAR(100) NOT NULL,
  salary DECIMAL(10, 2) CHECK (salary > 0),
  department_id INT,
  FOREIGN KEY (department_id) REFERENCES Departments(department_id)
);
    

In this example:

• The PRIMARY KEY constraint ensures that employee_id is unique for each employee.
• The NOT NULL constraint ensures that the employee_name cannot be NULL.
• The CHECK constraint ensures that the salary is greater than zero.
• The FOREIGN KEY constraint establishes a relationship between the department_id and a column in the Departments table.

🔑 PRIMARY KEY Constraint

The PRIMARY KEY constraint uniquely identifies each record in a table. A primary key column cannot have duplicate or NULL values.

CREATE TABLE Students (
  student_id INT PRIMARY KEY,
  student_name VARCHAR(100)
);
    

Here, the student_id column is a primary key, ensuring each student has a unique ID.

🔗 FOREIGN KEY Constraint

The FOREIGN KEY constraint ensures that the value in a column matches one of the values in another table's primary key. It establishes a link between the tables and maintains referential integrity.

CREATE TABLE Orders (
  order_id INT PRIMARY KEY,
  order_date DATE,
  customer_id INT,
  FOREIGN KEY (customer_id) REFERENCES Customers(customer_id)
);
    

In this example, the customer_id column is a foreign key that references the customer_id column in the Customers table.

🚫 NOT NULL Constraint

The NOT NULL constraint ensures that a column cannot have NULL values. This is important when you want to ensure that every row in a column has a valid value.

CREATE TABLE Employees (
  employee_id INT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  department VARCHAR(50) NOT NULL
);
    

Here, the name and department columns cannot be NULL, ensuring that every employee has these details.

💡 REMEMBER:

• PRIMARY KEY ensures uniqueness and cannot have NULL values.
• FOREIGN KEY maintains referential integrity between tables.
• NOT NULL makes sure that important columns always contain data.
• CHECK lets you enforce data validation rules.
• UNIQUE allows uniqueness but accepts one NULL value per column.