Adding SRID column constraints

SRID constraints allow you to place restrictions on the values that can be stored in a spatial column. To include a spatial column in an index, the column must have a SRID constraint, which can be added using the CREATE TABLE and ALTER TABLE statements.

Prerequisites

Must be a user with permissions to create or alter a table.

If you add a spatial column to a table, you should make sure that the table has a primary key defined. Update and delete operations are not supported for a table that contains a spatial column unless a primary key is defined.

Context and remarks

Spatial columns cannot be included in a primary key, unique index, or unique constraint.

Add SRID column constraints

Execute a CREATE TABLE or ALTER TABLE statement that includes the SRID constraint for the spatial column.

CREATE TABLE Test (
   ID INTEGER PRIMARY KEY,
   Geometry_1 ST_Geometry,
   Geometry_2 ST_Geometry(SRID=4326),
   );

Results

The SRID constraint is added to the spatial column in the table.

Next

You can include the spatial column in an index.

Example

For example, execute the following statement to create a table named Test with a SRID constraint (SRID=4326) on the Geometry_2 column:

CREATE TABLE Test (
   ID INTEGER PRIMARY KEY,
   Geometry_1 ST_Geometry,
   Geometry_2 ST_Geometry(SRID=4326),
   );

This constraint means that only values associated with SRID 4326 can be stored in this column.

The column Geometry_1 is unconstrained and can store values associated with any SRID.

You cannot create an index on the Geometry_1 column. However, you can create an index on the Geometry_2 column.

If you have a table with an existing spatial column, you can use the ALTER TABLE statement to add a SRID constraint to a spatial column. For example, execute a statement similar to the following to add a constraint to the Geometry_1 column in the table named Test:

ALTER TABLE Test 
   MODIFY Geometry_1 ST_Geometry(SRID=4326);

Adding SRID column constraints

Add SRID column constraints

See also