SQL Constraints
- SQL Constraints are the principles that are applied to table columns to confirm data integrity and uniformity within a relational database.
- Those laws make sure that the data maintained in a database is in line with particular standards and that relations between tables are secured.
- SQL constraints can be initially applied at the time of table creation or altered afterwards, and they aid in the accuracy, reliability, and security of the database.
- SQL constraints are the most important things in databases that developers should keep in mind. Each one of them plays a different role. It will help you get a clearer understanding by providing examples about how SQL Constraints work.
1. NOT NULL Constraint
- The NOT NULL constraint is the one that makes sure a column has a value for it and cannot be NULL.
- This restriction is a condition that is met whenever you need to make sure of the fact that each row has a value in a given column.
- For a column that has a NOT NULL applied to it, it cannot remain empty when inserting or updating an existing row.
- Syntax:
CREATE TABLE employees (
employee_id INT NOT NULL,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50)
);
- Example:
INSERT INTO employees (employee_id, first_name, last_name)
VALUES (1, 'John', 'Doe');
- So, in this situation, the employee_id and first_name columns should not be null columns, and any effort to insert a record without bringing a value to these columns will cause an error.
2. UNIQUE Constraint
- The UNIQUE constraint is the one that makes sure column values are all different.
- The primary key, which can also guarantee no duplicates, differs from a column with the USE NULL constraint, as it can accept NULL values (but only one NULL per column).
- This is a rather useful feature when you seek to avoid the presence of the duplicate data in the table but do not need the column to be a primary key.
- Syntax:
CREATE TABLE employees (
employee_id INT NOT NULL UNIQUE,
email VARCHAR(100) UNIQUE
);
- Example:
INSERT INTO employees (employee_id, email)
VALUES (1, 'john.doe@example.com'),
(2, 'jane.smith@example.com');
- The way these employee_id and email will be unique can be both displayed. Searching for the employee” will be returned the first time and it is very likely that a duplicate will be found but an error would be instead created.
3. PRIMARY KEY Constraint
- The PRIMARY KEY constraint is a column that, when combined with the other columns in a table, collectively forms a group of columns with each row unique.
- A column with a primary key cannot contain NULL values and must have unique values.
- Each table can have only one primary key, which may have one or more columns to form the composite primary key.
- Syntax:
CREATE TABLE employees (
employee_id INT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50)
);
- Example:
INSERT INTO employees (employee_id, first_name, last_name)
VALUES (1, 'John', 'Doe');
- For this case, the employee_id is the primary key. This means that no two people can have the same employee_id, and it cannot be null.
4. FOREIGN KEY Constraint
- The FOREIGN KEY constraint is utilized to associate two tables.
- It safeguards that the values in the foreign key column of one table must correspond to the values in the reference column of another table.
- This constraint is necessary for the maintenance of referential integrity with the parent table or master table.
- Syntax:
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT,
FOREIGN KEY (customer_id) REFERENCES customers (customer_id)
);
- Example:
CREATE TABLE customers (
customer_id INT PRIMARY KEY,
name VARCHAR(100)
);
INSERT INTO customers (customer_id, name)
VALUES (1, 'John Doe');
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT,
FOREIGN KEY (customer_id) REFERENCES customers (customer_id)
);
INSERT INTO orders (order_id, customer_id)
VALUES (101, 1);
- Over here, the orders table has a foreign key constraint that references the customer_id column in the customers table. Consequently, this ensures that every customer_id in the orders table will be found in the customers database.
5. CHECK Constraint
- The CHECK constraint makes it possible to provide a condition for the values in a column.
- Essentially, it permits only valid data which satisfies a certain condition to be entered into the table.
- Thus, you can be sure if an employee’s age is between 18 and 65 it was enforced.
- Syntax:
CREATE TABLE employees (
employee_id INT PRIMARY KEY,
first_name VARCHAR(50),
age INT CHECK (age >= 18 AND age <= 65)
);
- Example:
INSERT INTO employees (employee_id, first_name, age)
VALUES (1, 'John', 25);
- In the given example, the age column must contain values between 18 and 65. Any attempt to insert a value outside this range will lead to an error.
6. DEFAULT Constraint
- The DEFAULT constraint sets the column’s default value when no value is specified during record insertion.
- Thus, it is beneficial to columns with an expected common default value, like a status column which defaults to ‘Active’ or a created_at column which should automatically be set to the current date.
- Syntax:
CREATE TABLE employees (
employee_id INT PRIMARY KEY,
first_name VARCHAR(50),
hire_date DATE DEFAULT CURRENT_DATE
);
- Example:
INSERT INTO employees (employee_id, first_name)
VALUES (1, 'John');
- In this example, the hire_date column will take the current date by default due to the DEFAULT CURRENT_DATE constraint. Thus, even if the user does not set a value for hire date, the system sets it automatically.
7. INDEX Constraint
- Although an INDEX is not a technical constraint, it is usually associated with the improved performance of a query that is constrained.
- Indexes have been popular for many years due to speeding up rows’ retrieval based on the columns of the values.
- A proper index is one of many that could be defined for a table, which is particularly helpful for tables with high search or joins frequency though.
- Syntax:
CREATE INDEX index_name
ON table_name (column_name);
- Example:
CREATE INDEX idx_employee_name
ON employees (first_name, last_name);
- The query will create an index on the first_name and the last_name columns of the employees table. This index improves the queries that are related to the last_name and first_name columns even in cases where filtering or sorting has to be applied to these columns.
8. AUTO_INCREMENT (or SERIAL in PostgreSQL)
- The AUTO_INCREMENT (in MySQL) or SERIAL (in PostgreSQL) constraint automatically drafts an exclusive number for a new statement.
- It is instead very practical for the primary key columns, because every new record gets a new identifier and from the aspect of users, there is no need to put them in manually.
- Syntax (MySQL):
CREATE TABLE employees (
employee_id INT AUTO_INCREMENT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50)
);
- Example (MySQL):
INSERT INTO employees (first_name, last_name)
VALUES ('John', 'Doe');
- The employee_id column will automatically increment for each new row, starting from 1 by default.