Forem: Roxana Maria Haidiner

Custom data types in DbSchema’s Logical Design help architects enforce consistent column definitions across large models (e.g., all usernames use the same length everywhere).
Important: these types are design-time abstractions. When you deploy to a real database (e.g., MySQL), DbSchema generates the native DBMS type (such as VARCHAR(150)), not the custom name (UserName).

Open Data Type Settings

Open your project in DbSchema.
Go to Model → Settings → DBMS Specific → Data Types.
In the DBMS selector, choose Logical Design.

Define a Custom Data Type

Click + Add New.
Give the type a clear, descriptive name and set its length:

Examples:

MoneyAmount → DOUBLE, then mapped in Conversion Dictionary to DECIMAL(19,4).
CustomerCode → CHAR(10)
CreatedAt → TIMESTAMP
Click OK to save.

Map the Type for Deployment

Your Logical Design type needs a mapping so that, on deployment, DbSchema generates the correct native DBMS type and precision.

Open Model → Conversion Dictionary.
Click +Data Type, and choose the data type created earlier. (MoneyAmount)
Choose the target DBMS (e.g., MySQL).
Map it to the native type and set the exact precision (e.g., DECIMAL(19,4)).
Save.

Use the Custom Type in Your Model

In the column editor, select your custom type (e.g., MoneyAmount).
When you generate SQL or deploy, DbSchema applies the mapping and emits the native DBMS type.

Example:

The Logical Design types are now mapped, so deployment emits native DBMS types.

What Happens at Deployment?

In the model: you work with logical names like MoneyAmount, CustomerCode, CreatedAt.
On deployment: DbSchema automatically converts each logical type to the mapped native DBMS type (e.g., MoneyAmount → DECIMAL(19,4), CustomerCode → CHAR(10), CreatedAt → TIMESTAMP in MySQL).
No extra steps: the SQL generator uses your Conversion Dictionary; you can preview the exact DDL in Schema Compare / Generate SQL before applying.
In the database: developers will see only the native types (e.g., DECIMAL(19,4), CHAR(10), TIMESTAMP) produced by these mappings.

Troubleshooting

“Length required” prompts in MySQL UI: expected only if you use a DBMS-specific type. For Logical Design types, set the length in the type definition and the exact DBMS length in the Conversion Dictionary.
Custom name not visible in DB: by design. Logical Design names are model-level abstractions. The DB stores native types.

Summary

Create custom types in Logical Design to standardize columns.
Use the Conversion Dictionary to map each type to the exact native DBMS type (MoneyAmount → to DECIMAL(19,4) in MySQL).
On deployment, DbSchema converts Logical Design names to native types, ensuring consistency without leaking modeling abstractions into the database.

Next Step

Want to try this out yourself?
Download DbSchema Free Trial and start designing with custom data types today.

You may also find these guides helpful:

SQL GROUP BY Explained with Examples

Roxana Maria Haidiner — Wed, 24 Sep 2025 10:43:14 +0000

When you work with databases, you often want summaries instead of just raw rows.

How many orders did each customer make?
What are the total sales per department?
What is the average grade per student?

This is where SQL GROUP BY is useful.
It allows you to group rows that share the same value and then apply functions like COUNT(), SUM(), or AVG().

What You’ll Learn

In this guide, we’ll cover the main GROUP BY functions step by step:

COUNT() - Count number of rows
SUM() - Add up values
AVG() - Calculate averages
HAVING - Filter grouped results
JOIN - Use names instead of IDs
MIN() - Find the smallest value
MAX() - Find the largest value

Example Tables

Let’s use a simple shop database with customers and their orders.

GROUP BY with COUNT

Count how many orders each customer has placed.

SELECT customer_id, COUNT(*) AS order_count
FROM Orders
GROUP BY customer_id;

The Result:

Each customer_id is grouped, and the number of orders is counted.

GROUP BY with SUM

Now, let’s find the total spending of each customer.

SELECT customer_id, SUM(total_amount) AS total_spent
FROM Orders
GROUP BY customer_id;

The Result:

Each customer_id is grouped, and the SUM(total_amount) gives the total money they spent.

Customers Table

To make our reports more readable, let’s also create a Customers table with names for each customer:

