Exploring Master-Detail Data Across Foreign Keys

Checking one record pulls you into the rows hanging off it, then into the rows hanging off those, and each step is a join you write, run and throw away. DbSchema's Relational Data Editor does that walking instead: open a parent table, click a row, and every child pane refilters itself to the rows that belong to it, through every related table the foreign keys reach.

What is master-detail?

Master-detail is one row in a parent table owning many rows in a child table, where the child rows carry no meaning apart from the parent they belong to. An order and its lines are the case everyone meets first, and the queries below run on these three MySQL 8.4 tables:

CREATE TABLE customers (
  customer_id INT PRIMARY KEY,
  name        VARCHAR(60) NOT NULL
);
CREATE TABLE orders (
  order_id    INT PRIMARY KEY,
  customer_id INT NOT NULL,
  placed_on   DATE NOT NULL,
  CONSTRAINT orders_customer_fk FOREIGN KEY (customer_id)
    REFERENCES customers (customer_id) ON DELETE RESTRICT
);
CREATE TABLE order_items (
  item_id  INT PRIMARY KEY,
  order_id INT NOT NULL,
  product  VARCHAR(60) NOT NULL,
  amount   DECIMAL(8,2) NOT NULL,
  CONSTRAINT items_order_fk FOREIGN KEY (order_id)
    REFERENCES orders (order_id) ON DELETE CASCADE
);
INSERT INTO customers VALUES (1, 'Ada'), (2, 'Grace');
INSERT INTO orders VALUES (10, 1, '2026-03-01'), (11, 2, '2026-03-04');
INSERT INTO order_items VALUES
  (100, 10, 'Keyboard', 40.00),
  (101, 10, 'Mouse', 12.50),
  (102, 10, 'Cable', 6.00),
  (103, 11, 'Monitor', 180.00);

customers is the master of orders, and orders is in turn the master of order_items, so a table can be the detail in one relationship and the master in the next. Both relationships are one to many, and the NOT NULL on the referencing column is what says a detail row cannot exist without its master. Notice what order_items does not have: a customer column. The customer is reachable through the order, and storing it a second time would create a second place for the same fact to go wrong.

What a master-detail report puts in front of you

A report over these tables has to show the parent and its children together, which in plain SQL means joining them and accepting that the parent columns repeat:

SELECT o.order_id, o.placed_on, o.customer_id, i.product, i.amount
FROM orders o
JOIN order_items i ON i.order_id = o.order_id
ORDER BY o.order_id, i.item_id;

Order 10's date and customer arrive three times, once per line. A master-detail layout prints them once, as a header, with the three lines under it, which is the same data with the repetition taken out. That matters beyond tidiness: any figure that belongs to the order rather than to the line is now present three times in the result set, so a sum over it counts the order three times. Reconciling a total against its lines is the job this layout exists for, and it is why an analyst reaches for it whenever the question spans a parent and its children.

How the database enforces the master-detail relationship

The relationship is a primary key on the master and a foreign key on the detail that references it. The engine then refuses a detail row whose reference points at nothing, and each constraint carries an ON DELETE and an ON UPDATE referential action that decides what happens to the detail rows when the master row goes[1].

To read the keys already in a database, ask the catalog. information_schema.referential_constraints holds one row per foreign key with its delete rule, and information_schema.key_column_usage holds one row per column of the key, so a two-column key produces two rows there[2]:

SELECT rc.constraint_name, kcu.table_name, kcu.column_name,
       kcu.referenced_table_name, kcu.referenced_column_name, rc.delete_rule
FROM information_schema.referential_constraints rc
JOIN information_schema.key_column_usage kcu
  ON  kcu.constraint_schema = rc.constraint_schema
  AND kcu.constraint_name   = rc.constraint_name
WHERE rc.constraint_schema = DATABASE()
ORDER BY kcu.referenced_table_name, kcu.ordinal_position;

Two rows, two relationships, and the delete rules that separate them: deleting a customer who has orders is refused, while deleting an order takes its lines with it. Double-clicking a relationship line on the DbSchema diagram opens the same pair of settings in the Foreign Key Editor, and changing them there while a connection is open changes the constraint in the database.

How to convert master-detail to lookup?

A lookup is the looser relationship: the referenced table is an independent thing, such as a country, a status or a customer, and the referencing row survives without it. Guest checkout is the case that forces the conversion, since an order then has no customer at all, and closing an account must not delete the orders that account placed. Three statements move orders from detail to lookup:

