SQL CHECK Constraint
SQL CHECK Constraint
The SQL CHECK
constraint is used to limit the values that can be placed in a column. This command is essential for enforcing data integrity and ensuring that the data in a database meets specific criteria.
Syntax
-- To create a CHECK constraint when creating a table
CREATE TABLE table_name (
column1 datatype CHECK (condition),
column2 datatype,
...
);
-- To add a CHECK constraint to an existing table
ALTER TABLE table_name
ADD CONSTRAINT constraint_name CHECK (condition);
CREATE TABLE
: This is the SQL keyword used to create a new table.ALTER TABLE
: This is the SQL keyword used to modify an existing table.CHECK
: This is the SQL keyword used to define a CHECK constraint.column1, column2, ...
: These specify the names of the columns in the table.datatype
: This specifies the type of data the column can hold, such as INTEGER, VARCHAR, DATE, etc.condition
: This specifies the condition that the data must meet to be valid.table_name
: This specifies the name of the table to create or modify.constraint_name
: This specifies the name of the CHECK constraint.
Example
Let's go through a complete example that includes creating a database, creating a table with a CHECK constraint, and inserting data into the table.
Step 1: Creating a Database
This step involves creating a new database named example_db
.
CREATE DATABASE example_db;
In this example, we create a database named example_db
.
Step 2: Creating a Table with a CHECK Constraint
In this step, we create a table named employees
within the previously created database, defining a CHECK constraint on the salary
column to ensure it is greater than zero.
USE example_db;
CREATE TABLE employees (
id INT AUTO_INCREMENT PRIMARY KEY,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
email VARCHAR(100),
hire_date DATE,
salary DECIMAL(10, 2) CHECK (salary > 0)
);
Here, we define the employees
table with columns for id
, first_name
, last_name
, email
, hire_date
, and salary
. The salary
column has a CHECK constraint to ensure it is greater than zero.
Step 3: Inserting Data into the Table
This step involves inserting some sample data into the employees
table.
INSERT INTO employees (first_name, last_name, email, hire_date, salary) VALUES ('John', 'Doe', 'john.doe@example.com', '2023-01-01', 50000.00);
INSERT INTO employees (first_name, last_name, email, hire_date, salary) VALUES ('Jane', 'Smith', 'jane.smith@example.com', '2023-02-01', 60000.00);
INSERT INTO employees (first_name, last_name, email, hire_date, salary) VALUES ('Alice', 'Johnson', 'alice.johnson@example.com', '2023-03-01', 70000.00);
INSERT INTO employees (first_name, last_name, email, hire_date, salary) VALUES ('Bob', 'Brown', 'bob.brown@example.com', '2023-04-01', 0.00);
The first three insertions will be successful because the salary
values are greater than zero. The fourth insertion will fail because the salary
value is not greater than zero, violating the CHECK constraint.
Step 4: Adding a CHECK Constraint to an Existing Table
This step involves adding a CHECK constraint to an existing table named departments
to ensure that the budget
column is greater than 1000.
ALTER TABLE departments
ADD CONSTRAINT chk_budget CHECK (budget > 1000);
This command adds a CHECK constraint to the budget
column of the departments
table, ensuring that the budget is always greater than 1000.