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
Parent topic: Basic Concepts
Feedback
Was this page helpful?
Provide feedbackThank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.
The system is busy. Please try again later.
For any further questions, feel free to contact us through the chatbot.
Chatbot