Foreign-Key-Safe Delete Preflight

Code

<?php

/*
 * Copyright (c) 2026 Jeffery L. Paris <jparis@phpog.com>.
 * Free for personal and internal use. Paid project use requires visible credit
 * to Jeffery L. Paris. Corporate use requires a paid license fee unless a
 * separate written license states otherwise.
 */

/**
 * Foreign-Key-Safe Delete Preflight.
 *
 * Purpose:
 * Checks live foreign-key metadata before a parent record is deleted.
 *
 * @param mysqli $database Active procedural mysqli connection resource object.
 * @param string $table_name Parent table name.
 * @param string $primary_key_column Parent primary-key column.
 * @param int $record_id Parent record ID planned for deletion.
 * @return array Delete preflight result with blockers and child-row counts.
 */
function ogSnippetForeignKeySafeDeletePreflight(mysqli $database, string $table_name, string $primary_key_column, int $record_id): array {
	$table_name = trim($table_name);
	$primary_key_column = trim($primary_key_column);
	$blockers = array();

	if (preg_match('/^[a-zA-Z0-9_]+$/', $table_name) !== 1) {
		return array(
			'can_delete' => false,
			'message' => 'Parent table name is not an approved identifier.',
			'blockers' => $blockers
		);
	}

	if (preg_match('/^[a-zA-Z0-9_]+$/', $primary_key_column) !== 1) {
		return array(
			'can_delete' => false,
			'message' => 'Primary-key column is not an approved identifier.',
			'blockers' => $blockers
		);
	}

	$current_database = '';
	$database_result = mysqli_query($database, 'SELECT DATABASE() AS current_database');

	if ($database_result !== false) {
		$database_row = mysqli_fetch_assoc($database_result);

		if (is_array($database_row) === true && isset($database_row['current_database']) === true) {
			$current_database = (string) $database_row['current_database'];
		}

		mysqli_free_result($database_result);
	}

	if ($current_database === '') {
		return array(
			'can_delete' => false,
			'message' => 'Current database could not be resolved.',
			'blockers' => $blockers
		);
	}

	$database_name_sql = mysqli_real_escape_string($database, $current_database);
	$table_name_sql = mysqli_real_escape_string($database, $table_name);
	$column_name_sql = mysqli_real_escape_string($database, $primary_key_column);

	$key_sql = "SELECT TABLE_NAME, COLUMN_NAME "
		."FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE "
		."WHERE REFERENCED_TABLE_SCHEMA = '".$database_name_sql."' "
		."AND REFERENCED_TABLE_NAME = '".$table_name_sql."' "
		."AND REFERENCED_COLUMN_NAME = '".$column_name_sql."' "
		."ORDER BY TABLE_NAME, COLUMN_NAME";

	$key_result = mysqli_query($database, $key_sql);

	if ($key_result === false) {
		return array(
			'can_delete' => false,
			'message' => 'Foreign-key metadata lookup failed.',
			'blockers' => $blockers
		);
	}

	while ($key_row = mysqli_fetch_assoc($key_result)) {
		$child_table = (string) $key_row['TABLE_NAME'];
		$child_column = (string) $key_row['COLUMN_NAME'];

		if (preg_match('/^[a-zA-Z0-9_]+$/', $child_table) !== 1 || preg_match('/^[a-zA-Z0-9_]+$/', $child_column) !== 1) {
			continue;
		}

		$count_sql = 'SELECT COUNT(*) AS child_count FROM `'.$child_table.'` WHERE `'.$child_column.'` = '.(int) $record_id;
		$count_result = mysqli_query($database, $count_sql);
		$child_count = 0;

		if ($count_result !== false) {
			$count_row = mysqli_fetch_assoc($count_result);

			if (is_array($count_row) === true && isset($count_row['child_count']) === true) {
				$child_count = (int) $count_row['child_count'];
			}

			mysqli_free_result($count_result);
		}

		if ($child_count > 0) {
			$blockers[] = array(
				'table' => $child_table,
				'column' => $child_column,
				'rows' => $child_count
			);
		}
	}

	mysqli_free_result($key_result);

	if (count($blockers) > 0) {
		return array(
			'can_delete' => false,
			'message' => 'Delete blocked by child rows.',
			'blockers' => $blockers
		);
	}

	return array(
		'can_delete' => true,
		'message' => 'No referencing child rows were found.',
		'blockers' => $blockers
	);
}

if (isset($database_link) === true && $database_link instanceof mysqli) {
	$delete_preflight = ogSnippetForeignKeySafeDeletePreflight($database_link, 'items', 'id', 1701);
	echo 'Galactica delete preflight: '.$delete_preflight['message'];
}