EC
1998
MySQLERRORNotableSchemaHIGH confidence

Spatial index on column with NULL values not allowed

Production Risk

Low — DDL is rejected; existing data is unaffected.

What this means

A spatial index (SPATIAL KEY) cannot be created on a column that allows NULL values. Spatial indexes require all indexed values to be non-null geometries.

Why it happens
  1. 1Creating a SPATIAL INDEX on a column defined without NOT NULL.
  2. 2Altering a table to add a SPATIAL KEY on a nullable spatial column.
How to reproduce
trigger — this will error
trigger — this will error
CREATE TABLE geo (g GEOMETRY, SPATIAL INDEX(g));

expected output

ERROR 1998 (HY000): All parts of a SPATIAL index must be NOT NULL.

Fix 1

Declare the spatial column as NOT NULL

Declare the spatial column as NOT NULL
CREATE TABLE geo (g GEOMETRY NOT NULL, SPATIAL INDEX(g));

Why this works

NOT NULL ensures every row has a valid geometry for indexing.

Fix 2

Alter the column to NOT NULL before adding the index

Alter the column to NOT NULL before adding the index
ALTER TABLE geo MODIFY g GEOMETRY NOT NULL;
ALTER TABLE geo ADD SPATIAL INDEX(g);

Why this works

Changing nullability first satisfies the spatial index requirement.

What not to do

✕

Sources
Official documentation ↗

MySQL 8.0 — 1998 ER_SPATIAL_CANT_HAVE_NULL

Content generated with AI assistance and reviewed for accuracy. Found an error? hello@errcodes.dev

← All MySQL errors