table.foreign_key

Andromeda deals with foreign keys differently than most frameworks. When you place a foreign key into an Andromeda table, several things happen:

• Andromeda identifies the column(s) that make the primary key of the parent table.
• Andromeda places these same columns into the child table
• Andromeda puts referential integrity constraints onto both tables (explained below)

When we use the SQL CREATE TABLE command to establish a foreign key, we must specify the column type, precision and scale, and then declare the reference to the parent. In Andromeda all you have to do is name the parent table, the rest is automatic.

table table_id:
   #...various table property assignments go here
   foreign_key table_id:
      #...any optional foreign key properties

The default behavior for a foreign key is as follows:

• Any value in a child table must have a corresponding value in the parent table (modifiable with allow_empty, allow_orphans and auto_insert).
• If a parent row has matching rows in the child table, the parent row cannot be deleted (modifiable with delete_cascade).

Properties

foreign_key table_id. The first line of a foreign key definition begins with the keyword 'foreign_key', a space, and then the name of a parent table (table_id). If you are using suffix and prefix, then the table_id should be written out including the suffix and prefix, as in this example:

!> !>noformat table orders: foreign_key customers: foreign_key customers_referring: suffix: _referring !< !<

suffix. If specified, all of the columns taken from the parent table will have the suffix appended to their name.

prefix. If specified, all of the columns taken from the parent table will have the prefix prepended to their name.

primary_key. A Y/N flag that will cause all of the foreign key columns to be in the primary key.

uisearch. A Y/N flag that causes all of the foreign_key columns to be in the normal search results.

auto_insert. A very powerful Y/N flag. See Automations.

copysamecols. Related to auto_insert, see Automations.

nocolumns. A Y/N flag that stops the normal behavior of adding the columns to the table. Required if more than one foreign key definitions would end up placing the same column in the table.

allow_empty. A Y/N flag that allows the foreign key value to be empty (or null).

allow_orphans. A Y/N flag that completely disables reference checking, but still defines the columns.

delete_cascade. A Y/N flag that causes all child table entries to be deleted when a parent table row is deleted. Overrides the normal behavior which is to prevent the deletion of a parent table row if there exists matching child entries.