Help Center/ Data Warehouse Service / More Documents/ Tool Guide (Kuala Lumpur Region)/ DSC/ Migrating MySQL Syntax/ Table (Optional Parameters and Operations)/ CHECK Constraint
Updated on 2025-10-14 GMT+08:00
View PDF
Share

CHECK Constraint

Specifies an expression producing a Boolean result which new or updated rows must satisfy for an insert or update operation to succeed. Expressions evaluating to TRUE or UNKNOWN succeed. If any row of an insert or update operation produces a FALSE result, an error exception is raised and the insert or update does not alter the database.

A check constraint specified as a column constraint should reference only the column's values, while an expression appearing in a table constraint can reference multiple columns.

CREATE TABLE CHECK

In DWS, you can run CHECK (column name > 0) to define a CHECK constraint for a column when creating a table. The CHECK constraint can be placed after the column name or below the column name. If you want to name a CHECK constraint and define CHECK constraints for multiple columns, run CONSTRAINT chk_name CHECK (column_namw1 >0 AND column_name2='x>x").

The CHECK constraint is also removed if the table is removed.

Input

1
2
3
4
5
6
7
 
DROP TABLE IF EXISTS `t1`;
CREATE TABLE IF NOT EXISTS t1  (
  id int(25) not null primary key check (id > 1 and id < 100),
city varchar(255) not null unique check (city='city 1' or city='city 2' or city='city 3'),
  population int(25) not null ,
  constraint t1_1 check (population>0 and population<10000)
) ;

Output

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
 
DROP TABLE IF EXISTS "public"."t1";
CREATE TABLE IF NOT EXISTS "public"."t1" (
  "id" INTEGER NOT NULL PRIMARY KEY CHECK (
    id > 1
    and id < 100
  ),
  "city" VARCHAR(1020) NOT NULL CHECK (
city ='City 1'
or city ='City 2'
or city ='City 3'
  ),
  "population" INTEGER NOT NULL,
  CONSTRAINT t1_1 CHECK (
    population > 0
    and population < 10000
  )
) WITH (ORIENTATION = ROW, COMPRESSION = NO) NOCOMPRESS DISTRIBUTE BY HASH ("id");

ALTER TABLE CHECK

When you perform operations on table columns, you can have or have no naming constraint. To create a CHECK constraint in a column, use the ALTER TABLE Table name ADD CHECK (<Check constraint>); syntax.

To name a CHECK constraint and define it for multiple columns, use the ALTER TABLE Table name ADD CONSTRAINT <Name of the check constraint> CHECK(<Check constraint>); syntax.

Input

1
2
3
4
 
ALTER TABLE t1 add check (id>1 and id > 2  and id < 100 or id =50);
ALTER TABLE student add constraint check02 check (id > 1 and class = '3');
ALTER TABLE t1 add check (class=1 or class =2  or class = 3 or class=4 or class = 5 or class = 6);
ALTER TABLE t1 add check (city='city 1' or city='city 2' or city='city 3');

Output

1
2
3
4
 
ALTER TABLE "public"."t1" ADD CHECK ( id > 1  and id > 2 and id < 100  or id = 50 );
ALTER TABLE "public"."student" ADD CONSTRAINT check02 CHECK (  id > 1  and class = '3');
ALTER TABLE "public"."t1" ADD CHECK ( class = 1 or class = 2 or class = 3  or class = 4 or class = 5 or class = 6);
ALTER TABLE "public"."t1" ADD CHECK (city ='City 1' or city =' City 2' or city =' City 3');