Tables, Columns and Indexes

A table that already holds data needs a new column, and the database expects an ALTER TABLE statement in its own syntax. In DbSchema you fill in a dialog instead, and DbSchema writes the SQL and runs it. You work on tables from the diagram in the middle of the window or from the tree on the left, which lists each schema with its tables, views and other objects.

The pictures on this page use a small shop database in PostgreSQL 17: customers, orders, products and the tables around them.

Create a table

A table needs at least one column before DbSchema creates it. Without one, OK shows "Please add at least one column by pressing the '+' button."

1. Click an empty spot on the diagram, where the new table should go.
2. Choose Table → Create Table in the toolbar. You can also press Ctrl+T, or right-click the diagram and choose Create Table.
3. Type the name in Table Name.
4. In the Columns tab, press Add for each column, as Add a column shows.
5. Press OK.

The new table shows on the diagram and in the tree. If you're connected, OK creates the table in the database at once. Offline, the table stays in the model, as Check the SQL before it runs explains.

Edit a table

Double-click the table header on the diagram to open the Edit Table dialog. OK applies your changes to the model and, if you're connected, to the database. Each tab holds one part of the table:

Tab	What it holds
Columns	The columns, with their data types and comments
Primary Key / Unique / Indexes	The primary key, the unique keys and the indexes
Foreign Keys	The links from this table to other tables
Check Constraints	The conditions that each row must meet
Options	Clauses appended to CREATE TABLE, such as TABLESPACE
Scripts	The Prepend Script and Append Script, SQL added before and after the CREATE TABLE

Foreign Keys covers the Foreign Keys tab. The Comment box above the tabs describes the table. If you're connected, DbSchema writes it to the database as a COMMENT ON TABLE statement, and it shows it in the HTML5 documentation. The Options tab applies only when the table is created, so on an existing table its clauses change nothing in the database.

Virtual, at the top right, keeps the table in the model only: DbSchema never creates a virtual table in the database.

Add a column

1. Double-click the table header.
2. In the Columns tab, press Add.
3. Type the column name.
4. Choose the data type, such as varchar, integer or date.
5. Press OK in the column dialog, then OK in the table dialog.

In a table that already exists, OK in the column dialog adds the column at once. If you're connected, DbSchema runs the change in the database right then, and Cancel in the table dialog afterwards leaves the column in place. For the clip above, DbSchema ran this in PostgreSQL:

ALTER TABLE shop.customers ADD birth_date date;
COMMENT ON COLUMN shop.customers.birth_date IS 'Used for birthday offers';

The column dialog holds the settings of one column:

Field	What it sets
Length, or Precision	The size: Length for text, such as 100 in varchar(100), Precision for numbers
Decimal	The digits after the decimal point
Primary Key	Whether the column belongs to the primary key
Mandatory ( Not Null )	Whether every row must have a value
Unsigned	Whether negative numbers are refused, where the database has unsigned types
Identity	Whether the database numbers new rows, as GENERATED BY DEFAULT AS IDENTITY does
Default	The value that a new row gets when the insert gives none

Unsigned stays disabled on PostgreSQL, which has no unsigned types. Now() fills Default with the current date or time: CURRENT_DATE for a date column.

Set the primary key

A primary key is the column, or set of columns, that identifies each row. No two rows can share its value, and the value can never be empty.

To make one column the primary key, open its column dialog and tick Primary Key. A primary key over several columns, such as order_id and product_id in order_items, is an index of the type Primary Key:

1. Double-click the table header and open the Primary Key / Unique / Indexes tab.
2. Press Add.
3. Choose Primary Key.
4. Press Add under Columns for each column of the key.
5. Press OK in both dialogs.

A key icon marks the primary key columns on the diagram. What the icons mean lists the other column icons.

Create an index

An index lets the database find rows by a column without reading the whole table. A unique index also refuses a second row with the same value.

1. Double-click the table header.
2. Open the Primary Key / Unique / Indexes tab.
3. Press Add.
4. Choose Primary Key, Unique Key, Unique Index or Index.
5. Press Add under Columns and pick a column. Repeat for each column of the index.
6. Press OK in the index dialog, then OK in the table dialog.

A magnifying glass marks the indexed columns on the diagram, such as customer_id in orders, and a fingerprint marks the columns of a unique key or a unique index, such as email in customers.

