{"id":428,"date":"2024-08-28T08:47:25","date_gmt":"2024-08-28T08:47:25","guid":{"rendered":"https:\/\/techkubo.com\/sql\/?p=428"},"modified":"2025-05-25T04:03:50","modified_gmt":"2025-05-25T04:03:50","slug":"check","status":"publish","type":"post","link":"https:\/\/techkubo.com\/sql\/check\/","title":{"rendered":"CHECK"},"content":{"rendered":"\n

SQL CHECK Keyword<\/h2>\n\n\n\n

The CHECK constraint is used in SQL to limit the values that can be inserted into a column. It ensures that data entered into a table meets specific rules and helps maintain data integrity.<\/p>\n\n\n\n

<\/p>\n\n\n\n

SQL CHECK on CREATE TABLE<\/h2>\n\n\n\n

The following SQL creates a CHECK constraint on the EngineSize column when the Vehicles table is created. The constraint ensures that no vehicle with an engine size below 100 cc can be added.<\/p>\n\n\n\n

MySQL<\/strong>:<\/p>\n\n\n\n

CREATE TABLE Vehicles (\n    EngineSize INT,\n    CHECK (EngineSize >= 100)\n)<\/pre><\/div>\n\n\n\n
\"\"<\/figure>\n\n\n\n
SQL Server \/ Oracle \/ MS Access<\/strong>:<\/p>\n\n\n\n
CREATE TABLE Vehicles1 (\n    EngineSize INT CHECK (EngineSize >= 100)\n)<\/pre><\/div>\n\n\n\n
\"\"<\/figure>\n\n\n\n
\n
Note:<\/strong><\/em> In SQLite including in the TechKubo playground CHECK constraints are supported but must be defined during table creation Only database systems like MySQL and SQL Server allow adding or dropping CHECK constraints after creation using ALTER TABLE.<\/p>\n<\/blockquote>\n\n\n\n
<\/p>\n\n\n\n
SQL CHECK with Named Constraint on Multiple Columns<\/h2>\n\n\n\n
You can name a CHECK constraint and define it using multiple columns. The following example ensures that only motorcycles with an engine size of at least 100 cc and brand equal to ‘Yamaha’ are allowed.<\/p>\n\n\n\n
MySQL \/ SQL Server \/ Oracle \/ MS Access<\/strong>:<\/p>\n\n\n\n
CREATE TABLE Vehicles2 (\n    EngineSize INT,\n    Brand VARCHAR(255),\n    CONSTRAINT CHK_EngineBrand CHECK (EngineSize >= 100 AND Brand = 'Yamaha')\n)<\/pre><\/div>\n\n\n\n
\n
Note:<\/em><\/strong> SQLite including the TechKubo playground does support named CHECK constraints during CREATE TABLE However you cannot add or drop named constraints later using ALTER TABLE.<\/p>\n<\/blockquote>\n\n\n\n
<\/p>\n\n\n\n
SQL CHECK on ALTER TABLE<\/h2>\n\n\n\n
You can also add a CHECK constraint to an existing table using ALTER TABLE. The example below ensures that only vehicles with a horsepower greater than or equal to 50 are allowed.<\/p>\n\n\n\n
MySQL \/ SQL Server \/ Oracle \/ MS Access<\/strong>:<\/p>\n\n\n\n
ALTER TABLE Vehicles\nADD CHECK (Horsepower >= 50)<\/pre><\/div>\n\n\n\n
With a named constraint that applies to both horsepower and brand<\/p>\n\n\n\n
ALTER TABLE Vehicles\nADD CONSTRAINT CHK_PowerBrand CHECK (Horsepower >= 50 AND Brand = 'Honda')<\/pre><\/div>\n\n\n\n
\n
Note:<\/em><\/strong> These commands will not work in SQLite. SQLite does not support adding constraints to existing tables using ALTER TABLE. Constraints must be defined when the table is created.<\/p>\n<\/blockquote>\n\n\n\n
<\/p>\n\n\n\n
SQL DROP a CHECK Constraint<\/h2>\n\n\n\n
SQL Server \/ Oracle \/ MS Access<\/strong>:<\/p>\n\n\n\n
ALTER TABLE Vehicles\nDROP CONSTRAINT CHK_PowerBrand<\/pre><\/div>\n\n\n\n
MySQL<\/strong>:<\/p>\n\n\n\n
ALTER TABLE Vehicles\nDROP CHECK CHK_PowerBrand<\/pre><\/div>\n\n\n\n
\n
Note:<\/em><\/strong> Dropping a CHECK constraint is not supported in SQLite. Once added in SQLite during table creation it cannot be altered or dropped without recreating the table<\/p>\n<\/blockquote>\n\n\n\n
<\/p>\n\n\n\n
SQL CHECK Labs<\/h2>\n\n\n\n