Foreign key

We can define the foreign key as needed. Similar to the association between database tables, association is created between two DQL tables if a DQL table’s foreign key field points to the primary key of another DQL table.

Foreign key name: The foreign key name can be directly edited, and should be unique within the current table otherwise errors will happen during the foreign key reference. When a foreign key consists of only one field, usually an alias will not be specified for it but instead the field name is used directly. If a foreign key consists of multiple fields, we must use the Foreign key name when referencing a field of table referenced by the foreign key. In the above screenshot, fk1, fk2 and fk3 are all foreign key names and fk2 is selected.

Referenced table name: Name of the table pointed by the foreign key. When the referenced table has a unique primary key and the key does not act as the foreign key, the table is called dimension table (simply called dimension). In the above screenshot, the dimension table is Product.

Referenced field: The primary key of the table pointed by the foreign key. In the above screenshot, the referenced field is ProductID, which is Product table’s primary key.

Foreign key filed: Field values of the current table correspond to those of referenced field one by one. In the above screenshot, Inventory table’s foreign key field is ProdcutID.

In DQL, a foreign key field and a dimension field have equal positions. We can regard a foreign key field as a dimension field.

Any table’s primary key can be regarded as a dimension field. When a table’s primary key is also the foreign key, the primary key’s dimension is the dimension pointed by the foreign key. When a primary key isn’t a foreign key, its dimension is itself. For a non-primary key field, it becomes a dimension field only when it is used as the foreign key and the field’s dimension is the dimension pointed by the foreign key.

Example:

According to the above figure, ReturnedPmt table’s CustomerID field points to Customer table’s CustID field. The relationship between ReturnedPmt and Customer is many to one. Related conceptions are as follows:

Foreign key field: ReturnedPmt table’s CustomerID field.

Dimension table: Customer table

Referenced field: Customer table’s primary key, the CustID field

DQL allows referencing a field in the foreign key table as referencing an attribute. In SELECT CustomerID.CustName,Date,Amount FROM ReturnedPmt for instance, CustomerID.CustName references CustName field in Customer table pointed by CustomerID.

Tips:

1) In DQL, we can directly use a single-field foreign key through the syntax of foreign key field.foreign key table’s field name.

2) In DQL, we can directly use a composite foreign key through the syntax of foreign key name.foreign key table’s field name.

3) The reference syntax of foreign key name.foreign key table’s field name applies to both composite foreign keys and single-field foreign keys.