Help Center/ GaussDB(DWS)/ Troubleshooting/ Database Use/ Error Reported During Data Insertion: null value in column ' %s' violates not-null constraint
Updated on 2024-03-08 GMT+08:00
View PDF

Error Reported During Data Insertion: null value in column ' %s' violates not-null constraint

Symptom

The error "null value in column ' %s' violates not-null constraint" is reported when data is inserted into a table. In the message, s % indicates the name of the column (field) where the error occurs.

1
2
3
4
 
CREATE TABLE t1(a int, b int not null);

INSERT INTO t1 VALUES (1);
ERROR:  dn_6001_6002: null value in column "b" violates not-null constraint

Possible Causes

In the preceding case, if the not null constraint is set on column b in table t1 when the table is created, column b cannot contain null values. If column b is empty when data is inserted, an error is reported.

Solutions

There are two solutions to the preceding cases:

  • Solution 1: Use ALTER TABLE to delete the not null constraint on column b. 
    1
2
3
4
5
    		 
    ALTER TABLE t1 ALTER COLUMN b DROP NOT NULL;
ALTER TABLE

INSERT INTO t1 VALUES (1);
INSERT 0 1
  • Solution 2: Maintain the non-null (not null) constraint of column b but do not insert null values into column b.

In actual services, you can select a solution based on the site requirements.

Parent topic: Database Use