As an ex-DB guy, I am always interested to see how ServiceNow works under the hood.
Therefore, I did a little experiment on my PDI.
First I created a table called u_device with a single mandatory field u_imei:
And then I opened "Scripts - Background" and entered this simple script:
try {
var nRecords = 100;
var grDevice = new GlideRecord('u_device');
grDevice.setUseEngines(false);
for (var i = 0; i < nRecords; i++) {
grDevice.newRecord();
grDevice.insert();
}
} catch (error) {
gs.logError("Error in script:", error);
}What will this script do?
As expected, it inserts 100 records even tough I did not tell it a value for u_imei - since I switched off Data Policies using .setUseEngines(false) - or so I thought.
Then I went back to the script an executed:
try {
var nRecords = 100;
var grDevice = new GlideRecord('u_device');
for (var i = 0; i < nRecords; i++) {
grDevice.newRecord();
grDevice.insert();
}
} catch (error) {
gs.logError("Error in script:", error);
}What will this script do?
In contrast to what I and probably also you thought, it did not raise an exception, but it inserted the empty values just fine!
Honestly, that this is the case (note that the build tag of the instance is "glide-yokohama-12-18-2024__patch5-06-11-2025") astonished me quite a bit.
(If you ask Database Administrators, they will tell you that NOT NULL CONSTRAINT is one of the more important aspects of any decent database).
It seems that a mandatory field (set at data dictionary level is)
- Not Implemented as a NOT NULL CONSTRAINT (that I already knew)
- Not Implemented as a Data Policy (that was completely new for me)
- Only enforced in the UI
This means that it is a good idea to perform a check of all the fields that are set to mandatory (in my PDI, we are talking about 11k records):
I bet that you will find many records that are in fact null (use the ISEMPTY operator).
I could not let this be and created a script to check the tables. I even added a little sampling system for fun.
(Run at your own risk - ideally on a subprod instance).
/**
* Because mandatory fields are not enforced in all cases, we want to find out how many records have NULL values in mandatory fields.
* Run at your own risk - ideally on a subprod instance
* DD.MM.YYYY - Author - description
* 17.09.2025 - DOD - Created (Sidekick: Github Copilot)
*
* @function scanForNullsInMandatoryFields
* {integer} nTables - number of tables to sample
* {boolean} boolRandomSampling - if true, random sampling is used, otherwise the first nTables are used
* @returns {void}
* https://www.servicenow.com/community/admin-experience-forum/let-s-play-a-little-game-what-will-this-code-do/td-p/3381537
* Daniel C. Oderbolz
*/
function scanForNullsInMandatoryFields(nTables, boolRandomSampling) {
try {
if (typeof nTables === 'undefined' || nTables === null) {
nTables = 10; // Default to 10 tables
}
if (typeof boolRandomSampling === 'undefined' || boolRandomSampling === null) {
boolRandomSampling = true; // Default to random sampling
}
// First find out which tables have mandatory fields
var arrTablesWithMandatory = [];
var arrTablesWithNulls = [];
var ga = new GlideAggregate('sys_dictionary');
ga.addAggregate('COUNT');
// We filter out the thousands of var__ tables and syslog tables
ga.addEncodedQuery('mandatory=true^nameNOT LIKEvar__^nameNOT LIKEsyslog');
ga.groupBy('name');
ga.addHaving('COUNT', '>', 0);
ga.query();
while (ga.next()) {
arrTablesWithMandatory.push(ga.getValue('name'));
}
gs.log("Tables with mandatory fields: " + arrTablesWithMandatory.length);
if (nTables > arrTablesWithMandatory.length) {
gs.log("Requested number of tables to sample (" + nTables + ") exceeds the number of tables with mandatory fields (" + arrTablesWithMandatory.length + "). Sampling all available tables.");
nTables = arrTablesWithMandatory.length;
}
gs.log("Sampling " + nTables + " tables..." + (boolRandomSampling ? " (random sampling)" : " (first n tables)"));
var arrSampledTables = [];
// Create an array of nTables random table names in arrSampledTables
for (var i = 0; i < nTables; i++) {
if (boolRandomSampling) {
var nRandomIndex = Math.floor(Math.random() * arrSampledTables.length);
// Ensure we do not sample the same table twice
while (arrSampledTables.indexOf(arrTablesWithMandatory[nRandomIndex]) !== -1) {
nRandomIndex = Math.floor(Math.random() * arrTablesWithMandatory.length);
}
arrSampledTables.push(arrTablesWithMandatory[nRandomIndex]);
} else {
arrSampledTables.push(arrTablesWithMandatory[i]);
}
}
// Loop through the sampled tables and check for NULLS in mandatory fields
for (var j = 0; j < arrSampledTables.length; j++) {
var strTableName = arrSampledTables[j];
gs.log("Checking table: " + strTableName);
// Get the mandatory fields for the table
var arrMandatoryFields = [];
var grDict = new GlideRecord('sys_dictionary');
grDict.addEncodedQuery('name=' + strTableName + '^mandatory=true^internal_type!=collection');
grDict.query();
while (grDict.next()) {
arrMandatoryFields.push(grDict.getValue('element'));
}
// Check for NULLS in mandatory fields
var grTable = new GlideRecord(strTableName);
var strNullQuery = '';
for (var k = 0; k < arrMandatoryFields.length; k++) {
if (k > 0) {
strNullQuery += '^NQ';
}
strNullQuery += arrMandatoryFields[k] + 'ISEMPTY';
}
if (strNullQuery != '') {
if (grTable.isValidEncodedQuery(strNullQuery)) {
grTable.addEncodedQuery(strNullQuery);
grTable.setLimit(1); // We only need to know if there is at least one record
grTable.query();
if (grTable.next()) {
gs.log("Table " + strTableName + " has NULLS in at least one of those mandatory fields: " + arrMandatoryFields.join(', '));
arrTablesWithNulls.push(strTableName);
}
}
}
}
// Final report
gs.log("Sampled " + arrSampledTables.length + " tables.");
gs.log(arrTablesWithNulls.length + " tables have NULLS in mandatory fields: " + arrTablesWithNulls.join('\n'));
} catch (error) {
gs.logError("Error in script :", error);
gs.log(strTableName + " - " + strNullQuery);
}
}
// Test Run (100 Samples, random sampling)
scanForNullsInMandatoryFields(100, true);
// The real run, no sampling
scanForNullsInMandatoryFields(5045 , false);
I ran this on a subprod system in about 1 min on all the 5045 tables with mandatory fields I care about.
(It is left to the reader to add a corrective update to the script above - this indeed needs to be tested very well!)
Of those 5045 tables, 407 (8%) contained NULL values in mandatory fields, most of them are OOTB Tables, so some ServiceNow Developers are not aware of this issue:
ais_datasource_attribute ais_datasource_field_attribute ais_query_statistics alm_asset alm_stockroom appsec_security_notification_list asmt_condition asmt_metric asmt_metric_category asmt_metric_definition asmt_metric_type ast_contract awa_document_size awa_queue bsm_indicator business_calendar business_calendar_span catalog_channel_analytics cert_audit change_request change_task clm_m2m_contract_asset clone_data_exclude clone_data_preserver cmdb$par2 cmdb_ci_handheld_computing cmdb_data_management_policy cmdb_reconciliation_definition cmdb_software_product_model column_renderer comm_task content_config customer_contact cxs_table_config cxs_ui_action_config cxs_wizard_config discovery_credentials discovery_snmp_oid dynamic_scheduling_config em_alert em_alert_correlation_rule em_alert_history em_compose_field em_connector_instance em_impact_graph em_mapping_rule em_unique_nodes ga_decision_table_execution_context ga_decision_tree ga_decision_tree_execution ga_decision_tree_node ga_decision_tree_node_input ga_decision_tree_version ga_guidance_history gsw_change_log gsw_status_of_content guided_setup_rule_on_category help_guidance import_set_flow_engine_context incident instance interaction interaction_context isc_event isc_export_setting itfm_cost_allocation_metric itfm_data_source jwt_claim_validation jwt_keystore_aliases kb_knowledge kb_knowledge_base kb_submission kb_translation_task license_cust_table_allotment license_metadata life_cycle_mapping m2m_connected_content m2m_sys_nlu_intent_entity m2m_sys_nlu_model_sys_entity m2m_sys_suggestion_reader_sys_suggestion_reader_group ml_capability_definition_base ml_solution_definition oauth_entity oidc_provider_configuration one_api_feature_provider par_component_permission par_dashboard_user_metadata par_insight_user_action par_visualization_permission pa_breakdown_mappings pa_cached_forecast pa_dashboards pa_diagnostic pa_indicators pa_manual_breakdowns pa_score_notes pa_tabs pa_widgets planned_task_custom_console pm_portfolio_project proc_po proc_po_item project_status promin_activity_def promin_breakdown_stats promin_finding_def promin_project pwd_question question_choice quickactions_workspace_action resource_allocation resource_allocation_daily rm_release rm_story samp_discovered_user samp_downgrade_model samp_named_user_type samp_sw_publisher samp_sw_reclamation_candidate samp_sw_reclamation_rule samp_sw_usage sam_sw_product_lifecycle scan_check scan_finding scan_table_check sc_category sc_cat_item_producer sc_ic_column sc_ic_section sc_layout_column_mtom sm_template_definition sn_ace_app_config sn_ace_content_block sn_ace_content_block_type sn_ace_page sn_attr_prp_mgmt_attribute_mapping sn_bm_client_score sn_bm_common_indicator_activity sn_cmdb_ws_quick_links sn_csm_ec_chat_instance sn_csm_ec_feature sn_cs_telemetry_log sn_diagram_builder_diagram_template sn_diagram_builder_shape_property sn_diagram_builder_shape_property_override sn_escm_ws_cmn_score_calculator_rule sn_itom_integ_app_instance_details sn_kmf_resource_exchange_request sn_lf_task sn_nb_action_recommended_action sn_nb_action_search_result_ra_mapping sn_nb_action_type_definition sn_now_azure_user sn_now_teams_manifest sn_plng_att_core_doc_page sn_plng_att_core_resource_assignment sn_sec_analytics_health_dashboard_data sn_sec_cmn_approver_level sn_sec_cmn_calculator sn_sec_cmn_email_field sn_sec_cmn_enrichment_data_mapping_field sn_sec_cmn_field_mapping_field sn_sec_cmn_risk_score_audit sn_sec_cmn_security_tag_rule sn_slack_ah_v2_slack_configurations sn_sow_interceptor_record_type_selector sn_task_dependency_m2m sn_tcm_collab_hook_chat_config sn_templated_snip_note_template sn_ti_observable sn_vsc_event sn_vsc_export_event sn_vul_cwe sn_vul_integration sn_vul_pen_test_config sn_vul_rollup subscription_entitlement sysauto_pa sysevent_register sys_analytics_consent_policy sys_app_scan_payload sys_app_scan_variable sys_archive_destroy sys_atf_parameter_run sys_atf_test_result sys_auth_profile_basic sys_aw_list_attribute sys_aw_list_category sys_aw_module sys_aw_my_list sys_aw_renderer sys_cb_topic sys_choice sys_ci_analytics sys_complex_object sys_connection sys_cs_adapter_card_template sys_cs_auto_resolution_configuration sys_cs_channel_user_profile sys_cs_client_adapter sys_cs_collab_member sys_cs_collab_provider sys_cs_consumer_device_context sys_cs_custom_adapter_property sys_cs_custom_control_execution_plan sys_cs_media sys_cs_message sys_cs_message_last_read sys_cs_provider sys_cs_topic sys_cs_topic_library_usage sys_data_source sys_db_object sys_declarative_action_assignment sys_declarative_action_definition sys_dictionary sys_dictionary_override sys_dm_delete_count sys_es_latest_script sys_export_definition sys_export_target sys_flow_approver_listener_reference sys_flow_execution_setting sys_flow_step_definition sys_gen_ai_skill_config sys_hub_action_input_action_instance sys_hub_action_instance sys_hub_flow_base sys_hub_flow_block sys_hub_flow_logic_definition sys_hub_pill_compound sys_hub_resource_filter_rule sys_hub_trigger_instance sys_hub_trigger_instance_v2 sys_kmf_crypto_caller_policy sys_kmf_key_lifecycle_default_line sys_kmf_key_metadata sys_metric sys_metric_trigger_log sys_metric_trigger_log0000 sys_metric_trigger_log0001 sys_metric_trigger_log0006 sys_metric_trigger_log0007 sys_nlu_intent sys_nlu_model sys_nlu_vocabulary sys_notification_va_action sys_notification_va_content sys_nowmq_subject sys_nowmq_subject_param sys_now_assist_deployment_config sys_one_extend_builder_capability sys_one_extend_capability_definition sys_openapi sys_package sys_pd_activity sys_pd_activity_context sys_pd_derivative sys_pd_lane sys_physical_table_stats sys_platform_encryption_configuration sys_playbook_experience_activity_ui sys_polaris_configurable_menu_item sys_repo_config sys_rest_message_fn sys_robust_import_set_transformer sys_rte_eb_field_mapping sys_rte_eb_set_operation sys_scope_privilege sys_search_autocomplete_query_event sys_search_context_config sys_search_facet sys_search_filter sys_search_signal_event sys_sg_activity_stream_screen sys_sg_badge_count sys_sg_button sys_sg_button_instance sys_sg_chart sys_sg_icon sys_sg_icon_section sys_sg_item_view sys_sg_list_screen sys_sg_map_screen sys_sg_master_item sys_sg_redirect_dest_field sys_sg_related_list_map sys_sg_screen sys_sg_screen_field sys_sg_screen_param_map sys_sg_ui_parameter sys_sg_ui_style sys_sg_write_back_action_item sys_sg_write_back_action_step sys_suggestion_reader_group sys_uib_template sys_uib_toolbox_component sys_ui_policy sys_update_set sys_upgrade_plan_item sys_user_delegate sys_ux_auto_reflow_rule sys_ux_banner_announcement sys_ux_client_script sys_ux_component_preset sys_ux_composite_data sys_ux_composite_data_m2m_predicate_bundle sys_ux_composite_data_template_predicate sys_ux_diagnostic_rule sys_ux_event sys_ux_extension_point sys_ux_keyboard_shortcut sys_ux_lib_component_prop sys_ux_lib_source_script sys_ux_list sys_ux_macroponent sys_ux_my_list sys_ux_page_action sys_ux_page_action_binding sys_ux_page_element sys_ux_screen sys_ux_screen_type sys_ux_theme_customization sys_ux_theme_m2m_asset sys_workspace_declarative_action_exclusion sys_ws_operation task ts_configuration ua_custom_metric_defn ua_defn_agg_data ua_exempted_table_inventory usageanalytics_count_cfg user_calendar_event vtb_board vtb_card vtb_lane v_st_incident_alert_task widget_navigation wm_task_rejection ws_security_username_profile_outbound
If you are in a hurry, you can just sample about 56 tables with mandatory fields and you should have about a 99% chance of finding at least one instance of a NULL in such a table.
I can see why ServiceNow does not use a NOT NULL CONSTRAINT (these cannot be disabled on a per-transaction level) but why is this not a Data Policy?
What are your thoughts on this?
I created a Case for this and hope that this will be taken care of.