customer_id	name
1	Sarah James
2	Mark White
3	Olivia Reed

This table can be joined with Orders using customer_id.
From now on, we’ll use JOINs so results will display names instead of IDs.

GROUP BY with AVG

You can also compute the average order value per customer (showing names via JOIN).

SELECT c.name, AVG(o.total_amount) AS avg_order
FROM Customers c
JOIN Orders o ON c.customer_id = o.customer_id
GROUP BY c.name;

The Result:

Sarah placed two orders worth 50 and 70 → the average is 60.

GROUP BY with HAVING

Sometimes, you want to filter after grouping.
This is where HAVING comes in (not WHERE).

SELECT c.name, SUM(o.total_amount) AS total_spent
FROM Customers c
JOIN Orders o ON c.customer_id = o.customer_id
GROUP BY c.name
HAVING SUM(o.total_amount) > 50;

The Result:

Only customers with spending over 50 are included.

GROUP BY with JOIN

JOIN helps you show labels instead of IDs in grouped results.

SELECT c.name, SUM(o.total_amount) AS total_spent
FROM Customers c
JOIN Orders o ON c.customer_id = o.customer_id
GROUP BY c.name;

The Result:

Use JOIN whenever you want readable names in your reports.

GROUP BY with MIN

Find the smallest order amount per customer (names via JOIN).

SELECT c.name, MIN(o.total_amount) AS min_order
FROM Customers c
JOIN Orders o ON c.customer_id = o.customer_id
GROUP BY c.name;

The Result:

This returns the lowest order amount for each customer.

GROUP BY with MAX

Find the largest order amount per customer (names via JOIN).

SELECT c.name, MAX(o.total_amount) AS max_order
FROM Customers c
JOIN Orders o ON c.customer_id = o.customer_id
GROUP BY c.name;

The Result:

This returns the highest order amount for each customer.

How to Do This Visually in DbSchema

With DbSchema, you don’t need to type SQL from scratch - you can build GROUP BY queries visually in the Query Builder:

Drag your tables (Customers and Orders) into the canvas.
Connect them by their foreign key.
Select the columns you want to display (e.g., name).
Right-click on a numeric column (like total_amount).
Choose Aggregate → SUM, COUNT, AVG, MIN, MAX.

DbSchema automatically adds the GROUP BY for you and shows the generated SQL alongside the diagram.
This makes it much easier to learn SQL because you see both the visual structure and the query code at the same time.

Summary

GROUP BY groups rows with the same value.
Combine with COUNT, SUM, AVG, MIN, MAX to summarize data.
Use HAVING to filter groups after aggregation.
Perfect for totals, averages, and category-based reports.

Learn More

Check out more SQL tutorials here:

Handle Large PostgreSQL Schemas with a GUI Tool

Roxana Maria Haidiner — Thu, 18 Sep 2025 13:24:51 +0000

When a database is small, everything looks simple.
You open the diagram, see a few tables, and it all makes sense.

At first, a database looks simple.
But as projects grow, so do the tables.
Soon, the schema feels heavy, and even small changes become hard to track.

This is where DbSchema makes the work easier.

1. Organize With Multiple Diagrams

DbSchema lets you split a big schema into smaller diagrams.
For example:

one diagram for Orders & Transactions,
another for Customers & Billing,
one more for HR.

The database is the same, but you look only at the part you need.
This keeps things clear even if the schema has more than 10,000 tables.

2. Connect to Multiple Databases

Most projects don’t have only one database.
You usually work with Dev, Stage, and Prod.

In DbSchema, you can save all connections in the same project and switch between them easily.

Example:

company_dev
company_stage
company_prod

One click, and you are connected to the right place.
No need to remember passwords or JDBC URLs each time.

3. Make Safe Changes

Changing a big schema is stressful.
Instead of running ALTER TABLE directly in production, do it step by step:

Change the table visually in the diagram.
Use Schema Compare to see what changed between the model and the database.
Let DbSchema generate a migration script.

Example migration:

ALTER TABLE company.orders
ADD COLUMN discount NUMERIC(5,2);

Review it, test in Dev, then apply in Prod.
This makes changes predictable and safe.

4. Documentation Everyone Can Read

