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
  • FOREIGN KEY 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)
);
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:  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)
);
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)
);
CREATE TABLE

-- Insert data with duplicate data. An error is reported.
gaussdb=# INSERT INTO tbl_person2 VALUES (1,'Bob');
INSERT 0 1
gaussdb=# INSERT INTO tbl_person2 VALUES (1,'Bob');
ERROR:  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:  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

FOREIGN KEY Constraints

  • Columns with FOREIGN KEY constraints can contain only values of the corresponding column (referenced column) in the referenced table or NULL values.
  • A FOREIGN KEY constraint can be defined on a single column or a combination of multiple columns.
  • Both FOREIGN KEY and NOT NULL constraints can be defined for the same column.
  • The referenced column in the referenced table must have a PRIMARY KEY constraint or a UNIQUE constraint.
-- Create a department table.
gaussdb=# CREATE TABLE works(
    w_id int primary key, -- A referenced column must have a PRIMARY KEY constraint or UNIQUE constraint.
    w_name varchar(50)
);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "works_pkey" for table "works"
CREATE TABLE


-- Create an employee table. The w_id column references the w_id column in the works table.
gaussdb=# CREATE TABLE person(
    p_id int PRIMARY KEY,
    p_name varchar(50),
    p_age int CHECK(p_age > 0 and p_age < 200),
    w_id int,
    CONSTRAINT fk_person FOREIGN KEY (w_id) REFERENCES works(w_id)
);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "person_pkey" for table "person"
CREATE TABLE


-- Insert data to the works table.
gaussdb=# INSERT INTO works VALUES (90010,'Sales department');
INSERT 0 1
gaussdb=# INSERT INTO works VALUES (90020,'O&M department');
INSERT 0 1
gaussdb=# INSERT INTO works VALUES (90030,'Development department');
INSERT 0 1

-- Insert normal data into the employee table.
gaussdb=# INSERT INTO person VALUES (1, 'Xiaohong', 27, 90010);
INSERT 0 1

-- Insert data that does not comply with the constraint into the employee table. (The works table does not contain the data whose w_id is 90040.)
gaussdb=# INSERT INTO person VALUES (2, 'Xiaoliang', 32, 90040);
ERROR:  insert or update on table "person" violates foreign key constraint "fk_person"
DETAIL:  Key (w_id)=(90040) is not present in table "works".

-- Add a NOT NULL constraint to the w_id column in the employee table.
gaussdb=# INSERT INTO person VALUES (2, 'Xiaoliang', 32, NULL);
INSERT 0 1

When defining a FOREIGN KEY constraint, you can use the ON keyword to specify how to process data in the subtable (person table in the example) when the data in the main table (works table in the example) is dropped.

  • ON DELETE CASCADE: When data is deleted from the main table, all related records in the subtable are deleted.
  • ON DELETE SET NULL: When data is deleted from the main table, all related columns in the subtable are set to NULL.
  • ON DELETE NO ACTION: When data is deleted from the main table, an error is reported and no operation is performed (by default) if the subtable contains related data.
-- Example of deleting data from the main table by default
gaussdb=# DELETE FROM works WHERE w_id = 90010;
ERROR:  update or delete on table "works" violates foreign key constraint "fk_person" on table "person"
DETAIL:  Key (w_id)=(90010) is still referenced from table "person".

-- Example of ON DELETE SET NULL
gaussdb=# CREATE TABLE person2(
    p_id int PRIMARY KEY,
    p_name varchar(50),
    p_age int CHECK(p_age > 0 and p_age < 200),
    w_id int,
    CONSTRAINT fk_person FOREIGN KEY (w_id) REFERENCES works(w_id) ON DELETE SET NULL
);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "person2_pkey" for table "person2"
CREATE TABLE


-- Insert data.
gaussdb=# INSERT INTO person2 VALUES (3,'Xiao Ming',24,90020);
INSERT 0 1

-- Query table data.
gaussdb=# SELECT * FROM person2;
 p_id | p_name | p_age | w_id  
------+--------+-------+-------
    3 | Xiao Ming   |    24 | 90020
(1 row)

-- Delete data from the main table.
gaussdb=# DELETE FROM works WHERE w_id = 90020;
DELETE 1

-- View data in the subtable. The value of the w_id column is changed to NULL.
gaussdb=# SELECT * FROM person2;
 p_id | p_name | p_age | w_id 
------+--------+-------+------
    3 | Xiao Ming   |    24 |
(1 row)

-- Drop tables.
gaussdb=# DROP TABLE works,person,person2;
DROP TABLE