Foreign Keys

A foreign key is a column that points to a row in another table. For example, orders.customer_id holds the customer_id of the customer who placed the order. DbSchema draws each foreign key as a line between the two tables.

The tables countries, addresses, customers, payments, orders and order_items, with foreign key lines between them

The table that holds the foreign key is the child, such as orders. The table it points to is the parent, such as customers.

Create a foreign key

The fastest way is to drag the child column onto the parent column.

Drag the child column, such as order_id in shipments, onto the parent column, such as order_id in orders.
The New Foreign Key dialog opens, filled in. Check the name and the columns.
Press OK, or Enter.

The new line joins the two tables. If you are connected, DbSchema creates the foreign key in the database at once. If you work offline, the foreign key stays in the model until you synchronize.

The parent column must be a primary key or have a unique key. Otherwise the database refuses the foreign key.

Edit a foreign key

Double-click the line to open the Edit Foreign Key dialog.

The Edit Foreign Key dialog of orders_customer_id_fkey, with the referring and referred column customer_id and On Delete set to cascade

Name: the name of the constraint in the database.
Virtual: keep the foreign key in the model only. See Add a virtual foreign key.
Table: the parent table.
Referring Column and Referred Column: each pair links a child column to a parent column. A foreign key over two or more columns has one pair per column. Use Add to add a pair.
On Delete and On Update: what the database does with the child rows when the parent row is deleted, or when its key changes.
Type: the cardinality that the line shows, such as Type 1:[1 or many]. See Read the cardinality of a line.

The Optimizations tab holds two options for a new foreign key. Ensure Primary Key Index adds a unique key on the parent columns when they have none. Optimize deletions by indexing the referring column(s) adds an index on the child columns.

On Delete and On Update

noAction: the database refuses to delete a parent row that still has child rows. Delete the child rows first.
restrict: also refuses the delete, like noAction.
cascade: deleting the parent row deletes its child rows too. With On Update, a changed key is copied to the child rows.
setNull: the child column becomes empty. The column must allow empty values.

The list offers only the actions that your database supports.

Read the cardinality of a line

Cardinality says how many rows on one side match a row on the other side, such as "one customer has many orders". DbSchema draws it with marks at the two ends of each line.

In a database, you do not set the cardinality directly. DbSchema reads it from the child columns:

Child columns	Unique	Line ends, parent to child
Mandatory ( Not Null )	No	1 to 1 or many
Mandatory ( Not Null )	Yes	1 to 1
Nullable	No	0 or 1 to 0 or many
Nullable	Yes	0 or 1 to 0 or 1

A column is unique when a primary key, a unique key or a unique index covers exactly the foreign key columns. A line is dashed when its child column is nullable, in every notation but Idef1x.

The picture uses the Information Engineering notation, which marks both ends of the line:

Three lines to orders in Information Engineering notation: a solid line from order_items with a bar and crow's foot, a dashed line from payments with a circle and crow's foot, and a solid line from shipments with a single bar at each end

order_items to orders: the child column is mandatory and not unique. A bar and a crow's foot at the child end mean one or many.
payments to orders: here the child column is nullable, so the line is dashed. A circle and a crow's foot mean zero or many. The circle and bar at the parent end mean that a payment may have no order.
shipments to orders: here the child column is mandatory and has a unique key. A single bar at each end means one to one.

The Type line of the foreign key dialog names the child end in words, such as Type 1:[1 or many] or Type 1:1.

Change the notation

DbSchema can draw the line ends in four notations. Choose one in View → Foreign Keys → Foreign Key Notation:

Information Engineering With Arrows: the default. The child end shows the marks above, and the parent end shows an arrow.
Information Engineering: marks at both ends, as in the picture above.
Barker: a crow's foot for many, and a dashed half of the line for optional.
Idef1x: a filled dot at the child end, with a letter for the cardinality.

The same three lines in Information Engineering With Arrows notation, each with an arrow at the orders end

The notation changes only the diagram. The model and the database stay the same.

Change the cardinality

To change the cardinality, change the child column. These are real changes to the table: if you are connected, they run in the database.

Zero instead of one: clear Mandatory ( Not Null ) in the column dialog. The column then accepts empty values.
One instead of many: add a unique key on the child column, in the Primary Key / Unique / Indexes tab of the table. See Create an index.

To show zero or many without changing the column, tick View → Foreign Keys → Foreign Key Notation → Use Strict Conventions. DbSchema then reads the child end from uniqueness alone. A mandatory column that is not unique then shows zero or many, and a unique one shows zero or one.

The three lines with Use Strict Conventions ticked: order_items now shows a circle and crow's foot, and shipments a circle and bar

To set the cardinality yourself, without columns, use logical design. There, the foreign key dialog has the choices Optional or Mandatory, and Zero or More, One or More, Zero or One or One.

Add a virtual foreign key

A virtual foreign key exists only in the DbSchema model. DbSchema never creates it in the database. Use one when the database has no foreign key between two tables, but the data still links them.

Drag the child column onto the parent column, as in Create a foreign key.
Tick Virtual in the dialog.
Press OK.

The New Foreign Key dialog for shipments_order_id_vfk with Virtual ticked

A virtual foreign key is drawn in another color:

A violet virtual foreign key line from shipments to orders, beside the gray lines of the real foreign keys

The Query Builder and the Data Explorer follow virtual foreign keys like real ones. Model → Find Virtual Foreign Keys suggests virtual foreign keys between columns with matching names.

When you are connected, you cannot make an existing foreign key of the database virtual: the Virtual box is disabled.

Common problems

The cardinality is stuck at 1:1

The child columns are unique: a primary key or a unique key covers exactly the foreign key columns. This happens when the foreign key column is also the whole primary key of the child table. To get one to many, the child needs a key of its own, and the unique key on the foreign key columns must go.

All my lines show 1:[1 or many], but I need 0..n

The child columns are Mandatory ( Not Null ), which DbSchema reads as "at least one". Tick Use Strict Conventions to show zero or many without changing the columns, as Change the cardinality shows.

How do I make the parent side optional?

In the Information Engineering notation, the parent end shows a circle when the child column is nullable. In the default notation, the parent end is always an arrow. To set it yourself, use the Optional and Mandatory choices of logical design.

Why is my line dashed?

The child column is nullable. Tick Mandatory ( Not Null ) in the column dialog to make it solid. A deduced foreign key, which View → Diagram Display → Deduced Foreign Keys shows, is always dashed.

Cookie preferences