Large schemas confuse not only developers but also analysts and new colleagues.
DbSchema can generate HTML5 documentation that is:

Complete - includes tables, columns, indexes, and foreign keys
Visual - shows diagrams for better understanding
Interactive - you can click and navigate between tables
Searchable - quickly find a column or table by name
Shareable - publish it on an internal site for the whole team

This way, everyone can explore the schema without asking you each time.

5. Automation Scripts

Large databases often need repetitive tasks:

creating new partitions every month,
checking if important indexes exist,
exporting schema snapshots for backups,
or cleaning up old data.

In DbSchema you can write Automation Scripts in Groovy.
They run directly from the project, so you don’t have to manage external tools.

Example: create next month’s partition automatically


def nextMonth = java.time.LocalDate.now().plusMonths(1)
def start = nextMonth.withDayOfMonth(1)
def end   = start.plusMonths(1)

def sql = """
CREATE TABLE company.transactions_${start.getYear()}_${String.format("%02d", start.getMonthValue())}
PARTITION OF company.transactions
FOR VALUES FROM ('${start}') TO ('${end}');
"""

db.execute(sql)
println "Partition for ${start.getMonth()} ${start.getYear()} created."

With scripts like this, you can:

keep partitions up to date,
schedule routine checks,
or generate data exports with a single click.

Wrap-Up

Large PostgreSQL databases are not easy to handle.
But with DbSchema you can:

split diagrams into smaller views,
work with multiple environments,
apply changes safely,
generate documentation,
and automate routine tasks.

This workflow keeps the database clear and manageable, even when it grows beyond 10,000 tables.

Try it yourself for free with DbSchema. Download here.

Create MySQL ER Diagrams with a Free Design Tool

Roxana Maria Haidiner — Wed, 27 Aug 2025 14:06:51 +0000

When learning or working with a database, the first step is to see how the tables connect. For MySQL users, the easiest way to do that is with an Entity-Relationship Diagram (ERD).
Instead of reading long SQL scripts, an ER diagram gives you a clear picture of tables, keys, and relationships.

In this guide, I’ll use DbSchema - a free design tool for creating ER diagrams.
It also includes extra features such as HTML5 documentation (for up to 12 tables in the free edition) and Git integration if you try the PRO version.

We’ll look at two main cases:

Reverse engineer an existing MySQL database
Build a new schema visually

What Is an ER Diagram?

An ER diagram is a drawing of your database.
It shows tables (entities), their columns, and how they are connected with primary and foreign keys.

It usually includes:

Table names and their columns
Keys (primary and foreign)
Relationships between tables
Notes, data types, and indexes

Why is this useful?

You can understand the database structure at a glance
It’s easier to explain it to others
Missing or wrong relationships become obvious
New colleagues can get onboarded faster
It helps when documenting or keeping track of changes

If You Already Have a Database

With DbSchema, you don’t need to draw everything by hand.
You can reverse engineer your MySQL database: the tool connects to your schema, reads the tables and foreign keys, and builds a diagram automatically.

Here’s an example of a MySQL schema as an ER diagram:

Want step-by-step instructions?
Check the documentation here

Designing a Schema Visually

Sometimes you don’t have a database yet, you just have an idea.
Instead of starting with CREATE TABLE statements, you can draw it out:

Add new tables with a click
Define columns, data types, and keys using simple forms
Drag lines between tables to create relationships
Rearrange the diagram for clarity

When ready, you can generate the SQL code automatically.

Learn more about designing schemas visually

1. Organize with Multiple Layouts

If your database only has a few tables, one diagram might be enough.
But once you start adding more, things can get messy.

In DbSchema, you can create multiple layouts of the same schema.
For example:

A diagram showing user and authentication tables
A separate one for products and orders
Another one for reporting or logs

All layouts still point to the same schema, but splitting them up keeps them clear and readable.
This is especially useful in large MySQL databases where hundreds of tables exist.

2. Keep It Synchronized

Databases are never static - columns get added, constraints change, and indexes are tuned.

With DbSchema’s Schema Compare feature, you can:

Compare the diagram with the live MySQL database
See exactly what changed (new tables, dropped columns, altered constraints)
Choose whether to update the diagram or apply changes back to the database

