SQL Check Constraint
Introduction to SQL Check Constraint
SQL check constraints are rules defined within a database schema to enforce specific conditions on the data stored in a table. These constraints help maintain data integrity by limiting the values that can be inserted or updated in a column based on predefined criteria.
In this article, we will explore the various heck Constraints aspects of the SQL , its syntax, usage of wildcard characters, performance considerations, best practices, and real-world use cases.
What is Check Constraint In SQL?
A CHECK constraint in SQL is a type of constraint that is used to specify a condition that must be satisfied for the data in a column or a set of columns within a table. This constraint ensures that data entered into a table meets certain criteria or business rules, maintaining the integrity of the data.
Syntax of the SQL Check Constraints:
- The syntax for creating a Check Constraints in SQL typically follows the CREATE TABLE statement.
CREATE TABLE TableName (
column1 datatype CONSTRAINT constraint_name CHECK (condition),
column2 datatype,
...
); Here’s a breakdown of the syntax:
- CREATE TABLE table_name: Specifies the name of the table you are creating.
- (column1 datatype, column2 datatype, …): Defines the columns in the table along with their data types.
- CONSTRAINT constraint_name: Assigns a name to the CHECK constraint. This is optional, and you can omit it if you don’t want to name the constraint explicitly.
- CHECK (condition): Specifies the condition that must be satisfied for each row in the table. The condition is a logical expression that evaluates to either true or false.
Properties of SQL CHECK Constraints:
In SQL Check constraint has several properties and characteristics that help define and maintain the relationship between tables.
SQL CHECK constraints have several properties and characteristics that define their behavior and usage. Here are some key properties of SQL CHECK constraints:
Condition Definition:
- The core purpose of a
CHECKconstraint is to define a condition that must evaluate to true for each row in a table. - The condition is specified using a Boolean expression, and it can reference one or more columns in the table.
- The core purpose of a
Table and Column Scope:
- A
CHECKconstraint can be applied at the table level or column level. - Table-level constraints apply to the entire table, considering a combination of values from multiple columns.
- Column-level constraints apply to a specific column and only consider the values in that column.
- A
Constraint Naming:
- You can provide a name for the
CHECKconstraint to make it more readable and manageable. If a name is not provided, the database system will generate one. - The name must be unique within the scope of the table.
- You can provide a name for the
Enforcement:
CHECKconstraints are enforced by the database management system (DBMS) during data modification operations, such asINSERTorUPDATE.- If a row violates the condition specified in the
CHECKconstraint, the DBMS will prevent the operation and raise a constraint violation error.
Relationship Between Foreign key and Primary key
Example 1: SQL CHECK Constraint Success
Here’s an example illustrating the successful implementation of a SQL check constraint:
- Let’s create a simple table named Students with a check constraint to ensure that the
agecolumn only allows values greater than or equal to 18.
CREATE TABLE Students (
student_id INT PRIMARY KEY,
name VARCHAR(50),
age INT CHECK (age >= 18)
); - This SQL statement creates a table named Students with columns student_id, name, and age.
- The age column has a check constraint (CHECK (age >= 18)) defined, ensuring that any value inserted into the age column must be 18 or higher.
Example 2: SQL CHECK Constraint Failure
In this example, let’s consider a scenario where a SQL check constraint fails due to an attempt to insert data that violates the defined condition:
Suppose there’s a table called Products with a check constraint on the price column, ensuring that the price of a product is within a specific range (between $10 and $100).
CREATE TABLE Products (
product_id INT PRIMARY KEY,
product_name VARCHAR(100),
price DECIMAL(8, 2) CHECK (price >= 10 AND price <= 100)
); Now, let’s try to insert data that violates this check constraint by attempting to add a product with a price outside the specified range:
-- Invalid data insertion (check constraint violation) INSERT INTO Products (product_id, product_name, price) VALUES (1, 'Example Product', 150);
The INSERT statement tries to add a product with a price of $150, which exceeds the upper limit defined by the check constraint (price <= 100).
When this command is executed, the database will detect the violation of the check constraint and prevent the insertion of the row into the Products table.
Create Named CHECK Constraint
- Here’s an example of creating a named CHECK constraint for a hypothetical table named students:
CREATE TABLE Employees (
employee_id INT PRIMARY KEY,
employee_name VARCHAR(50),
age INT,
CONSTRAINT CHK_EmployeeAge CHECK (age BETWEEN 18 AND 65)
);
In this example:
- CONSTRAINT chk_age is the name given to the CHECK constraint.
- CHECK (age BETWEEN 18 AND 25) specifies the condition that the age column must be between 18 and 25 (inclusive).
You can adjust the constraint condition according to your specific requirements. The key part is using the CONSTRAINT keyword followed by a name and then specifying the condition using the CHECK keyword.
Prime Course Trailer
Related Banners
Get PrepInsta Prime & get Access to all 200+ courses offered by PrepInsta in One Subscription
Get over 200+ course One Subscription
Courses like AI/ML, Cloud Computing, Ethical Hacking, C, C++, Java, Python, DSA (All Languages), Competitive Coding (All Languages), TCS, Infosys, Wipro, Amazon, DBMS, SQL and others
