Table Structure
TablePro provides a visual interface for exploring and modifying database table structures. View column definitions, indexes, foreign keys, and the DDL (CREATE TABLE statement) for any table.
Accessing Table Structure
To view a table’s structure:
Select a Table
Click on a table name in the sidebar
Open Structure Tab
Click the Structure tab (below the data grid)
Explore
Navigate between Columns, Indexes, Foreign Keys, and DDL tabs
Columns Tab
The Columns tab displays all column definitions:
| Property | Description |
|---|
| Name | Column name |
| Type | Data type (VARCHAR, INT, etc.) |
| Nullable | Whether NULL values are allowed |
| Default | Default value if none specified |
| Extra | Additional attributes (AUTO_INCREMENT, etc.) |
| Key | Primary key (PRI), Foreign key (FOR), etc. |
Copy as TSV
Right-click the structure grid to copy column definitions as TSV (tab-separated values). This is useful for pasting into spreadsheets or documentation tools.
Data Types by Database
MySQL/MariaDB
PostgreSQL
SQLite
Common MySQL data types:| Category | Types |
|---|
| Numeric | INT, BIGINT, DECIMAL, FLOAT, DOUBLE |
| String | VARCHAR, TEXT, CHAR, ENUM |
| Date/Time | DATE, DATETIME, TIMESTAMP, TIME |
| Binary | BLOB, BINARY, VARBINARY |
| JSON | JSON |
Common PostgreSQL data types:| Category | Types |
|---|
| Numeric | INTEGER, BIGINT, NUMERIC, REAL |
| String | VARCHAR, TEXT, CHAR |
| Date/Time | DATE, TIMESTAMP, TIMESTAMPTZ, TIME |
| Binary | BYTEA |
| Special | JSON, JSONB, ARRAY, UUID, INET |
SQLite type affinities:| Affinity | Description |
|---|
| TEXT | String data |
| INTEGER | Signed integers |
| REAL | Floating point |
| NUMERIC | Numeric or text |
| BLOB | Binary data |
Indexes Tab
View all indexes defined on the table:
| Property | Description |
|---|
| Name | Index name |
| Columns | Columns included in the index |
| Type | BTREE, HASH, FULLTEXT, etc. |
| Unique | Whether the index enforces uniqueness |
| Primary | Whether this is the primary key |
Index Types
| Type | Usage |
|---|
| PRIMARY | Primary key constraint |
| UNIQUE | Unique constraint/index |
| INDEX | Regular index for performance |
| FULLTEXT | Full-text search (MySQL) |
| SPATIAL | Geospatial data (PostGIS) |
Foreign Keys Tab
View foreign key relationships:
| Property | Description |
|---|
| Name | Constraint name |
| Column | Local column(s) |
| References | Target table and column |
| On Delete | Action when referenced row is deleted |
| On Update | Action when referenced row is updated |
Referential Actions
| Action | Description |
|---|
| RESTRICT | Prevent delete/update if referenced |
| CASCADE | Delete/update referencing rows |
| SET NULL | Set referencing column to NULL |
| SET DEFAULT | Set to default value |
| NO ACTION | Similar to RESTRICT (deferred check) |
DDL Tab
View the CREATE TABLE statement for the table:
Copying DDL
- Open the DDL tab
- Select all (
Cmd+A)
- Copy (
Cmd+C)
Use DDL for:
- Recreating tables in another database
- Version control of schema changes
- Documentation
- Creating similar tables
DDL Examples
CREATE TABLE `users` (
`id` int NOT NULL AUTO_INCREMENT,
`email` varchar(255) NOT NULL,
`name` varchar(100) DEFAULT NULL,
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
UNIQUE KEY `email` (`email`),
KEY `created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE users (
id SERIAL PRIMARY KEY,
email VARCHAR(255) NOT NULL UNIQUE,
name VARCHAR(100),
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP WITH TIME ZONE
);
CREATE INDEX idx_users_created_at ON users(created_at);
CREATE TABLE users (
id INTEGER PRIMARY KEY AUTOINCREMENT,
email TEXT NOT NULL UNIQUE,
name TEXT,
created_at TEXT DEFAULT CURRENT_TIMESTAMP,
updated_at TEXT
);
CREATE INDEX idx_users_created_at ON users(created_at);
| Property | Description |
|---|
| Engine | Storage engine (MySQL: InnoDB, MyISAM) |
| Collation | Character collation |
| Row Count | Approximate row count |
| Data Size | Table data size |
| Index Size | Index data size |
| Comment | Table comment/description |
Modifying Structure
Structure modifications alter your database schema. Always backup important data before making changes.
Visual Structure Editor
TablePro provides a visual interface for modifying table structure directly — no SQL required:
Adding Columns
- Open the Structure tab > Columns
- Click the + button to add a new column
- Set column properties: name, type, nullable, default, auto-increment, comment
- Click Apply to preview and execute the changes
Modifying Columns
- Click on a column in the Columns tab
- Edit properties in the detail panel
- Changes are tracked with undo/redo support
- Click Apply to preview the ALTER TABLE SQL
Removing Columns
- Select the column to remove
- Click the - button or press Delete
- Confirm the removal
- Apply changes
Schema Change Preview
Before applying structure changes, TablePro shows a preview of the generated ALTER TABLE SQL. This allows you to:
- Review the exact SQL that will be executed
- Verify the changes match your intent
- Cancel if the changes are not what you expected
Undo/Redo
Structure changes support full undo/redo:
- Press
Cmd+Z to undo a change
- Press
Cmd+Shift+Z to redo a change
- Changes are tracked until applied to the database
- See Change Tracking for details
Adding Columns via SQL
You can also add columns directly with SQL:
-- MySQL
ALTER TABLE users ADD COLUMN phone VARCHAR(20) AFTER email;
-- PostgreSQL
ALTER TABLE users ADD COLUMN phone VARCHAR(20);
-- SQLite (limited ALTER TABLE)
ALTER TABLE users ADD COLUMN phone TEXT;
Modifying Columns via SQL
-- MySQL
ALTER TABLE users MODIFY COLUMN name VARCHAR(200) NOT NULL;
-- PostgreSQL
ALTER TABLE users ALTER COLUMN name TYPE VARCHAR(200);
ALTER TABLE users ALTER COLUMN name SET NOT NULL;
-- SQLite (requires table recreation)
-- See SQLite documentation for workarounds
Dropping Columns via SQL
-- MySQL/PostgreSQL
ALTER TABLE users DROP COLUMN phone;
-- SQLite (requires table recreation)
Managing Indexes
-- Create index
CREATE INDEX idx_users_email ON users(email);
-- Create unique index
CREATE UNIQUE INDEX idx_users_username ON users(username);
-- Drop index
DROP INDEX idx_users_email ON users; -- MySQL
DROP INDEX idx_users_email; -- PostgreSQL
Managing Foreign Keys
-- Add foreign key
ALTER TABLE orders
ADD CONSTRAINT fk_orders_user
FOREIGN KEY (user_id) REFERENCES users(id)
ON DELETE CASCADE;
-- Drop foreign key
ALTER TABLE orders DROP FOREIGN KEY fk_orders_user; -- MySQL
ALTER TABLE orders DROP CONSTRAINT fk_orders_user; -- PostgreSQL
Refreshing Structure
After making schema changes:
- Via sidebar: Right-click the table > Refresh
- Via menu: View > Refresh
- Automatically: Changes made in TablePro refresh automatically
Best Practices
Schema Design
- Use appropriate data types: Choose the smallest type that fits your data
- Add indexes for queries: Index columns used in WHERE and JOIN
- Define foreign keys: Maintain referential integrity
- Use NOT NULL: When columns should always have values
- Add defaults: Provide sensible defaults for optional columns
Naming Conventions
| Element | Convention | Example |
|---|
| Tables | Plural, snake_case | users, order_items |
| Columns | Singular, snake_case | user_id, created_at |
| Indexes | idx_table_columns | idx_users_email |
| Foreign Keys | fk_table_reference | fk_orders_user |
| Primary Keys | id or table_id | id, user_id |
- Primary keys: Every table should have a primary key
- Foreign key indexes: Index foreign key columns
- Composite indexes: Order columns by selectivity
- Avoid over-indexing: Indexes slow down writes
Troubleshooting
Structure Not Loading
- Check database connection
- Verify user has permissions to view schema
- Refresh the connection
Missing Columns
- Refresh the table structure
- Check if column was added in a transaction not yet committed
- Verify you’re looking at the correct database/schema
DDL Syntax Errors
When copying DDL to another database:
- Check database-specific syntax differences
- Adjust data types for target database
- Remove unsupported features (e.g., MySQL ENGINE for PostgreSQL)
Next Steps