This prevents surprises and ensures the diagram and the real database stay in sync.
It’s like a two-way mirror - you can decide which side becomes the source of truth.

3. Use Git for Version Control

Your diagram is saved in a project file (.dbs).
This file can be committed to Git just like code, which means:

You can track every change (e.g., when a column is renamed or a new table is added)
Work in a team without overwriting each other’s changes
Roll back to earlier versions if a design decision didn’t work out
Keep schema design history alongside your application code

This makes database design a collaborative process, not just a one-person job.

4. Share with HTML5 Documentation

Not everyone on your team knows SQL, but they might still need to understand the database.

With DbSchema, you can export your ER diagram as interactive HTML5 documentation:

Opens in any browser (no installation required)
Tables are clickable - you can see all the details from the columns and constraints
Notes and comments are included
Great for sending to managers, analysts, or clients

It’s like publishing your schema online in a clickable, explorable form.

5. Notes and Comments

Sometimes a diagram isn’t enough. People want to know why something is designed in a certain way.

In DbSchema, you can add:

Notes on tables (e.g., "This table stores archived orders older than 2 years")
Notes on relationships (e.g., "One customer can have multiple addresses")
Comments on columns (e.g., "This column is auto-incremented for IDs")

These annotations are not just helpful for your teammates. They also show up in the exported HTML docs, making your schema easier to understand for anyone who reads it.

6. Export Options

Once you’re done, you can export your design in different ways:

Images (PNG, PDF) for reports or presentations
SQL scripts to deploy your schema
Interactive HTML5 for easy sharing with teammates

Benefits of Using DbSchema for MySQL ER Diagrams

Feature	Description
Free Version Available	Create diagrams without a license
Visual Interface	Build schemas without typing SQL
Offline Mode	Work even without a live MySQL connection
Schema Compare	Spot differences between model and database
HTML5 Documentation	Generate clickable docs
Git Integration	Keep track of schema changes in teams
Multi-Database Support	Works with PostgreSQL, Oracle, SQL Server, and more

Conclusion

For MySQL users, ER diagrams are the easiest way to understand and manage a schema.
They make relationships clear, reduce mistakes, and improve teamwork.

Whether you’re documenting an old database or planning a new one, drawing it visually makes the job simpler.

You can try all of this with the free version of DbSchema – a straightforward tool to design, organize, and share MySQL databases.

Download DbSchema here

SQL Joins Explained - INNER, LEFT, RIGHT, FULL

Roxana Maria Haidiner — Fri, 22 Aug 2025 19:31:45 +0000

When working with databases, your data is often stored in more than one table.
But how can you bring the data together?

This is what SQL JOINs are for.
A JOIN helps you combine rows from two tables based on a related column, usually a column that appears in both.

Types of SQL JOINs

Here are the most common types of JOINs and what they do:

JOIN Type	What it returns
INNER JOIN	Only rows that exist in both tables
LEFT JOIN	All rows from the left table and matching rows from the right table
RIGHT JOIN	All rows from the right table and matching rows from the left table
FULL JOIN	All rows from both tables

What Is a SQL JOIN?

Let’s say you have:

One table with a list of employees
Another table with a list of departments

Each employee works in a department. If we want to know who works where, we need to connect the tables using a JOIN.

The JOIN compares values in both tables and brings the matching rows together.

Example Tables

Table: Employees

employee_id	name	department_id
1	Sarah James	10
2	Mark White	20
3	Olivia Reed	NULL

Table: Departments

department_id	department_name
10	Engineering
20	Sales
30	HR

INNER JOIN

Returns only employees who are assigned to a department.

SELECT name, department_name
FROM Employees
INNER JOIN Departments
ON Employees.department_id = Departments.department_id;

➝

name	department_name
Sarah James	Engineering
Mark White	Sales

Employees without a department, and departments with no employees, are not shown.

LEFT JOIN

Returns all employees, even if they don’t belong to any department.

SELECT name, department_name
FROM Employees
LEFT JOIN Departments
ON Employees.department_id = Departments.department_id;

➝

name	department_name
Sarah James	Engineering
Mark White	Sales
Olivia Reed	NULL

