Database structure and relationships

6 minute read · SQL Client

The database structure explains how the app stores information. Inspect it before asking an agent to add a field or change a relationship.

• Inspect structure before changing it
• Review a schema change in context
• Troubleshoot missing or unexpected metadata
• Explore the shape of an unfamiliar app
• Structure: column controls
• Indexes and foreign keys
• Table Info, routines and definitions

Inspect structure before changing it#

Select a table and open Structure. Review columns, types, defaults, nullability, primary/unique keys and indexes. Use the available CREATE definition when you need the engine's actual representation. The relationships/diagram view helps understand how tables connect; it is metadata visualization of the selected database.

For an article app, identify the article primary key and any author/category relationship. Check whether the foreign-key column is nullable and indexed before asking an agent to change the relationship. An apparent line in a design sketch is not proof that the database enforces a foreign key.

Review a schema change in context#

Adding, editing or dropping a supported structure uses driver-specific DDL. MySQL/MariaDB, PostgreSQL and SQLite do not support every operation in the same way. Read the operation and target before confirming it. A useful request is: “Inspect the article table and propose an index for this existing query; show the intended change before executing it.”

For an application maintained through migrations, coordinate the schema change with its source migration so another checkout can reproduce it. A manual table edit may fix one local database while leaving the app's setup history inconsistent.

Troubleshoot missing or unexpected metadata#

If a schema, routine or relation is missing, check the selected database and account privileges. Some engines expose different metadata or require a schema selection. Refresh after an authorized structure change. Unsupported engine features should be reported rather than emulated silently.

Before a destructive DDL action, use the project's appropriate backup/export procedure and review dependencies such as foreign keys and app queries. A diagram does not make dropping a table reversible. After a change, inspect the resulting schema and rerun the app behavior it supports. Continue with import and export.

Explore the shape of an unfamiliar app#

Choose one table, such as articles, and inspect column names, types, defaults, nullability and keys. Open its indexes before deciding a slow filter needs application changes. Follow a foreign-key relationship to the related table and use the diagram to see how records connect. The diagram reflects available database metadata; a relationship used only in application code may not appear as a declared foreign key.

Inspect the CREATE definition and supported routines when you need the database's exact representation. Driver capabilities differ, so an unavailable structure operation should remain unavailable instead of being approximated as another engine's SQL.

Before changing a column or index, check the application's migration and any existing rows that would violate the change. Describe the intended outcome to the agent and ask for the proposed schema operation first. Apply an approved change to the intended development database, refresh Structure, then read and save a sample record through the app. Keep a recovery path for data-changing DDL. Checks and publishing explains why a successful schema command alone is not a completed feature.

Related guides: saved data, moving data.

Structure: column controls#

Select a table, then Structure. Column rows show Name, Type, Null, Key, Default, Extra, Encoding and Collation. The key icon identifies a primary key. Copyable metadata cells and their context menu let you reuse an exact name or definition.

If a column edit fails, read the database error. An incompatible type, duplicate values for a unique constraint or insufficient privileges requires a different resolution; repeatedly pressing Save will not correct the definition.

Indexes and foreign keys#

Add index asks for Index name, Type and one or more columns. Column order matters for a compound index; each entry can specify ASC or DESC where supported. Drop index removes the selected index after confirmation. An index speeds some reads but also costs storage and write work.

In Relations, inspect the constraint name and referenced table/column. Add foreign key collects:

For the actions offered by your engine: RESTRICT / NO ACTION prevents an operation that would violate the relationship; CASCADE propagates it to child rows; SET NULL clears the child value and requires a nullable column. SET DEFAULT, where supported, uses a valid default. Database engines differ in support and timing. Cascading a delete can remove many rows even though you clicked on one parent. SQLite relationship changes are not exposed through the same add/drop controls as MySQL/PostgreSQL; use a reviewed migration for unsupported changes.

Table Info, routines and definitions#

After applying a schema change, refresh the table and exercise the affected application form or API. Store the equivalent migration in the project so another checkout can reproduce it. See data editing and exports.