Data Editor - Linking Records [Foreign Key]

Relational databases link two tables using a “link by value”, when child table has a field that stores ID value of the parent table.

To establish a link between records it is enough to set the value of such FK-field into one of existed ID value. To break a link it is enough to set the value into NULL.

Usually a GUI Manager just allows you to assign that value. Sometimes it offers yet look-up list, which display all existed ID fields of the first table. Not a huge help actually, because the first table can have million unique ID values.

In Valentina Studio, Related Table Editor introduce a new way to link/unlink records, using mouse clicks only.

Let you have two tables. Table “tblPerson_FK” is linked to table “tblPhone_FK” as 1:M with the help of FK-link “linkPhonePerson_FK”. This link is based on “tblPerson_FK.fldID” and “tblPhone_FK.fldPersonPtr” fields.

Note, that Valentina Studio shows you a lots of info and prompts about link and its character:

• in the menu you can see the name of link and its cardinality.
• “fldID” field in the top Table “tblPerson_FK” has icon of KEY.
• “fldPersonPtr” filed in the bottom Table “tblPhone_FK” has icon of PTR.
• both these columns are highlighted by yellow color.

You can link records in the related Table in two ways:

• by mouse click.
• by value edit.

To link records by mouse clicks:

• Mark a record in the top Table.
• Click in the first mark-column of the bottom Table on a record that should be linked.

In result, the Foreign Key field “fldPersonPtr” was automatically assigned by the value of the Primary Key field “fldID” of the marked record in the table Person.

In such way you can link any amount of records in the bottom table with records of the top table.

You can set the value of Foreign Key field manually to link records in tables. For this double click its cell and type some ID value.

Note, that if you enter not valid ID value, then a DB engine will return error.

You can unlink records in two ways:

• by mouse click.
• by value edit.

To unlinking record(s) by mouse click:

• mark a parent-record in the top Table.
• unmark the needed child-record (one click in the first column of the record) in the bottom Table.

You can see that the value of the Foreign Key field becomes NULL.

You can set the value of the Foreign Key field manually to NULL value, what will means unlink it from the parent record.

To set value to NULL you can

• click button NULL at bottom of Data Editor
• choose command “Set NULL” in the main menu Record
• use the keyboard shortcut CMD + L for mac or CTRL + L for other OS.