Updated on 2025-09-22 GMT+08:00

Constraints

SQL constraints are used to specify the data rules in a table. If a user attempts to insert data that violates a constraint in a table, an error is thrown. Constraints are classified into the following types:

  • NOT NULL constraints
  • UNIQUE constraints
  • PRIMARY KEY constraints
  • CHECK constraints

NOT NULL Constraints

  • Columns with NOT NULL constraints cannot contain NULL values.
  • Only column-level NOT NULL constraints can be created.
  • Multiple NOT NULL constraints can be defined in the same table.
-- Create a table and add a NOT NULL constraint.
gaussdb=# CREATE TABLE tbl_person1(
    id int not null,
    name varchar(50)
);
NOTICE:  The 'DISTRIBUTE BY' clause is not specified. Using 'id' as the distribution column by default.
HINT:  Please use 'DISTRIBUTE BY' clause to specify suitable data distribution column.
CREATE TABLE

-- Insert data that does not comply with the constraint. An error is reported.
gaussdb=# INSERT INTO tbl_person1 (id,name) VALUES (NULL,'Bob');
ERROR:  dn_6001_6002_6003: The null value in column "id" violates the not-null constraint.
DETAIL:  Failing row contains (null, Bob).

-- Drop the table.
gaussdb=# DROP TABLE tbl_person1;
DROP TABLE

UNIQUE Constraints

  • Columns with UNIQUE constraints cannot contain duplicate values.
  • Multiple UNIQUE constraints can be defined in the same table.
  • After a UNIQUE constraint is defined, the database automatically adds a UNIQUE index to the constraint column.
  • A column can have both UNIQUE and NOT NULL constraints. If a column has only the UNIQUE constraint, the column can contain multiple NULL values.
-- Create a table and add a column-level UNIQUE constraint.
gaussdb=# CREATE TABLE tbl_person2(
    id int UNIQUE, 
    name varchar(50)
);
NOTICE:  CREATE TABLE / UNIQUE will create implicit index "tbl_person2_id_key" for table "tbl_person2"
CREATE TABLE

-- Create a table and add a table-level UNIQUE constraint.
gaussdb=# CREATE TABLE tbl_person3(
    id int,
    name varchar(50),
    CONSTRAINT uq_person3_id UNIQUE(id)
);
NOTICE:  CREATE TABLE / UNIQUE will create implicit index "uq_person3_id" for table "tbl_person3"
CREATE TABLE

-- Insert duplicate values. A UNIQUE constraint error is reported.
gaussdb=# INSERT INTO tbl_person2 VALUES (1,'Bob');
INSERT 0 1
gaussdb=# INSERT INTO tbl_person2 VALUES (1,'Bob');
ERROR:  dn_6004_6005_6006: Duplicate key value violates unique constraint "tbl_person2_id_key".
DETAIL:  Key (id)=(1) already exists.

-- Duplicate NULL values do not trigger a UNIQUE constraint.
gaussdb=# INSERT INTO tbl_person2 VALUES (null,'Bob');
INSERT 0 1
gaussdb=# INSERT INTO tbl_person2 VALUES (null,'Bob');
INSERT 0 1

-- Drop.
gaussdb=# DROP TABLE tbl_person2,tbl_person3;
DROP TABLE

PRIMARY KEY Constraints

  • Columns with PRIMARY KEY constraints cannot contain duplicate values and NULL values.
  • You can define PRIMARY KEY constraints for one or more columns.
  • After a PRIMARY KEY constraint is created, the database automatically creates a UNIQUE index and a NOT NULL constraint for the column.
  • Only one PRIMARY KEY constraint can be defined in a table.
-- Create a table and add a column-level PRIMARY KEY constraint.
gaussdb=# CREATE TABLE tbl_person4(
    id int PRIMARY KEY,
    name varchar(50)
);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "tbl_person4_pkey" for table "tbl_person4"
CREATE TABLE

-- Create a table and add a table-level PRIMARY KEY constraint.
gaussdb=# CREATE TABLE tbl_person5(
    id int,
    name varchar(50),
    CONSTRAINT pk_person5_id PRIMARY KEY(id)
);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "pk_person5_id" for table "tbl_person5"
CREATE TABLE

-- Drop tables.
gaussdb=# DROP TABLE tbl_person4,tbl_person5;
DROP TABLE

CHECK Constraints

  • An expression with a CHECK constraint must reference one or more columns in a table, and the calculation result of the expression must be a Boolean value. The expression cannot contain subqueries.
  • CHECK constraints can be defined at the column or table level.
  • Both CHECK and NOT NULL constraints can be defined for the same column.
  • Multiple CHECK constraints can be defined for the same column.
  • Multiple CHECK constraints can be defined in the same table.
-- Create a table and add a CHECK constraint.
gaussdb=# CREATE TABLE tbl_person6(
    id int PRIMARY KEY,
    name varchar(50) NOT NULL,
    age int CHECK(age > 0 AND age < 200) NOT NULL
);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "tbl_person6_pkey" for table "tbl_person6"
CREATE TABLE

-- Insert data that does not comply with the constraint. An error is reported.
gaussdb=# INSERT INTO tbl_person6 VALUES (1,'Bob',500);
ERROR:  dn_6004_6005_6006: New row in relation "tbl_person6" violates check constraint "tbl_person6_age_check".
DETAIL:  N/A

-- Drop the table.
gaussdb=# DROP TABLE tbl_person6;
DROP TABLE