SQL Constraints

by

The SQL Constraints

The SQL constraints are used to specify rules or conditions applied to table data. These sql constraints help to preserve a database’s data accuracy, consistency, and reliability.

You can use ALTER TABLE to add sql constraints such as primary keys, foreign keys, unique constraints, and check constraints to an existing table.

SQL Constraints

What is SQL Constraints?

  • SQL constraints are used to define rules for table data.
  • Constraints are used to restrict the kind of data that may be entered into a table.
  • Consequently, this ensures that the data included in the table is correct and trustworthy.
  • In certain cases, constraints may be implemented at the table level or the column level.
  • Table-level restrictions apply to the whole table, while column-level constraints apply to a single column.

ALTER TABLE customers
ADD CONSTRAINT my_customer_id PRIMARY KEY (customer_id);

Dropping a sql constraint:

To remove a constraint from a table, you can use the DROP CONSTRAINT clause.

ALTER TABLE employees
DROP CONSTRAINT your_department;

Create SQL Constraints

The Constraints can be specified when the table is created with the CREATE TABLE statement
When a table is formed using the CREATE TABLE statement, the constraints can be specified.

Syntax to Create SQL Constraints

CREATE TABLE table_name (
column1 datatype constraint,
column2 datatype constraint,
column3 datatype constraint,
column4 datatype constraint,
column5 datatype constraint,
….
);

The following Common SQL Constraints are used in sql. We have explained with some examples to better understand of these sql constraints:

  • PRIMARY KEY
  • FOREIGN KEY
  • UNIQUE
  • NOT NULL
  • CHECK
  • DEFAULT

PRIMARY KEY Constraint:

Each record in a table is uniquely identified by a PRIMARY KEY constraint.
It ensures that a column or a combination of columns has unique values and cannot contain NULL values.

Example:

CREATE TABLE students (
student_id INT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50)
);

FOREIGN KEY Constraint:

A FOREIGN KEY constraint establishes a link between two tables, ensuring referential integrity.
It enforces that values in a column match values in another table’s primary key column.

Example:

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

UNIQUE Constraint:

The UNIQUE constraint ensures that all values in a column or a set of columns are unique.
Example:

CREATE TABLE employees (
employee_id INT PRIMARY KEY,
email VARCHAR(100) UNIQUE,
department_id INT
);

NOT NULL Constraint:

The NOT NULL constraint ensures that a column must have a value, and it cannot contain NULL.

Example:

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

CHECK Constraint:

The CHECK constraint specifies a condition that must be met for the data in a column.

Example:

CREATE TABLE employees (
employee_id INT PRIMARY KEY,
salary DECIMAL(10, 2),
CHECK (salary >= 30000)
);

DEFAULT Constraint:

The DEFAULT constraint is used to assign a default value to a column in a table in sql. When a new row is inserted and no value is specified for a column with a DEFAULT constraint, the default value defined for that column will be automatically used.

Syntax for Adding DEFAULT Constraint:

ALTER TABLE table_name
ADD CONSTRAINT constraint_name DEFAULT default_value FOR column_name;

Consider a table named employees where you want to add a DEFAULT constraint to the department column with a default value of ‘HR’.

Example:

ALTER TABLE employees
ADD CONSTRAINT default_department DEFAULT ‘HR’ FOR department;

If you insert a row into the employees table without specifying the department, the DEFAULT value ‘HR’ will be assigned to the department column.

INSERT INTO employees (name, salary) VALUES (‘Jon Doa’, 50000);
Insertion with Specifying the DEFAULT Column:

If you explicitly provide a value for the department, that value will be used instead of the default.

INSERT INTO employees (name, salary, department) VALUES (‘Jan Smooth’, 60000, ‘IT’);

Benefits:
DEFAULT constraints ensure that columns have valid default values, preventing NULL values or ensuring a specific value in the absence of explicit input.

These limitations aid in preserving the consistency and integrity of the data in your database.

They minimize the possibility of data mistakes and inconsistencies by ensuring that the data recorded in tables complies with particular rules and relationships.

Leave a Comment