Error
Error Code:
3716
MySQL Error 3716: Modify SRID Dependency Conflict
Description
Error 3716 indicates that you are attempting to modify or drop a Spatial Reference System Identifier (SRID) definition that is currently referenced by one or more spatial columns in your database. MySQL prevents this operation to maintain data integrity and prevent data corruption in your spatial data.
Error Message
Can't modify SRID %u. There is at least one column depending on it.
Known Causes
3 known causesSRID Actively Assigned to Columns
The Spatial Reference System Identifier (SRID) you are trying to modify is currently assigned to one or more spatial data type columns in your database tables.
Attempt to Drop Used SRID
You are attempting to remove an SRID definition using `DROP SPATIAL REFERENCE SYSTEM` while it is still actively referenced by existing spatial columns.
System-Managed SRID
The SRID you are attempting to modify or drop might be a default or system-managed SRID that MySQL prevents users from altering to maintain database integrity.
Solutions
3 solutions available1. Identify and Drop Dependent Columns medium
Find columns referencing the SRID and remove them before modification.
1
Identify the SRID you are trying to modify. The error message will provide this value (e.g., %u). Let's assume it's SRID 1234 for this example.
2
Query the information schema to find all columns that depend on the specified SRID.
SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME
FROM information_schema.COLUMNS
WHERE SRID = 1234; -- Replace 1234 with your SRID3
For each identified dependent column, you have two primary options:
1. **Drop the column:** If the column is no longer needed, dropping it is the simplest solution.
2. **Modify the column to use a different SRID:** If the data is still relevant, you might need to alter the column to reference a different SRID before proceeding.
1. **Drop the column:** If the column is no longer needed, dropping it is the simplest solution.
2. **Modify the column to use a different SRID:** If the data is still relevant, you might need to alter the column to reference a different SRID before proceeding.
4
If you choose to drop a column (e.g., `column_name` in `table_name` within `table_schema`):
**WARNING: This will permanently delete data. Ensure you have backups or have exported the data if necessary.**
**WARNING: This will permanently delete data. Ensure you have backups or have exported the data if necessary.**
ALTER TABLE `table_schema`.`table_name` DROP COLUMN `column_name`;5
After ensuring no columns depend on the SRID, you can attempt to modify the SRID again.
2. Drop and Recreate the SRID medium
Remove the problematic SRID and then re-add it with the desired properties.
1
Identify the SRID you are trying to modify. Let's assume it's SRID 1234.
2
First, you must identify and deal with any dependent columns. Refer to 'Identify and Drop Dependent Columns' solution for steps on how to do this.
3
Once all dependent columns are removed, you can drop the SRID.
DROP SRID 1234; -- Replace 1234 with your SRID4
Then, recreate the SRID with its desired definition. You'll need to know the original definition (e.g., its name, units, and spatial reference system identifier).
CREATE SRID 1234 NAME 'MyNewSRID' USING 'EPSG:4326'; -- Example: Replace with actual definition5
After recreating the SRID, you can re-add any necessary columns, potentially associating them with the newly created SRID if that was the intention.
3. Alter Dependent Columns to Use a Different SRID advanced
Change the SRID of existing columns instead of modifying the target SRID.
1
Identify the SRID you are trying to modify (e.g., SRID 1234) and the columns that depend on it. Use the query from the 'Identify and Drop Dependent Columns' solution.
SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME
FROM information_schema.COLUMNS
WHERE SRID = 1234; -- Replace 1234 with your SRID2
For each dependent column, determine a suitable alternative SRID. This might involve converting spatial data if the SRIDs have different coordinate systems.
3
Alter each dependent column to use the new SRID. You might need to re-project the spatial data if the SRID definitions are incompatible.
ALTER TABLE `table_schema`.`table_name` MODIFY COLUMN `column_name` GEOMETRY SRID new_srid_value; -- Replace with actual values and potentially data transformation4
Once all columns have been altered to use different SRIDs, you should be able to modify the original SRID (e.g., SRID 1234) without conflict.