Use MySQL Foreign Key Constraints in Custom WordPress Plugin Tables

Foreign key constraints enforce referential integrity at the database level — when a plugin creates custom tables that reference WordPress core tables (e.g., a wp_bookings table with a user_id column referencing wp_users.ID), a foreign key constraint ensures the database rejects any insert or update that would create an orphaned row, and defines what happens to child rows when a parent row is deleted (CASCADE, SET NULL, or RESTRICT). This database-level enforcement complements application-level validation and catches integrity violations that bypass the application entirely — direct database modifications, buggy batch import scripts, or multi-plugin interactions where one plugin deletes a WordPress post without another plugin being notified. InnoDB (the default MySQL storage engine since MySQL 5.5.5) supports foreign keys; MyISAM does not — all custom plugin tables should use ENGINE=InnoDB explicitly. A foreign key is defined in the CREATE TABLE statement with FOREIGN KEY (local_column) REFERENCES parent_table(parent_column) ON DELETE CASCADE ON UPDATE CASCADE, and both columns must have matching data types and collations. ON DELETE CASCADE automatically deletes child rows when the referenced parent row is deleted — appropriate for records that have no meaning without the parent (e.g., booking items without a booking). ON DELETE SET NULL sets the foreign key column to NULL when the parent is deleted — appropriate for optional relationships where child records remain valid without a parent (e.g., a log entry whose author was deleted). ON DELETE RESTRICT (the default) prevents parent deletion if child rows exist — appropriate when orphaned children would be a data integrity problem. WordPress’s dbDelta() function for table creation does not handle foreign key syntax well — add foreign keys with a separate ALTER TABLE statement after dbDelta() runs. The window functions post showed advanced query patterns; this post covers the schema integrity layer that makes those queries reliable.

Problem: A WordPress plugin creates wp_bookings and wp_booking_items tables — when a user account is deleted, booking records remain with a user_id pointing to a non-existent user, breaking the booking admin screen with PHP errors; deleting posts linked to bookings similarly leaves orphaned references with no cascade cleanup.

Solution: Create the custom tables with InnoDB engine and define foreign keys to wp_users and wp_posts with appropriate ON DELETE behaviors — SET NULL for the optional user reference and CASCADE for booking items that must not outlive their parent booking.

register_activation_hook( __FILE__, 'myplugin_create_tables' );

function myplugin_create_tables(): void {
    global $wpdb;
    $charset_collate = $wpdb->get_charset_collate();

    // ── Step 1: create tables with dbDelta (no FK syntax here) ────────────
    require_once ABSPATH . 'wp-admin/includes/upgrade.php';

    $sql_bookings = "CREATE TABLE {$wpdb->prefix}bookings (
        id           BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
        user_id      BIGINT(20) UNSIGNED DEFAULT NULL,
        post_id      BIGINT(20) UNSIGNED NOT NULL,
        booked_at    DATETIME             NOT NULL DEFAULT CURRENT_TIMESTAMP,
        status       VARCHAR(20)          NOT NULL DEFAULT 'pending',
        total        DECIMAL(10,2)        NOT NULL DEFAULT 0.00,
        PRIMARY KEY (id),
        KEY idx_user_id (user_id),
        KEY idx_post_id (post_id),
        KEY idx_status  (status)
    ) ENGINE=InnoDB {$charset_collate};";

    $sql_items = "CREATE TABLE {$wpdb->prefix}booking_items (
        id          BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
        booking_id  BIGINT(20) UNSIGNED NOT NULL,
        product_id  BIGINT(20) UNSIGNED NOT NULL,
        quantity    SMALLINT  UNSIGNED  NOT NULL DEFAULT 1,
        unit_price  DECIMAL(10,2)       NOT NULL,
        PRIMARY KEY (id),
        KEY idx_booking_id (booking_id)
    ) ENGINE=InnoDB {$charset_collate};";

    dbDelta( $sql_bookings );
    dbDelta( $sql_items );

    // ── Step 2: add foreign keys with ALTER TABLE ──────────────────────────
    // Check if FK already exists to make activation idempotent
    $fk_exists = $wpdb->get_var( $wpdb->prepare(
        "SELECT COUNT(*) FROM information_schema.TABLE_CONSTRAINTS
         WHERE CONSTRAINT_SCHEMA = %s AND TABLE_NAME = %s AND CONSTRAINT_NAME = %s",
        DB_NAME, $wpdb->prefix . 'bookings', 'fk_bookings_user'
    ) );

    if ( ! $fk_exists ) {
        // SET NULL: keep booking record if user is deleted (audit trail)
        $wpdb->query( "ALTER TABLE {$wpdb->prefix}bookings
            ADD CONSTRAINT fk_bookings_user
            FOREIGN KEY (user_id) REFERENCES {$wpdb->users}(ID)
            ON DELETE SET NULL ON UPDATE CASCADE" );

        // CASCADE: delete booking when the associated post/resource is deleted
        $wpdb->query( "ALTER TABLE {$wpdb->prefix}bookings
            ADD CONSTRAINT fk_bookings_post
            FOREIGN KEY (post_id) REFERENCES {$wpdb->posts}(ID)
            ON DELETE CASCADE ON UPDATE CASCADE" );
    }

    $fk_items_exists = $wpdb->get_var( $wpdb->prepare(
        "SELECT COUNT(*) FROM information_schema.TABLE_CONSTRAINTS
         WHERE CONSTRAINT_SCHEMA = %s AND TABLE_NAME = %s AND CONSTRAINT_NAME = %s",
        DB_NAME, $wpdb->prefix . 'booking_items', 'fk_items_booking'
    ) );

    if ( ! $fk_items_exists ) {
        // CASCADE: delete items when their parent booking is deleted
        $wpdb->query( "ALTER TABLE {$wpdb->prefix}booking_items
            ADD CONSTRAINT fk_items_booking
            FOREIGN KEY (booking_id) REFERENCES {$wpdb->prefix}bookings(id)
            ON DELETE CASCADE ON UPDATE CASCADE" );
    }
}

// Drop FKs and tables on uninstall to avoid constraint errors
register_deactivation_hook( __FILE__, function(): void {
    global $wpdb;
    $wpdb->query( "ALTER TABLE {$wpdb->prefix}booking_items DROP FOREIGN KEY fk_items_booking" );
    $wpdb->query( "ALTER TABLE {$wpdb->prefix}bookings DROP FOREIGN KEY fk_bookings_post" );
    $wpdb->query( "ALTER TABLE {$wpdb->prefix}bookings DROP FOREIGN KEY fk_bookings_user" );
    $wpdb->query( "DROP TABLE IF EXISTS {$wpdb->prefix}booking_items" );
    $wpdb->query( "DROP TABLE IF EXISTS {$wpdb->prefix}bookings" );
} );

NOTE: Foreign key constraints on wp_users.ID and wp_posts.ID can cause unexpected errors when WordPress’s own operations (user deletion, post deletion) try to delete rows referenced by your plugin’s tables. Always define ON DELETE behavior explicitly — never rely on RESTRICT (the implicit default) on references to WordPress core tables, as it will block core operations and show mysterious database errors when admins delete users or posts. The safest approach is ON DELETE SET NULL for user references with a nullable user_id column, combined with a WordPress delete_user action hook that handles any additional application-layer cleanup before the foreign key constraint is evaluated.