📋

Description

This error occurs when you attempt to define a primary key column with a `NULL` constraint, or when you try to insert or update a `NULL` value into a column that is part of the `PRIMARY KEY`. Primary keys are fundamentally designed to uniquely identify rows and thus cannot contain `NULL` values.

💬

Error Message

All parts of a PRIMARY KEY must be NOT NULL; if you need NULL in a key, use UNIQUE instead

🔍

Known Causes

3 known causes

⚠️

Defining PK with NULL Constraint

You explicitly declared a column as `PRIMARY KEY` and also specified it as `NULL` in the `CREATE TABLE` or `ALTER TABLE` statement.

⚠️

Inserting NULL Value

An `INSERT` or `UPDATE` statement attempted to assign a `NULL` value to a column that is part of the table's `PRIMARY KEY`.

⚠️

Existing NULLs in Column

You attempted to add a `PRIMARY KEY` constraint to an existing column that already contains one or more `NULL` values.

🛠️

Solutions

3 solutions available

1. Modify Table to Ensure PRIMARY KEY is NOT NULL easy

Alter the table definition to explicitly set the PRIMARY KEY column(s) as NOT NULL.

1

Identify the table and column(s) involved in the PRIMARY KEY that are allowing NULL values.

2

Use the ALTER TABLE statement to modify the column definition, adding the NOT NULL constraint.

ALTER TABLE your_table_name MODIFY COLUMN your_column_name data_type NOT NULL;

3

If the PRIMARY KEY is a composite key, apply the NOT NULL constraint to all involved columns.

ALTER TABLE your_table_name MODIFY COLUMN column1 data_type NOT NULL;
ALTER TABLE your_table_name MODIFY COLUMN column2 data_type NOT NULL;

4

Verify the table schema to confirm the PRIMARY KEY columns are now defined as NOT NULL.

DESCRIBE your_table_name;

2. Recreate Table with Correct PRIMARY KEY Definition medium

Back up data, drop the existing table, and recreate it with the correct PRIMARY KEY definition.

1

Create a backup of the existing table's data.

CREATE TABLE your_table_name_backup AS SELECT * FROM your_table_name;

2

Drop the original table.

DROP TABLE your_table_name;

3

Recreate the table with the PRIMARY KEY columns defined as NOT NULL.

CREATE TABLE your_table_name (
    your_column_name data_type NOT NULL,
    other_column data_type,
    PRIMARY KEY (your_column_name)
);

4

Restore the data from the backup.

INSERT INTO your_table_name (your_column_name, other_column) SELECT your_column_name, other_column FROM your_table_name_backup;

5

Drop the backup table.

DROP TABLE your_table_name_backup;

3. Replace PRIMARY KEY with a UNIQUE Constraint medium

If NULLable values are intentionally required in a key, change the PRIMARY KEY to a UNIQUE constraint.

1

Identify the table and column(s) that are intended to be a unique identifier but need to allow NULLs.

2

Drop the existing PRIMARY KEY constraint.

ALTER TABLE your_table_name DROP PRIMARY KEY;

3

Add a UNIQUE constraint to the desired column(s). Note that MySQL/MariaDB allows NULL values in UNIQUE constraints, but only one NULL is permitted per column if it's a single-column UNIQUE index. For composite UNIQUE indexes, multiple rows can have NULL in one or more columns as long as the combination is unique.

ALTER TABLE your_table_name ADD UNIQUE INDEX unique_index_name (your_column_name);

4

If the table requires a PRIMARY KEY for other reasons, consider adding a separate auto-incrementing integer column as the PRIMARY KEY and keeping the original column as a UNIQUE constraint.

ALTER TABLE your_table_name ADD COLUMN id INT AUTO_INCREMENT PRIMARY KEY FIRST;
ALTER TABLE your_table_name ADD UNIQUE INDEX unique_index_name (your_column_name);

🔗

Related Errors

5 related errors

MariaDB Error 1171: PRIMARY KEY Cannot Be NULL