SQL Constraints – Tutorial with Examples

SQL Constraints are used to specify rules and restrictions on the data that can be stored in a database. Constraints are used to ensure data integrity, which helps to maintain the accuracy and consistency of the data in a database. The constraints can be specified while creating a table or can be added later using the ALTER TABLE statement.

Types of Constraints

There are several types of constraints in SQL, including:

• Primary Key Constraint – The primary key is a unique identifier for each record in a table. It is used to enforce the uniqueness of the values in a column or set of columns. A table can have only one primary key.
• Foreign Key Constraint – The foreign key is used to establish a relationship between two tables. It is used to enforce referential integrity, which ensures that a value in a column exists in another table.
• Not Null Constraint – The not null constraint is used to ensure that a column cannot have a null value. This helps to ensure that a value is always entered for that column.
• Unique Constraint – The unique constraint is used to ensure that the values in a column are unique. It is similar to the primary key constraint, but a table can have multiple unique constraints.
• Check Constraint – The check constraint is used to validate the data that is entered into a column. It allows you to specify a condition that the data must meet in order to be entered into the column.

Creating Constraints

Constraints can be specified while creating a table using the CREATE TABLE statement or can be added later using the ALTER TABLE statement. Here are some examples of creating constraints:

Primary Key Constraint

CREATE TABLE customers (
  customer_id INT PRIMARY KEY,
  customer_name VARCHAR(255),
  address VARCHAR(255)
);

Foreign Key Constraint

CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  customer_id INT,
  order_date DATE,
  FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

Not Null Constraint

CREATE TABLE products (
  product_id INT PRIMARY KEY,
  product_name VARCHAR(255) NOT NULL,
  price DECIMAL NOT NULL
);

Unique Constraint

CREATE TABLE employees (
  employee_id INT PRIMARY KEY,
  employee_name VARCHAR(255),
  email VARCHAR(255) UNIQUE
);

Check Constraint

CREATE TABLE sales (
  sale_id INT PRIMARY KEY,
  product_id INT,
  sale_date DATE,
  quantity INT,
  CHECK (quantity >= 0)
);

In the above examples, we are specifying the constraints while creating the table using the CREATE TABLE statement. The constraints are added to the appropriate columns and ensure that the data stored in the database is valid and meets the specified conditions.

Enforcing Constraints

Constraints are enforced when data is inserted, updated, or deleted from a table. If a constraint is violated, the database management system will reject the change and an error message will be displayed. This helps to maintain the accuracy and consistency of the data in the database and prevent invalid data from being stored.

It is also possible to disable and enable constraints using the ALTER TABLE statement. This can be useful when performing bulk operations, such as loading data into a table, where it may not be necessary to enforce constraints during the process. However, it is important to enable constraints again once the operation is complete to ensure the data remains valid and accurate.

Conclusion

SQL Constraints are an important aspect of database management, helping to ensure the accuracy and consistency of the data stored in a database. By specifying constraints, you can enforce rules and restrictions on the data, and prevent invalid data from being stored. Constraints are enforced when data is inserted, updated, or deleted from a table and can be added, disabled, and enabled using the appropriate SQL statements.