Using a SRID as column constraint

SRID constraints allow you to place restrictions on the values that can be stored in a spatial column. 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.

In order to include a spatial column in an index, the column must have a SRID constraint. For example, 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);
Note

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.