Integrity constraints in SQL

Integrity constraints in SQL are rules that are applied to ensure the accuracy and consistency of the data stored in a database. There are four types of integrity constraints in SQL:

NOT NULL Constraint: This constraint ensures that a column cannot have a NULL value. For example, the following query creates a table with a NOT NULL constraint on the 'id' column:

Example:

CREATE TABLE employee (
   id INT NOT NULL,
   name VARCHAR(50),
   age INT
);
 

UNIQUE Constraint: This constraint ensures that a column or a group of columns have unique values in each row. For example, the following query creates a table with a UNIQUE constraint on the 'email' column:

Example:

CREATE TABLE customer (
   id INT,
   name VARCHAR(50),
   email VARCHAR(50) UNIQUE
);
 

PRIMARY KEY Constraint: This constraint ensures that a column or a group of columns uniquely identifies each row in a table. For example, the following query creates a table with a primary key on the 'id' column:

Example:

 CREATE TABLE product (
   id INT PRIMARY KEY,
   name VARCHAR(50),
   price DECIMAL(8,2)
);

FOREIGN KEY Constraint: This constraint ensures that the values in a column or a group of columns of one table match the values in a column or a group of columns of another table. For example, the following query creates two tables with a foreign key constraint on the 'category_id' column of the 'product' table:

Example:

CREATE TABLE category (
   id INT PRIMARY KEY,
   name VARCHAR(50)
);

CREATE TABLE product (
   id INT PRIMARY KEY,
   name VARCHAR(50),
   price DECIMAL(8,2),
   category_id INT,
   FOREIGN KEY (category_id) REFERENCES category(id)
);
 

The above query ensures that the 'category_id' column of the 'product' table matches the 'id' column of the 'category' table.

CHECK Constraint 

The SQL CHECK constraint is used to restrict the values that can be inserted into a column in a table. It allows you to specify a condition that must be satisfied for the data to be valid. If the condition is not met, an error is returned and the data is not inserted into the table.

The syntax for the CHECK constraint is as follows:

CREATE TABLE table_name (
    column1 datatype CHECK (condition),
    column2 datatype CHECK (condition),
    ...
);

Here, the CHECK keyword is followed by a condition in parentheses that specifies the allowable values for the column. The condition can use operators such as =, >, <, >=, <=, <>, BETWEEN, LIKE, IN, and IS NULL, as well as logical operators such as AND, OR, and NOT.

Let's look at an example. Suppose we have a table called employees with columns id, name, age, and salary. We want to ensure that the age column only accepts values between 18 and 65. We can add a CHECK constraint to the age column as follows:

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    age INT CHECK (age BETWEEN 18 AND 65),
    salary DECIMAL(10,2)
);

Now, any attempt to insert a row into the employees table with an age value outside the range of 18 to 65 will fail with an error.

Note that the CHECK constraint can also be added to an existing table using the ALTER TABLE statement. For example, to add a CHECK constraint to the age column of the employees table, you can use the following statement:

ALTER TABLE employees
ADD CONSTRAINT age_check CHECK (age BETWEEN 18 AND 65);
 

DEFAULT Constraint

In SQL, the DEFAULT constraint is used to specify a default value for a column when no value is specified during an INSERT operation. The default value is automatically assigned to the column if a value is not provided.

The syntax for adding a DEFAULT constraint to a column when creating a table is as follows:

CREATE TABLE table_name (
    column1 datatype DEFAULT default_value,
    column2 datatype DEFAULT default_value,
    ...
);
 

Here, the DEFAULT keyword is followed by the default value to be assigned to the column if no value is specified during an INSERT operation.

Let's look at an example. Suppose we have a table called orders with columns order_id, customer_id, and order_date. We want to specify a default value of the current date for the order_date column when no value is provided. We can add a DEFAULT constraint to the order_date column as follows:

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE DEFAULT CURRENT_DATE
);

Now, any INSERT operation that does not provide a value for the order_date column will automatically use the current date as the default value.

Note that the DEFAULT constraint can also be added to an existing table using the ALTER TABLE statement. For example, to add a DEFAULT constraint to the order_date column of the orders table, you can use the following statement:

 ALTER TABLE orders
ALTER COLUMN order_date SET DEFAULT CURRENT_DATE;

These constraints help maintain data consistency and accuracy in a database.