Constraints

SQL constraints are rules that enforce limits or conditions on the columns of a table. They ensure data integrity by preventing invalid data from being inserted into the database tables. Let's explore some common SQL constraints with examples:

 

NOT NULL Constraint

The NOT NULL constraint ensures that a column cannot contain NULL values. This is useful for columns where data is required and NULL is not acceptable.

Example:

Suppose we have a table students:

CREATE TABLE students ( student_id INT PRIMARY KEY, student_name VARCHAR(100) NOT NULL, date_of_birth DATE NOT NULL, email VARCHAR(100) );

In this example, student_name and date_of_birth columns cannot be NULL, ensuring every student record has these values.

 

UNIQUE Constraint

The UNIQUE constraint ensures that all values in a column (or a combination of columns) are unique across the table.

Example:

CREATE TABLE users ( user_id INT PRIMARY KEY, username VARCHAR(50) UNIQUE, email VARCHAR(100) UNIQUE );

In this example, Each username and email must be unique across all records in the users table.

 

PRIMARY KEY Constraint

The PRIMARY KEY constraint uniquely identifies each record in a table and ensures that it cannot contain NULL values.

Example:

CREATE TABLE products ( product_id INT PRIMARY KEY, product_name VARCHAR(100), price DECIMAL(10, 2) );

In this example, product_id uniquely identifies each product, and it cannot be NULL.

 

FOREIGN KEY Constraint

The FOREIGN KEY constraint establishes a relationship between two tables. It ensures referential integrity by enforcing a link between the data in the referencing table and the referenced table.

Example:

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, customer_id in the orders table is a foreign key that references the customer_id column in the customers table. It ensures that every customer_id in the orders table must exist in the customers table.

 

CHECK Constraint

The CHECK constraint specifies a condition that must be true for each row in a table.

Example:

CREATE TABLE employees ( emp_id INT PRIMARY KEY, emp_name VARCHAR(100), emp_age INT, emp_salary DECIMAL(10, 2), CHECK (emp_age >= 18) );

In this example, CHECK (emp_age >= 18) ensures that the emp_age column must be 18 years or older for every record in the employees table.

 

DEFAULT Constraint

A DEFAULT constraint in SQL is used to specify a default value for a column when no explicit value is specified during an INSERT operation. This ensures that the column always has a value, even if one is not provided explicitly.

Example:

CREATE TABLE employees ( emp_id INT PRIMARY KEY, emp_name VARCHAR(100) NOT NULL, emp_dept VARCHAR(50), emp_salary DECIMAL(10, 2), emp_status VARCHAR(20) DEFAULT 'Active' );

In this example, The employees table has several columns (emp_id, emp_name, emp_dept, emp_salary, emp_status). The emp_status column has a DEFAULT constraint specified as DEFAULT 'Active'.

 

SQL constraints are essential for maintaining data integrity and enforcing business rules within a database. They help ensure that data is accurate, reliable, and consistent, which is crucial for the proper functioning of applications and systems that rely on the database.