The CHECK constraint enforces data integrity by ensuring column values meet specified conditions during insert or update operations. 1. It can be applied at column or table level using CREATE TABLE or ALTER TABLE. 2. It evaluates conditions before data modification: INSERTs and UPDATEs are rejected if the condition is FALSE. 3. It supports single-column rules, such as CHECK (Age >= 0), or multi-column rules, like CHECK (Price >= Discount). 4. Multiple conditions can be combined with AND or OR, as in CHECK (Salary > 0 AND Department IN ('HR', 'Engineering', 'Sales')). 5. NULL values pass the check because the result is UNKNOWN, not FALSE, unless explicitly restricted. 6. Enforcement varies across databases; some may parse but ignore CHECK constraints. For example, inserting a negative age into a table with CHECK (Age >= 0) will fail, ensuring only valid data is accepted, thereby maintaining data consistency and business rule compliance within the database schema.
The CHECK constraint in SQL ensures that the values inserted or updated in a column meet a specific condition. If the condition evaluates to FALSE, the operation is rejected, helping maintain data integrity.
Enforces Data Rules at the Column or Table Level
You can apply a CHECK constraint when creating a table (using CREATE TABLE) or add it later (with ALTER TABLE). It works on single columns or across multiple columns.
For example, to ensure ages are positive:
CREATE TABLE Users (
ID INT PRIMARY KEY,
Name VARCHAR(50),
Age INT,
CHECK (Age >= 0)
);Or, to enforce a rule across multiple columns:
CREATE TABLE Products (
ProductID INT PRIMARY KEY,
Price DECIMAL(10,2),
Discount DECIMAL(10,2),
CHECK (Price >= Discount)
);This prevents the discount from being higher than the price.
How It Works During Data Modifications
- INSERT: Before a row is added, the
CHECKcondition is evaluated. If it fails, the insert is blocked. - UPDATE: When a column involved in the check is modified, the new values must satisfy the condition, or the update fails.
For instance, this insert would fail with the above Users table if Age is negative:
INSERT INTO Users (ID, Name, Age) VALUES (1, 'Alice', -5); -- Fails
Key Points to Remember
- A
CHECKconstraint can include multiple conditions usingANDorOR. - It only checks the row being modified, not other rows in the table.
- NULL values are typically allowed unless explicitly restricted (since
CHECKconstraints evaluate toUNKNOWNwith NULLs, which is notFALSE, so it passes). - Not all databases enforce
CHECKconstraints the same way — some (like older versions of MySQL with MyISAM) may parse but ignore them.
Example with multiple conditions:
CREATE TABLE Employees (
ID INT,
Salary DECIMAL(10,2),
Department VARCHAR(50),
CHECK (Salary > 0 AND Department IN ('HR', 'Engineering', 'Sales'))
);Basically, CHECK constraints are a simple but powerful way to enforce business rules directly in the database schema.
The above is the detailed content of How does the CHECK constraint work in SQL?. For more information, please follow other related articles on the PHP Chinese website!
Hot AI Tools
Undress AI Tool
Undress images for free
Undresser.AI Undress
AI-powered app for creating realistic nude photos
AI Clothes Remover
Online AI tool for removing clothes from photos.
Clothoff.io
AI clothes remover
Video Face Swap
Swap faces in any video effortlessly with our completely free AI face swap tool!
Hot Article
Hot Tools
Notepad++7.3.1
Easy-to-use and free code editor
SublimeText3 Chinese version
Chinese version, very easy to use
Zend Studio 13.0.1
Powerful PHP integrated development environment
Dreamweaver CS6
Visual web development tools
SublimeText3 Mac version
God-level code editing software (SublimeText3)
Hot Topics
Defining Database Schemas with SQL CREATE TABLE Statements
Jul 05, 2025 am 01:55 AM
In database design, use the CREATETABLE statement to define table structures and constraints to ensure data integrity. 1. Each table needs to specify the field, data type and primary key, such as user_idINTPRIMARYKEY; 2. Add NOTNULL, UNIQUE, DEFAULT and other constraints to improve data consistency, such as emailVARCHAR(255)NOTNULLUNIQUE; 3. Use FOREIGNKEY to establish the relationship between tables, such as orders table references the primary key of the users table through user_id.
Key Differences Between SQL Functions and Stored Procedures.
Jul 05, 2025 am 01:38 AM
SQLfunctionsandstoredproceduresdifferinpurpose,returnbehavior,callingcontext,andsecurity.1.Functionsreturnasinglevalueortableandareusedforcomputationswithinqueries,whileproceduresperformcomplexoperationsanddatamodifications.2.Functionsmustreturnavalu
Can You Provide Code Examples Demonstrating Pattern Matching in SQL?
Jul 04, 2025 am 02:51 AM
Pattern matching functions in SQL include LIKE operator and REGEXP regular expression matching. 1. The LIKE operator uses wildcards '%' and '_' to perform pattern matching at basic and specific locations. 2.REGEXP is used for more complex string matching, such as the extraction of email formats and log error messages. Pattern matching is very useful in data analysis and processing, but attention should be paid to query performance issues.
Using SQL LAG and LEAD functions for time-series analysis.
Jul 05, 2025 am 01:34 AM
LAG and LEAD in SQL are window functions used to compare the current row with the previous row data. 1. LAG (column, offset, default) is used to obtain the data of the offset line before the current line. The default value is 1. If there is no previous line, the default is returned; 2. LEAD (column, offset, default) is used to obtain the subsequent line. They are often used in time series analysis, such as calculating sales changes, user behavior intervals, etc. For example, obtain the sales of the previous day through LAG (sales, 1, 0) and calculate the difference and growth rate; obtain the next visit time through LEAD (visit_date) and calculate the number of days between them in combination with DATEDIFF;
How to find columns with a specific name in a SQL database?
Jul 07, 2025 am 02:08 AM
To find columns with specific names in SQL databases, it can be achieved through system information schema or the database comes with its own metadata table. 1. Use INFORMATION_SCHEMA.COLUMNS query is suitable for most SQL databases, such as MySQL, PostgreSQL and SQLServer, and matches through SELECTTABLE_NAME, COLUMN_NAME and combined with WHERECOLUMN_NAMELIKE or =; 2. Specific databases can query system tables or views, such as SQLServer uses sys.columns to combine sys.tables for JOIN query, PostgreSQL can be used through inf
How to create a user and grant permissions in SQL
Jul 05, 2025 am 01:51 AM
Create a user using the CREATEUSER command, for example, MySQL: CREATEUSER'new_user'@'host'IDENTIFIEDBY'password'; PostgreSQL: CREATEUSERnew_userWITHPASSWORD'password'; 2. Grant permission to use the GRANT command, such as GRANTSELECTONdatabase_name.TO'new_user'@'host'; 3. Revoke permission to use the REVOKE command, such as REVOKEDELETEONdatabase_name.FROM'new_user
How to backup and restore a SQL database
Jul 06, 2025 am 01:04 AM
Backing up and restoring SQL databases is a key operation to prevent data loss and system failure. 1. Use SSMS to visually back up the database, select complete and differential backup types and set a secure path; 2. Use T-SQL commands to achieve flexible backups, supporting automation and remote execution; 3. Recovering the database can be completed through SSMS or RESTOREDATABASE commands, and use WITHREPLACE and SINGLE_USER modes if necessary; 4. Pay attention to permission configuration, path access, avoid overwriting the production environment and verifying backup integrity. Mastering these methods can effectively ensure data security and business continuity.
What is the SQL LIKE Operator and How Do I Use It Effectively?
Jul 05, 2025 am 01:18 AM
TheSQLLIKEoperatorisusedforpatternmatchinginSQLqueries,allowingsearchesforspecifiedpatternsincolumns.Ituseswildcardslike'%'forzeroormorecharactersand'_'forasinglecharacter.Here'showtouseiteffectively:1)UseLIKEwithwildcardstofindpatterns,e.g.,'J%'forn