ALTER TABLE orders DROP FOREIGN KEY orders_customer_fk;
ALTER TABLE orders MODIFY customer_id INT NULL;
ALTER TABLE orders ADD CONSTRAINT orders_customer_fk
  FOREIGN KEY (customer_id) REFERENCES customers (customer_id) ON DELETE SET NULL;

Run the catalog query again and that constraint reads differently:

The order rows now outlive the customer, with a null where the reference was. SET NULL needs a nullable column, which is why the MODIFY comes before the new constraint rather than after it. Where the constraint cannot be added at all, on a legacy schema or a database that enforces no keys, drag one column onto another in the DbSchema diagram and DbSchema saves a virtual foreign key in the model file, leaving the database untouched.

The master-detail interface that refilters as you click

A master-detail interface is a set of grids where selecting a row in the parent reloads the child grids against it. DbSchema's Relational Data Editor is one, and it works over real and virtual foreign keys alike.

Choose New Relational Data Editor from the Editors menu, or right-click a table header on the diagram and choose Open in Relational Data Editor, and DbSchema opens the table in the Tools panel at the bottom of the screen. Click the foreign key button on the table header panel and DbSchema adds the child table as a further pane, filtered to the row selected in the parent. Click a different parent row and every pane below reloads, and the descent continues for as long as the keys lead somewhere: customer, order, line, shipment, each one a click.

Click a column header and DbSchema opens the filter dialog for that column, so one pane narrows without touching the panes above it. The Relational Data Editor writes as well as reads, through Insert, Edit and Delete on the pane, and nothing you change reaches the database until you press Commit, while Rollback discards it. What it changes is rows: the tables and their constraints are untouched by anything you do in a grid. The Relational Data Editor is part of the Pro edition.

The four report layouts and where master-detail fits

Reports over related tables come in four layouts, and the choice follows the shape of the data rather than its subject. The question to ask first is whether the reader needs the parent row at all, and then whether they need two of them at once.

Take master-detail whenever the relationship is one to many, which the flat result earlier in this article shows the reason for: the parent columns repeat once per child row, so the export grows and any parent-level figure is counted once per line. A flat table earns its place when the details are all you want, an inventory dump or a list of transactions with nothing above them, and it is the only one of the four that an export to CSV survives unchanged, since a file with one header row has nowhere to put a second level.

Side by side and sub-report sit on top of one of the first two rather than replacing them. Side by side puts two of the same layout next to each other, which is how last quarter is compared with this one. A sub-report embeds a whole report inside a row of another, which is how an invoice carries a payment history under its line items. Both start from a decision already made about whether the parent belongs on the page.

Following the keys instead of writing the joins

Every query on this page was written out by hand, to show what the walking costs when no editor does it for you. Two tables took one join; a real investigation crosses five or six tables, and the joins get rewritten at each hop as the question changes.

DbSchema removes both halves of that work. Browsing across relationships is the Relational Data Editor, and building a statement you want to keep is the Query Builder, where following a foreign key adds the table and writes the join for you. Both are saved into the model file and reopened from the Editors menu, so the arrangement you set up for one investigation is there for the next, and neither leaves anything behind on the server. Working without writing SQL is the point, and reading the generated statement is how you check it.

Point DbSchema at your own orders table, or whatever plays that part in your schema, and click a row instead of writing the join. Download it at https://dbschema.com/download.html. The Relational Data Editor, the Query Builder and saving either of them into the model file are in the Pro edition; connecting, reverse-engineering and the interactive diagrams are in the free Community edition.

Frequently asked questions

What is the difference between a master-detail and a lookup relationship?

A master-detail relationship couples the lifecycles: the detail row cannot exist without its master, and deleting the master usually cascades. A lookup is a nullable reference to an independent table, so the referencing row stays when the referenced one is deleted or was never there.

Can I explore master-detail data without writing SQL joins?

Yes, DbSchema's Relational Data Editor refilters every child pane when you select a row in the parent, cascading down each level the foreign keys reach. Where the database declares no keys, virtual foreign keys drawn in the DbSchema diagram drive the same browsing.

Does exploring data in DbSchema alter my production database?

The DbSchema Relational Data Editor reads and writes rows, never structure, so your tables and their constraints are untouched by it. An edit you make in a grid is held until you press Commit, and Rollback discards it.

What referential actions protect master-detail data?

RESTRICT blocks deleting a master row that still has details, and CASCADE deletes the details with it. An ON DELETE or ON UPDATE clause you leave out defaults to NO ACTION, which InnoDB treats as RESTRICT, so an undeclared action already refuses the delete.

Sources

1. dev.mysql.com
2. dev.mysql.com