A Unique Key is a constraint, and the database builds an index for it. A Unique Index is an index alone. In PostgreSQL 17 both refuse a duplicate value and both accept any number of empty values, but only the unique key is listed among the table's constraints. With products_sku_key as a unique key on products.sku, a second 'A-1' fails with:

ERROR:  duplicate key value violates unique constraint "products_sku_key"
DETAIL:  Key (sku)=(A-1) already exists.

Index an expression

An index can hold an expression instead of a column. A unique index on lower(email) makes email addresses unique whatever their letter case.

1. In the index dialog, open the arrow next to Add under Columns.
2. Choose Add Expression.
3. Type the expression, such as lower(email), and press OK.

DbSchema creates the index with:

CREATE UNIQUE INDEX customers_email_lower ON shop.customers ( lower(email) );

With that index in place, PostgreSQL 17 refuses '[email protected]' when '[email protected]' is already there:

ERROR:  duplicate key value violates unique constraint "customers_email_lower"
DETAIL:  Key (lower(email::text))=([email protected]) already exists.

Sort or filter an index

For the type Index, the index dialog on PostgreSQL has an Options (editable) column and a WHERE Clause & Storage Options field. The Options (editable) cell of a column takes ASC or DESC, and the field below the columns takes a WHERE condition, which limits the index to the rows that match it.

For the index in the picture, DbSchema ran:

CREATE INDEX orders_new_recent ON shop.orders ( ordered_at DESC ) WHERE status = 'NEW';

Check the SQL before it runs

DbSchema keeps the schema in a model, and whether a change also reaches the database depends on the connection.

When you're connected, each OK runs its change in the database at once. To work on the model alone, choose Database → Offline (connect later): the toolbar then shows Offline in place of the connection name, and the changes stay in the model. Later, synchronize the model with the database to review the changes and apply them.

To read the table's SQL before you press OK, press SQL at the bottom of the table dialog. The View SQL Statement window shows the table's whole CREATE TABLE statement, with its comments. For an existing table, OK runs only the change, such as the ALTER TABLE in Add a column.

The model lives in a .dbs file. Save it with File → Save Model to File, or Ctrl+S. Saving the model to a file needs DbSchema Pro.

Drop a table

Drop and Exclude from Diagram both take a table off the diagram, but only Drop deletes the table.

1. Right-click the table header on the diagram.
2. Choose Drop, or press Ctrl+Delete.
3. Press Drop, then OK when DbSchema warns that the operation may lose data.

Drop removes the table from the model and, if you're connected, from the database. For shipments, DbSchema ran:

DROP TABLE shop.shipments CASCADE;

CASCADE makes PostgreSQL drop the objects that depend on the table, such as a view that reads it or a foreign key from another table, so check them before you confirm.

To take a table off one diagram only, choose Exclude from Diagram, or press Delete. The table stays in the model, in the tree and in the database.

Names in view, trigger and procedure scripts

When DbSchema loads a view or a trigger from the database, it replaces the object's own name in the script with a placeholder, and so it does for a MySQL procedure or function. For a PostgreSQL view, the placeholder is ${view}:

DbSchema fills in the current name each time it creates the object, so the script keeps working after a rename. To rename a view, change its Name in the Edit View dialog and leave the script alone. Renaming monthly_totals, which had the comment "Order totals per month", to sales_by_month made DbSchema run:

CREATE OR REPLACE VIEW shop.sales_by_month AS SELECT date_trunc('month'::text, ordered_at) AS month,
    sum(total) AS total
   FROM shop.orders
  GROUP BY (date_trunc('month'::text, ordered_at));
COMMENT ON VIEW shop.sales_by_month IS 'Order totals per month';
DROP VIEW shop.monthly_totals;

The placeholders that a view script can hold:

Placeholder	Becomes
${nameWithSchemaName}, ${view}	The schema and the name, such as shop.monthly_totals
${name}, ${viewName}	The name alone
${schemaName}	The schema alone
${catalogName}	The catalog alone

Common problems

What do the icons next to the columns mean?

A key marks a primary key column, a fingerprint a column of a unique key or unique index, and a magnifying glass an indexed column. What the icons mean lists every icon.

Can I edit the name inside a view script?

Leave the placeholder in the script and rename the view in its Name field instead, as Names in view, trigger and procedure scripts shows.

Next steps

• Link tables with foreign keys
• Arrange tables on diagrams
• Compare the model with the database
• Generate HTML5 documentation