You get all employees.
If they have no department, the result will show NULL.

RIGHT JOIN

Returns all departments, even if no employee is assigned to them.

SELECT name, department_name
FROM Employees
RIGHT JOIN Departments
ON Employees.department_id = Departments.department_id;

➝

name	department_name
Sarah James	Engineering
Mark White	Sales
NULL	HR

You get all departments.
If no one works there, the result shows NULL for the name.

FULL JOIN

Returns all employees and all departments.
If they match, they are shown together.
If not, one side will be NULL.

SELECT name, department_name
FROM Employees
FULL JOIN Departments
ON Employees.department_id = Departments.department_id;

➝

name	department_name
Sarah James	Engineering
Mark White	Sales
Olivia Reed	NULL
NULL	HR

This gives you the full picture.

Summary

Use INNER JOIN when you want only matching data
Use LEFT JOIN when you want all rows from the first table
Use RIGHT JOIN when you want all rows from the second table
Use FULL JOIN when you want everything, matched or not

How to Do This Visually in DbSchema

You can use DbSchema to build JOINs without writing any SQL.

Drag the two tables into the canvas
Connect them by dragging from one column to the other, to create a foreign key

Open the Query Builder and cascade related tables

Right-click the line to select the JOIN type
Choose the columns you want to display

Run the query and see the results

This is a great way to learn how JOINs work, because DbSchema auto-generates SQL code for you!

Note: The JOIN types shown in the menu depend on the database you are connected to.
Some databases (like SQLite) do not support RIGHT JOIN or FULL JOIN.
In these cases, DbSchema will only show the join types supported by your database.

Learn More

