{"id":328,"date":"2024-08-26T05:48:59","date_gmt":"2024-08-26T05:48:59","guid":{"rendered":"https:\/\/techkubo.com\/sql\/?p=328"},"modified":"2025-05-16T10:01:12","modified_gmt":"2025-05-16T10:01:12","slug":"sql-check","status":"publish","type":"post","link":"https:\/\/techkubo.com\/sql\/sql-check\/","title":{"rendered":"SQL Check"},"content":{"rendered":"\n

The CHECK constraint is used to limit the value range that can be placed in a column. If the condition evaluates to FALSE, the data is rejected. This ensures data validity by enforcing rules at the column or table level.<\/p>\n\n\n\n

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

SQL Check Syntax<\/h2>\n\n\n\n
CREATE TABLE table_name (\n  column_name datatype CHECK (condition)\n);<\/pre><\/div>\n\n\n\n
You can also define a CHECK constraint on multiple columns:<\/p>\n\n\n\n
CREATE TABLE table_name (\n  column1 datatype,\n  column2 datatype,\n  CHECK (column1 > column2)\n);<\/pre><\/div>\n\n\n\n
<\/p>\n\n\n\n
SQL Check Example<\/h2>\n\n\n\n
SQL Check on Create Table<\/h3>\n\n\n\n
This query applies a CHECK constraint while creating the table, ensuring that the date of birth (dob) is not in the future:<\/p>\n\n\n\n
CREATE TABLE PatientsWithDOBCheck (\n  patient_id INTEGER PRIMARY KEY,\n  first_name VARCHAR(50) NOT NULL,\n  last_name VARCHAR(50) NOT NULL,\n  dob DATE CHECK (dob <= DATE('now')),\n  gender CHAR(1) NOT NULL,\n  contact_number VARCHAR(15)\n);<\/pre><\/div>\n\n\n\n
\"\"<\/figure>\n\n\n\n
\n
Try It Out!<\/a><\/div>\n<\/div>\n\n\n\n
<\/p>\n\n\n\n
SQL Check on Alter Table<\/h3>\n\n\n\n
SQLite does not support adding CHECK constraints using ALTER TABLE. The only workaround is to recreate the table with the new CHECK constraint and copy the data from the original table.<\/p>\n\n\n\n
Steps<\/strong>:<\/h4>\n\n\n\n
    \n
  1. Create a new table with the desired CHECK constraint<\/strong>:<\/li>\n<\/ol>\n\n\n\n
    CREATE TABLE PatientsNew (\n  patient_id INTEGER PRIMARY KEY,\n  first_name VARCHAR(50) NOT NULL,\n  last_name VARCHAR(50) NOT NULL,\n  dob DATE NOT NULL,\n  gender CHAR(1) NOT NULL CHECK (gender IN ('M', 'F')),\n  contact_number VARCHAR(15)\n);<\/pre><\/div>\n\n\n\n
      \n
    1. Copy data from the existing table<\/strong>:<\/li>\n<\/ol>\n\n\n\n
      INSERT INTO PatientsNew (patient_id, first_name, last_name, dob, gender, contact_number)\nSELECT patient_id, first_name, last_name, dob, gender, contact_number FROM Patients;<\/pre><\/div>\n\n\n\n
        \n
      1. Drop the old table<\/strong> (optional \u2014 do with caution):<\/li>\n<\/ol>\n\n\n\n
        DROP TABLE Patients;<\/pre><\/div>\n\n\n\n
          \n
        1. Rename the new table<\/strong>:<\/li>\n<\/ol>\n\n\n\n
          ALTER TABLE PatientsNew RENAME TO Patients;<\/pre><\/div>\n\n\n\n
          \"\"<\/figure>\n\n\n\n
          <\/p>\n\n\n\n
          SQL Check Drop a Check Constraint<\/h3>\n\n\n\n
          SQLite does not<\/strong> support dropping a CHECK constraint directly.<\/p>\n\n\n\n
          If needed, you must:<\/p>\n\n\n\n
            \n
          • Create a new table without the constraint<\/li>\n\n\n\n
          • Move the data over<\/li>\n\n\n\n
          • Drop the old table<\/li>\n\n\n\n
          • Rename the new one<\/li>\n<\/ul>\n\n\n\n
            <\/p>\n\n\n\n
            <\/p>\n\n\n\n
            SQL Check Labs<\/h2>\n\n\n\n