Examine this statement and output: CREATE TABLE geom (g GEOMETRY NOT NOLL, SPATIAL INDEX(g) ); Query OK, 0 rows affected, 1 warning (0.01 sec) An attempt is made to add an SRID attribute to the…

Question

Options

• AAn error is generated because srid o is an invalid identifier value.
• BExecution succeeds with a warning.
• CAn error is generated because the index prevents changes to the column.
• DExecution succeeds and allows the use of the index by the optimizer.

• A
  11% (6)
• B
  20% (11)
• C
  64% (36)
• D
  5% (3)

Explanation

Option C is correct because MySQL does not permit altering a geometry column's SRID attribute while a SPATIAL INDEX exists on that column - the index must be dropped first, then the column modified, then the index recreated. The ALTER TABLE statement will fail with an error citing the conflicting index, not merely produce a warning.

Why the distractors are wrong:

• A is wrong because SRID 0 is a perfectly valid identifier in MySQL, representing a Cartesian (undefined) coordinate system - it is not an invalid value.
• B is wrong because execution does not succeed at all; the presence of the spatial index causes a hard error, not a warning.
• D is wrong because while adding an SRID constraint would theoretically help the optimizer use the spatial index, the statement never succeeds - you never reach that outcome.

Memory tip: Think "Spatial index = column frozen." Any attempt to add or change SRID restrictions on an indexed geometry column requires a drop-modify-recreate sequence. The index guards the column definition, not just the data.

No community discussion yet for this question.