If you're learning SQL and want more examples, visit our full SQL Tutorial here:
[https://dbschema.com/blog/tutorials/]

How to Design a PostgreSQL Schema Visually (Step-by-Step)

Roxana Maria Haidiner — Wed, 20 Aug 2025 11:14:15 +0000

1. What is a Schema?

In PostgreSQL, a schema is just a folder inside your database where you keep your tables, views, and other objects. You can even have the same table name in different schemas. It’s best to name it after your app so it’s easy to keep things organized.

In our case, we’ll create a schema called school.

Example in SQL:

CREATE SCHEMA school;

In DbSchema tool, you can create it following these steps:

1. Start a new schema from scratch in the "Welcome Screen".
2. Select the PostgreSQL database and name the project (model) as you wish.
3. Right-click in the database tree → Create → Schema → give it a name, like school, in our case.

2. Why Visual Design Helps?

When you design visually a ER diagram:

You see all the tables and how they connect
Which columns are keys or have constraints
You can drag to create foreign keys (relationships)
The tool writes the SQL for you

3. Example Database

For our example, let’s design a database for a school system.
We’ll start with three main tables:

students - information about students
courses - information about courses
enrollments - which student is in which course

When adding columns, choose PostgreSQL-specific data types:

SERIAL - auto-incrementing integers, often used for primary keys
VARCHAR(n) - variable-length text
DATE - calendar dates
BOOLEAN - true/false values

1. Creating a Table with SQL

Usually, when working directly in PostgreSQL, you would create a table by writing SQL in the SQL editor.
For example, here’s how we can create a students table inside the school schema:

Example: students table

Why school.students?
You write the schema name first (school) because you can have multiple schemas in the same database, and this keeps your tables organized.

After running this SQL, DbSchema will show the table visually in the diagram, with all columns, keys, and data types.

2. Creating the Second Table Visually

For the next table, let’s use DbSchema’s visual design instead of writing SQL:

Right-click in the diagram area
Select Create Table
Type the table name – for example, courses
Add columns:
- course_id – Primary Key
- course_name
- start_date
- end_date
Mark the primary key column

The best part:
While you work visually, DbSchema shows you the SQL code it generates in real time. You can copy or run that code directly if needed.

4. Creating a Foreign Key in DbSchema

1. Creating a Foreign Key Using SQL

You can create a foreign key directly in the SQL Editor.
For example, linking enrollments.student_id to students.student_id:

Example for a cascade rule:

Once you run this command, DbSchema will update the diagram so you can see the foreign key visually.

2. Creating a Foreign Key Visually

You can also create foreign keys without writing SQL, using the diagram:

Drag from the student_id column in the enrollments table
Drop onto the student_id column in the students table
In the Edit Foreign Key dialog:
- Give the foreign key a name (e.g., fk_enrollments_students)
- Verify the referring and referred columns
- Optionally set On Delete or On Update actions
Click OK

The foreign key line will appear in the diagram, and DbSchema will generate the SQL automatically in the background.

4. Add Comments and Tags

While designing your schema in DbSchema, you can add:

Comments to tables or columns to explain their purpose
Tags to group related tables or mark certain objects for review

These notes are saved in your model and can be included in the generated documentation.
This is useful when working in teams, so everyone understands the meaning of each table and column.

5. Synchronize Schema Changes

If you are connected to your PostgreSQL server from the start, DbSchema will apply changes immediately as you create or edit tables.

If you design offline:

Complete your schema design
Go online and connect to PostgreSQL
Open the Synchronization dialog
Review the differences between your model and the database
Choose which changes to apply to PostgreSQL

This way, you can work without affecting the live database until you’re ready.

6. Save and Share Your Schema

Once your schema is ready, you can save it as a model file (.dbs).
This file is XML and contains your entire project, including tables, relationships, comments, and tags.

Working in a Team

You can store this .dbs file in a Git repository so multiple team members can work on the same schema.
DbSchema has Git Integration built in, making it easy to pull updates, merge changes, and keep everyone in sync.

Sharing the Design

You can share your work easily:

Export as interactive HTML5 documentation
- Includes your ER diagram and all schema details
- Displays table structures, relationships, and comments
No DbSchema required for viewers
- SVG image, which is interactive and low in size
- Team members or stakeholders can open it directly in their browser
- They can explore the diagram and table details interactively

Conclusion

A PostgreSQL schema is a way to organize your tables and other database objects so everything stays clear and structured.
You can create it with SQL commands or design it visually for a more intuitive experience.

If you want to try designing a PostgreSQL schema visually for free, download DbSchema.

What Is a Primary Key in SQL? Learn with Examples

Roxana Maria Haidiner — Fri, 08 Aug 2025 14:49:52 +0000

A primary key is a column in a database table that uniquely identifies each row.

That means no two rows can have the same value in this column, and it can’t be left empty.

It’s the most important way to organize your data and avoid duplicates.

Each table can have only ONE primary key.

It can be just one column or a few columns joined together to make each row unique.

Why Is It Important?

The primary key is essential for a few key reasons:

It makes sure each row is unique
It helps the database work faster when searching or linking data
It allows other tables to connect to this one through foreign keys
It prevents duplicate or missing records

Without a primary key, there’s no reliable way to know which row is which.

Example: Primary Key with Countries and Cities

Let’s look at a simple example using two tables: one for countries and one for cities.

Table 1: Countries

Each country has a short code (like "US" or "CH") that is unique. That code is used as the primary key.

CREATE TABLE Countries (
  country_code CHAR(2) PRIMARY KEY,
  name VARCHAR(100),
  continent VARCHAR(50)
);

In this case, country_code is the primary key. It identifies each country uniquely.

Table 2: Cities

Now, let’s say you want to store information about cities. Each city will belong to a country, and we’ll include a country_code column so we know where the city is located.

CREATE TABLE Cities (
  city_id INT PRIMARY KEY,
  name VARCHAR(100),
  population INT,
  country_code CHAR(2)
);

Here, city_id is the primary key for the Cities table. Each city gets a unique ID number, like 1, 2, 3, and so on.

Later, we can use the country_code in the Cities table to create a foreign key that connects each city to a real country, but that only works if the Countries table has a clear primary key defined.

If you want to learn more about how foreign keys work, check out this article:
What Is a Foreign Key

What Makes a Good Primary Key?

There are two main rules:

It must be unique - no two rows can have the same value
It must not be null - every row must have a value

If you try to insert a duplicate or missing value, the database will return an error.

Composite Primary Keys

Sometimes a single column isn’t enough to uniquely identify a row. In that case, you can use a composite primary key, which is made of two or more columns.

Here’s an example:

CREATE TABLE CarRegistrations (
  country_code CHAR(2),
  plate_number VARCHAR(10),
  registration_date DATE,
  PRIMARY KEY (country_code, plate_number)
);

In this case, a car’s license plate isn’t unique on its own - the same plate could exist in multiple countries.
But the combination of country_code and plate_number is unique, so it makes a perfect composite primary key.
This structure ensures no duplicates and keeps everything clearly linked.

Adding a Primary Key Later

You can also define a primary key after creating the table, using ALTER TABLE:

ALTER TABLE Countries
ADD PRIMARY KEY (country_code);

This is useful if you forget to define it at the beginning or need to apply it to existing data.

How to Work with Primary Keys in DbSchema

If you’re using DbSchema, you can define primary keys without writing any SQL.

Here’s how:

Create or import your table
Double-click on the column you want to set as the primary key
Click “Set as Primary Key”
You’ll see a small key icon on that column in the diagram

DbSchema also supports composite keys. Just select multiple columns when defining the key.

Working visually helps you understand your design faster, especially when working with multiple tables.

Summary

A primary key uniquely identifies each row in a table
It must be unique and NOT NULL
Other tables can link to it using foreign keys
You can define it during table creation or later using ALTER TABLE
Visual tools like DbSchema make it easy to design and understand primary keys

Understanding primary keys is the first step toward building well-structured, reliable databases.

Free SQL Tool to Understand Your Database Visually

Roxana Maria Haidiner — Fri, 08 Aug 2025 14:44:54 +0000

You might already know how to write basic SQL.

Let’s say you’ve written something like this:

CREATE TABLE customers (
  customer_id INT
);

That’s great, you've created a table.
But do you understand how it looks in the database?

Is it linked to another table?
What kind of data goes into that column?

- What happens when you add more fields or relationships?

This is where most people get stuck - you write code, but you don’t see the full picture.

That’s why tools like DbSchema include a free Community Edition, so you can learn faster by seeing how your SQL shapes the database in real time.

1. Real-Time Diagram Updates

As you write or modify SQL code, DbSchema updates the ER diagram automatically.

Instead of scrolling through raw SQL code or switching between tabs, you can see:

Which table connects to which
What foreign keys are in place
How is your schema structured as a whole

This is especially helpful when working with large databases, collaborating in teams, or just trying to get a quick understanding of how everything fits together.

2. Smart Autocomplete & Syntax Highlighting

DbSchema’s SQL editor helps you write better and faster:

Autocomplete suggestions as you type (table names, columns, commands)
Syntax highlighting so you can easily spot errors
Helpful tooltips and validation when something’s off

This reduces typos and lets you focus on logic, not memorization.

3. Explore Your Data Easily

Query results don’t just appear as plain text.

DbSchema shows them in a clean, spreadsheet-style grid.

Run queries and view results instantly
Edit data directly in the table
Sort and filter columns just like in Excel

Perfect for exploring datasets and testing results.

4. Save Results to File

Need to export your query results?

Just click the save button in the result panel.

DbSchema will rerun the query and write the full dataset to a file, even if it's too large to display on screen.

Ideal for:

Reports
Data backups
Sharing with colleagues

5. Instant SQL Generation from Diagrams

Already designed your schema visually?

You don’t have to write the SQL manually.

DbSchema automatically generates the full SQL script for each table or relationship.

You can copy, inspect, or tweak it any time, perfect for learning or reusing structures.

6. Execute Groovy and JavaScript in the SQL Editor

Yes, really - you can run both Groovy and JavaScript right inside the editor.

There’s a drop-down in the SQL editor where you can switch between SQL, Groovy, and JavaScript modes.

You can write scripts to:

Fetch or manipulate data
Generate reports or exports
Automate tasks or workflows using the language you're most comfortable with

DbSchema even includes built-in code samples to help you get started faster, just check the Help menu inside the application.

Conclusion

Most SQL editors just let you type queries.

DbSchema helps you understand your database while you write it.

From diagrams to scripts, from autocompletion to real data editing - it’s all built-in.

If you're still learning SQL and want to get more confident,

we’ve also prepared a full SQL tutorial with all the syntax you need:

Learn SQL - The Complete Syntax Guide

Ready to try it yourself?

Download DbSchema Free