Adding a NOT NULL constraint to a column

Last modified: March 28, 2025

When you add a NOT NULL constraint to a column storing NULL entries without specifying a default value, the deployment process may fail because the new constraint conflicts with the existing NULL values in the column.

Solution: Instead, you need to create a migration script to update all existing NULL entries of the column with a NOT NULL value:

• Create a migration script to update NULL values and execute it
• Add a NOT NULL constraint

Note

The comparison engine of the tools doesn’t handle all cases automatically, which is why the cases mentioned above must be resolved manually.

The screenshot displays the Schema Comparison document and the scripts generated by Schema Compare Engine and a migration script, which should be created manually to perform errorless data migration.

The table provides source and target table script examples to illustrate the case:

CREATE TABLE product (
  ProductID INT AUTO_INCREMENT PRIMARY KEY,
  ProductNumber INT NOT NULL,
  ProductName VARCHAR(25) NULL
);

CREATE TABLE product (
  ProductID INT AUTO_INCREMENT PRIMARY KEY,
  ProductNumber INT,
  ProductName VARCHAR(25) NULL
);

INSERT INTO product (ProductNumber, ProductName)
  VALUES (1125, 'screwdriver');
INSERT INTO product (ProductNumber, ProductName)
  VALUES (1126, NULL);

The table provides examples of scripts generated by the Schema Comparison engine and those that should be generated manually for errorless data migration to illustrate the case:

ALTER TABLE product
  CHANGE COLUMN ProductNumber ProductNumber INT NOT NULL;

-- migration script:

UPDATE product
SET ProductNumber = <default_value>
WHERE ProductNumber IS NULL;

-- migration script.

ALTER TABLE product
  CHANGE COLUMN ProductNumber ProductNumber INT NOT NULL;