The example above is straightforward so far. We have defined two
columns, "customer" and "order_id" as auto-incrementing int columns
(the auto: sequence makes them auto-increment). We have then
created two tables, both in the orders module, with the required
primary keys and with a bunch of columns set "uisearch: Y".

But now it is time to put a foreign key into the orders table that
links back to the customers. Andromeda has an extremely simple
way to do this:

The single last line of the example does everything that is
required for a foreign key. All you have to do is use the
foreign_key keyword and name the parent table. When Andromeda
builds your database, it looks to the definition of the CUSTOMERS
table, fetches the definitions of the primary key of that table,
and copies those columns into the ORDERS table.

This is a very powerful feature. The first benefit is that you
do not have to retype the column(s) that make up the primary
key of the CUSTOMERS table, so there is no chance of a typo or mistake
when doing so. But more importantly, if you modify the primary
key of the parent table, such as by adding more columns or
expanding one or more columns, these changes cascade down to all
child tables without any work on your part.

Multiple Foreign Keys and Self-Referencing Foreign Keys

Sometimes you need to have more than one foreign key from a child
to a parent. Sometimes you may also need a table to refer to
itself (a hierarchy). Both of these are done the same way, by
using the prefix and/or suffix keywords.

First we will consider the fact that these foreign keys are
self-referencing. The table has a foreign key to itself.
Normally Andromeda will copy the columns from a parent to a
child, but this would mean two columns of the same name.
So we modify the name of the parent table by adding "_mother"
to it, then we provide the "suffix: _mother" property so that
Andromeda can figure out that we mean the table "persons".

The resulting column will be named "persons_mother".

The suffix is also used to allow multiple foreign keys to the
same table. If we have multiple foreign keys from one table
to another, then Andromeda will try to make the same column
twice in the child table. We keep them separate by using
suffix "_mother" for one and "_father" for the other.

To sum up, the resulting table will have the column PERSON
which is the primary key, PERSON_MOTHER, a foreign key to itself,
and PERSON_FATHER, a second foreign key to itself.

The prefix property works the same way, we might have done
this instead:

Changing Column Properties

One problem we will have with the above example is that it will
have three columns that all have the description "Person ID".
We would rather those descriptions be "Person ID", "Mother ID"
and "Father ID".

We can do this by adding a definition of the relevant columns
and setting properties. The nice thing about foreign keys is that
we do not need to specify the columns in a foreign key, but we
can if we have something to gain by it.