Help Center/ GaussDB(DWS)/ Tool Guide/ DSC/ SQL Syntax Migration Reference/ MySQL Syntax Migrating/ Schema Objects and Data Definition Language (DDL)/ Table (Optional Parameters and Operations)/ CHECK Constraint
Updated on 2024-07-19 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 GaussDB(DWS), when creating a table, you can place the CHECK constraint of a column after the column field or below the column field. The syntax is as follows: CHECK (column namel > 0), To name the CHECK constraint or define the CHECK constraint of multiple columns, use the 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 use the ALTER TABLE table_name ADD CHECK(<check_constraint >) syntax to create a CHECK constraint on a column. To name a CHECK constraint and define CHECK constraints for multiple columns, run the ALTER TABLE table_name ADD CONSTRAINT < check_constraint_name > 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');