How can I troubleshoot "ERROR: null value in column violates not-null constraint" for my AWS DMS full load and CDC task?

2 minute read

I have an AWS Database Migration Service (AWS DMS) task that is both full load and change data capture (CDC). I received an error that says: "ERROR: null value in column violates not-null constraint." How do I resolve this issue?


If you have large object (LOB) columns in your migration task, then you see a log entry similar to the following:

E: Command failed to load data with exit error code 1, Command output: ERROR: null value in column ’xyz’ violates not-null constraint

When AWS DMS migrates LOB columns, first all the data except for the LOB column is migrated into your target table, and AWS DMS inserts a NULL record in the LOB column. Then, AWS DMS updates the rows in the target table with the LOB column data.

If the target isn't created by AWS DMS, check the target data description language (DDL) to see if a NOT NULL attribute is specified. If there is a NOT NULL attribute, alter the table to remove the NOT NULL constraint on LOB column datatypes. Then, resume your AWS DMS task.

See the following table for AWS DMS task behavior for LOB modes and task phases:

LOB ModeFull LoadChange Data Capture
Full LOB ModeNOT NULL constraint isn't allowedNOT NULL constraint isn't allowed
Limited LOB ModeNULL constraint is allowedNOT NULL constraint isn't allowed

Related Information

Migrating Large Binary Objects (LOBs)

AWS OFFICIALUpdated 3 years ago