# 2.10.3. CITYDB_CONSTRAINT¶

The CITYDB_CONSTRAINT packages includes stored procedures to define constraints or change their behavior. A user can temporarily disable certain foreign key relationships between tables, e.g. the numerous references to the SURFACE_GEOMETRY table. The constraints are not dropped. While it comes at the risk of data inconsistency it can improve the performance for bulk write operations such as huge imports or the deletion of thousands of city objects.

It is also possible to change the delete rule of foreign keys from ON DELETE NO ACTION (use ‘a’ as input) to ON DELETE SET NULL (‘n’) or ON DELETE CASCADE (‘c’). Switching the delete rule will remove and recreate the foreign key constraint. The delete rule does affect the layout of automatically generated delete scripts as no explicit code is necessary in case of cascading deletes. However, we do not recommend to change the behavior of existing foreign key relationships because some delete operations might not work properly anymore. For Oracle databases, there is an additional procedure to define spatial metadata for single geometry column. All functions are schema-aware and their return type is void.

 Function Explanation set_column_sdo_metadata (geom_column_name, dimension, srid, table_name, schema_name) Inserts a new entry in the USER_SDO_GEOM_METADATA view for a given geometry column set_enabled_fkey (fkey_name, table_name, BOOLEAN, schema_name) Disables / enables a given foreign key constraint set_enabled_geom_fkeys (BOOLEAN, schema_name) Disables / enables all foreign key constraints that reference the SURFACE_GEOMETRY table set_enabled_schema_fkeys (BOOLEAN, schema_name) Disables / enables all foreign key constraints within a given user schema set_fkey_delete_rule (fkey_name, table_name, column_name, ref_table, ref_column, on_delete_param, schema_name) Changes the delete rule of a given foreign key constraint set_schema_fkey_delete_rule (on_delete_param, schema_name) Changes the delete rule of all foreign key constraint within a given user schema set_schema_sdo_metadata (schema_name) Inserts new entries in the USER_SDO_GEOM_METADATA view for all geometry columns of a given schema (some expections)

There is only one significant difference in the API in PostgreSQL. Instead of specifying the name, table and schema of a foreign key, the OID of the corresponding integrity trigger is enough. This is because there is no ALTER TABLE command in PostgreSQL to disable foreign keys.

 Function Explanation set_enabled_fkey (fkey_trigger_oid, BOOLEAN) Disables / enables a foreign key constraint trigger