EC
1636
MySQLERRORNotableSchemaHIGH confidence

Spatial index requires a NOT NULL geometry column

Production Risk

Low — the index creation fails; no data is modified.

What this means

A spatial index can only be created on a geometry column that is defined as NOT NULL.

Why it happens
  1. 1Attempting to add a SPATIAL INDEX on a geometry column that allows NULL values.
How to reproduce
trigger — this will error
trigger — this will error
CREATE TABLE geo (id INT, location GEOMETRY);
CREATE SPATIAL INDEX idx_location ON geo(location);

expected output

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

Fix

Define the geometry column as NOT NULL

Define the geometry column as NOT NULL
CREATE TABLE geo (
  id INT,
  location GEOMETRY NOT NULL
);
CREATE SPATIAL INDEX idx_location ON geo(location);

Why this works

Spatial indexes require NOT NULL columns to guarantee valid geometry values.

Sources
Official documentation ↗

MySQL 8.0 — 1636 ER_SPATIAL_MUST_HAVE_GEOM_COL

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

← All MySQL errors