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

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.