Meritshot Tutorials

  1. Home
  2. »
  3. SQL CHECK Constraint
SQL

SQL Tutorial

SQL CHECK Constraint

The CHECK constraint in SQL is used to ensure that all values in a column meet a specific condition or set of conditions. It is a way to enforce domain integrity by limiting the values that can be inserted or updated in a table. This constraint ensures that only valid data is entered into the column.

Syntax

CREATE TABLE table_name (

    column_name datatype CHECK (condition),

    …

);

— Adding CHECK constraint to an existing table

ALTER TABLE table_name

ADD CONSTRAINT constraint_name CHECK (condition);

  • column_name: The name of the column to which the CHECK constraint is applied.
  • datatype: The type of data the column will store (e.g., INT, VARCHAR).
  • condition: The condition that values in the column must satisfy.

Example:

1 Creating a Table with a CHECK Constraint

Suppose you want to create a table Products where the Price column must be greater than zero:

CREATE TABLE Products (

    ProductID INT PRIMARY KEY,

    ProductName VARCHAR(100),

    Price DECIMAL(10, 2) CHECK (Price > 0)

);

In this table, the CHECK constraint ensures that the Price must always be greater than zero.

2 Adding a CHECK Constraint to an Existing Column

If you have an existing table Employees and want to ensure that the Age column contains values between 18 and 65:

ALTER TABLE Employees

ADD CONSTRAINT age_check CHECK (Age BETWEEN 18 AND 65);

This command adds a CHECK constraint to the Age column, enforcing that the age must be within the specified range.

3 Using CHECK Constraints with Multiple Conditions

You can also apply multiple conditions in a CHECK constraint. For instance, to ensure that an Order status is either ‘Pending’ or ‘Completed’:

CREATE TABLE Orders (

    OrderID INT PRIMARY KEY,

    OrderDate DATE,

    Status VARCHAR(20),

    CHECK (Status IN (‘Pending’, ‘Completed’))

);

Here, the CHECK constraint ensures that the Status column only contains the specified values.

4 Handling Constraint Violations

When inserting data into a table with a CHECK constraint, ensure that the data meets the specified condition. For example, if Price must be greater than zero:

— Valid insertion

INSERT INTO Products (ProductID, ProductName, Price)

VALUES (1, ‘Laptop’, 75000.00);

— Invalid insertion, will fail

INSERT INTO Products (ProductID, ProductName, Price)

VALUES (2, ‘Mouse’, -150.00);

The second insertion will fail because the Price does not meet the CHECK constraint condition.

Tips to Remember

  1. Ensure Data Validity: Use CHECK constraints to enforce valid data entry and prevent incorrect data from being inserted.
  2. Combine with Other Constraints: CHECK constraints can be combined with other constraints like PRIMARY KEY and FOREIGN KEY to maintain comprehensive data integrity.
  3. Consider Performance Impact: Extensive use of CHECK constraints can affect performance; use them judiciously.

Frequently Asked Questions

Q1: Can a CHECK constraint be applied to multiple columns?
A1: Yes, you can apply a CHECK constraint that involves multiple columns, allowing you to enforce conditions that depend on the values of more than one column.

Q2: What happens if I try to insert a value that violates a CHECK constraint?
A2: SQL will return an error and prevent the insertion or update of the row with invalid data.

Q3: Can I modify a CHECK constraint after it has been created?
A3: Yes, you can modify a CHECK constraint using the ALTER TABLE command, but you need to handle existing data to ensure it complies with the new constraint.

Q4: How do I drop a CHECK constraint?
A4: Use the ALTER TABLE command to drop a CHECK constraint:

ALTER TABLE Products

DROP CONSTRAINT constraint_name;

Q5: Can a CHECK constraint be used with NULL values?
A5: Yes, if the condition in the CHECK constraint does not involve NULL, the column can contain NULL values, as NULL does not affect the constraint evaluation.

❮ Previous
Next ❯
ads website