ToolsHub
Developer Tools

SQL Foreign Key Generator

Many databases grow with *_id columns that were never declared as foreign keys, so nothing stops rows from pointing at records that no longer exist. Paste your schema and the generator finds those columns, checks that the types match, writes queries that find orphaned rows, and adds the constraints with the delete behaviour you choose, plus the indexes they need.

  • Runs in your browser
  • No sign-up
  • Free to use
Start from an example
Options

    How to use SQL Foreign Key Generator

    1. Paste the CREATE TABLE statements.
    2. Review the detected relations and add any others.
    3. Choose ON DELETE and ON UPDATE behaviour.
    4. Fix orphaned rows, then run the ALTER TABLE statements.

    SQL Foreign Key Generator features

    Detection

    customer_id → customers.id and similar naming patterns.

    Type check

    Warns when the column types do not match.

    Orphan check

    Finds rows whose parent does not exist.

    Delete behaviour

    RESTRICT, CASCADE, SET NULL or NO ACTION.

    Indexes

    Adds indexes where the database does not create them.

    Large tables

    NOT VALID + VALIDATE in PostgreSQL; table rebuild for SQLite.

    When to use SQL Foreign Key Generator

    • Adding integrity constraints to a legacy schema.
    • Auditing a database for missing relations.
    • Cleaning up orphaned rows before a migration.
    • Learning how foreign key actions work.

    SQL Foreign Key Generator FAQ

    How are relations detected?

    A column named customer_id is matched to a table customers (or customer) with a primary key. Columns that match no table are listed so you can add them manually.

    Why check for orphans first?

    Adding a foreign key fails if any existing row points to a missing parent. The orphan queries show which values to fix or delete.

    Which ON DELETE action should I choose?

    RESTRICT prevents deleting a parent that still has children, CASCADE deletes the children too, and SET NULL keeps them with an empty reference (the column must allow NULL).

    Why add indexes?

    PostgreSQL, SQLite and SQL Server do not index foreign key columns automatically. Without an index, deleting a parent or joining scans the whole child table.

    What does NOT VALID do?

    In PostgreSQL it adds the constraint for new rows immediately and lets VALIDATE CONSTRAINT check existing rows later without blocking writes.

    Is anything executed?

    No. The SQL is generated in your browser.

    SQL ER Diagram Generator Paste the CREATE TABLE statements of a database and see its structure as an entity… SQL ALTER TABLE Generator Instead of writing ALTER TABLE statements by hand, paste the table as it is and as… SQL JOIN Generator Joining several tables means knowing which columns connect them. Paste your CREATE… SQL Index Generator Paste a slow query and get the indexes it needs. The generator reads the WHERE… SQL DELETE Query Generator Deleting data is the one SQL operation you cannot undo by running another query.… SQL CREATE TABLE Generator Design a table once and see how to create it in four databases. The same columns…

    Making relations explicit

    A foreign key constraint tells the database that a column refers to a row in another table, and the database then guarantees it: inserts with a missing parent fail, and deleting a parent follows the rule you chose. Without the constraint, the relation exists only in application code, and orphaned rows accumulate.

    The generator scans your schema for columns that look like references, ending in _id, without a foreign key. It matches each to a table by name, using the plural form as most schemas do, and to that table’s primary key. Columns it cannot match are listed so you can declare them yourself in the extra relations box.

    Before a constraint can be added, existing data must satisfy it. The orphan check queries use a LEFT JOIN to find child rows whose parent is missing and count them per value, so you can decide whether to fix, reassign or delete them. Type mismatches between the column and the referenced key are reported too, since MySQL in particular refuses mismatched types.

    The ALTER TABLE statements use the action you choose for ON DELETE and ON UPDATE, translated where needed: SQL Server writes NO ACTION in place of RESTRICT. Indexes are added for the new foreign keys in databases that do not create them, and PostgreSQL can add constraints as NOT VALID and validate them separately to avoid long locks on large tables.

    SQLite cannot add a constraint to an existing table, so the generator writes the rebuild: a new table with the foreign keys, a copy of the data, a rename and a final PRAGMA foreign_key_check.

    Other useful tools