src/main/java/com/conveyal/gtfs/loader/JdbcTableWriter.java
private void ensureReferentialIntegrity(
        ObjectNode jsonObject,
        String namespace,
        Table table,
        Integer id
    ) throws SQLException {
        final boolean isCreating = id == null;
        String keyField = table.getKeyFieldName();
        String tableName = String.join(".", namespace, table.name);
        if (jsonObject.get(keyField) == null || jsonObject.get(keyField).isNull()) {
            // FIXME: generate key field automatically for certain entities (e.g., trip ID). Maybe this should be
            // generated for all entities if null?
            if ("trip_id".equals(keyField)) {
                jsonObject.put(keyField, UUID.randomUUID().toString());
            } else if ("agency_id".equals(keyField)) {
                LOG.warn("agency_id field for agency id={} is null.", id);
                int rowSize = getRowCount(tableName, connection);
                if (rowSize > 1 || (isCreating && rowSize > 0)) {
                    throw new SQLException("agency_id must not be null if more than one agency exists.");
                }
            } else {
                throw new SQLException(String.format("Key field %s must not be null", keyField));
            }
        }
        String keyValue = jsonObject.get(keyField).asText();
        // If updating key field, check that there is no ID conflict on value (e.g., stop_id or route_id)
        TIntSet uniqueIds = getIdsForCondition(tableName, keyField, keyValue, connection);
        int size = uniqueIds.size();
        if (size == 0 || (size == 1 && id != null && uniqueIds.contains(id))) {
            // OK.
            if (size == 0 && !isCreating) {
                // FIXME: Need to update referencing tables because entity has changed ID.
                // Entity key value is being changed to an entirely new one.  If there are entities that
                // reference this value, we need to update them.
                updateReferencingTables(namespace, table, id, keyValue);
            }
        } else {
            // Conflict. The different conflict conditions are outlined below.
            if (size == 1) {
                // There was one match found.
                if (isCreating) {
                    // Under no circumstance should a new entity have a conflict with existing key field.
                    throw new SQLException(
                        String.format("New %s's %s value (%s) conflicts with an existing record in table.",
                                      table.entityClass.getSimpleName(),
                                      keyField,
                                      keyValue)
                    );
                }
                if (!uniqueIds.contains(id)) {
                    // There are two circumstances we could encounter here.
                    // 1. The key value for this entity has been updated to match some other entity's key value (conflict).
                    // 2. The int ID provided in the request parameter does not match any rows in the table.
                    throw new SQLException("Key field must be unique and request parameter ID must exist.");
                }
            } else if (size > 1) {
                // FIXME: Handle edge case where original data set contains duplicate values for key field and this is an
                // attempt to rectify bad data.
                String message = String.format(
                        "%d %s entities shares the same key field (%s=%s)! Key field must be unique.",
                        size,
                        table.name,
                        keyField,
                        keyValue);
                LOG.error(message);
                throw new SQLException(message);
            }
        }
    }
private void ensureReferentialIntegrity(
        ObjectNode jsonObject,
        String namespace,
        Table table,
        Integer id
    ) throws SQLException {
        final boolean isCreating = id == null;
        String keyField = table.getKeyFieldName();
        String tableName = String.join(".", namespace, table.name);
        if (jsonObject.get(keyField) == null || jsonObject.get(keyField).isNull()) {
            // FIXME: generate key field automatically for certain entities (e.g., trip ID). Maybe this should be
            // generated for all entities if null?
            if ("trip_id".equals(keyField)) {
                jsonObject.put(keyField, UUID.randomUUID().toString());
            } else if ("agency_id".equals(keyField)) {
                LOG.warn("agency_id field for agency id={} is null.", id);
                int rowSize = getRowCount(tableName, connection);
                if (rowSize > 1 || (isCreating && rowSize > 0)) {
                    throw new SQLException("agency_id must not be null if more than one agency exists.");
                }
            } else {
                throw new SQLException(String.format("Key field %s must not be null", keyField));
            }
        }
        String keyValue = jsonObject.get(keyField).asText();
        // If updating key field, check that there is no ID conflict on value (e.g., stop_id or route_id)
        TIntSet uniqueIds = getIdsForCondition(tableName, keyField, keyValue, connection);
        int size = uniqueIds.size();
        if (size == 0 || (size == 1 && id != null && uniqueIds.contains(id))) {
            // OK.
            if (size == 0 && !isCreating) {
                // FIXME: Need to update referencing tables because entity has changed ID.
                // Entity key value is being changed to an entirely new one.  If there are entities that
                // reference this value, we need to update them.
                updateReferencingTables(namespace, table, id, keyValue);
            }
        } else {
            // Conflict. The different conflict conditions are outlined below.
            if (size == 1) {
                // There was one match found.
                if (isCreating) {
                    // Under no circumstance should a new entity have a conflict with existing key field.
                    throw new SQLException(
                        String.format("New %s's %s value (%s) conflicts with an existing record in table.",
                                      table.entityClass.getSimpleName(),
                                      keyField,
                                      keyValue)
                    );
                }
                if (!uniqueIds.contains(id)) {
                    // There are two circumstances we could encounter here.
                    // 1. The key value for this entity has been updated to match some other entity's key value (conflict).
                    // 2. The int ID provided in the request parameter does not match any rows in the table.
                    throw new SQLException("Key field must be unique and request parameter ID must exist.");
                }
            } else if (size > 1) {
                // FIXME: Handle edge case where original data set contains duplicate values for key field and this is an
                // attempt to rectify bad data.
                String message = String.format(
                        "%d %s entities shares the same key field (%s=%s)! Key field must be unique.",
                        size,
                        table.name,
                        keyField,
                        keyValue);
                LOG.error(message);
                throw new SQLException(message);
            }
        }
    }
Checks for modification of GTFS key field (e.g., stop_id, route_id) in supplied JSON object and ensures
both uniqueness and that referencing tables are appropriately updated.

FIXME: add more detail/precise language on what this method actually does