Repository navigation
Allow renaming the referencing attribute in a foreign key #206
Description
From the start, DataJoint allowed foreign key constraints only between identically named attributes. This convention simplified the definitions and the relational algebra. Most data definition problems can be adequately addressed under this limitation.
However, some problems would become easier after renaming the referencing attribute. Therefore, I suggest the following definition syntax for defining foreign key constraints
[attribute [=default-value]] -> Table
If the referenced table schema.Table has exactly one primary key attribute that is not already in the current table, then the referencing attribute will be named attribute.
For example, imagine we have a table Vertex listing the vertices of graphs. Then the table Edge may reference Vertex twice as from and to.
class Vertex(dj.Manual):
definition = """ # vertex in a graph
-> Graph
vertex_id : int
---
value : float
"""
class Edge(dj.Manual):
definition = """ # directed edge connecting two vertices
-> Graph
from -> Vertex
to -> Vertex
---
weight : float # edge weight
"""The relational algebra remains unchanged. For example, to compute the mean of the two connected vertex values for each edge, the expression will be
src = Vertex().proj('node_id -> from', 'value -> v1')
dest = Vertex().proj('node_id -> to','value -> v2')
result = (src*Edge()*dst).proj('(v1+v2)/2 -> edge_value')Proper renaming of attributes before joins and restrictions will become necessary. Therefore, this approach will be used rarely when it results in significant improvements compared to the original DataJoint approach such as the example with graphs above.
Activity
dimitri-yatsenko commentedon May 14, 2016
We may also allow renaming multiple attributes if the referenced table has multiple new attributes:
The order of the new attributes must correspond to the order of the new attributes in the referenced table.
dimitri-yatsenko commentedon May 17, 2016
Specification
The foreign key definition can be performed in the following three ways:
1. Simple foreign key
Simple foreign keys have been the only way to declare dependencies between tables up until now (the first 6+ years of DataJoint) and are, in most cases, the only kind you ever need. We have found that you can produce every form of dependency using only simple foreign keys. Simple foreign keys simplify the relational algebra in long chains of dependencies in typical data processing pipelines.
The simple foreign key is declared by adding the following line to the table declaration:
This line has the following effect: All primary key attributes from
ReferencedTableare declared in the current table unless they have already been declared. The names of the attributes are kept unchanged. The foreign key constraint is created between the two tables.2. One-alias foreign keys
A one-alias foreign key has the form
This form works when
ReferencedTable's primary key has only one primary key attribute or only one new primary key attribute (one that is not yet in the current declaration). Thenaliased_attributewill be added to the declaration and cannot be already declared.The Graph database example above uses the one-alias form.
3. Mapped foreign keys
A mapped foreign key allows creating any foreign key relationship that SQL can as long as the dependency graph is acyclic. There is never a good reason for using it (please correct me) but DataJoint provides it for completeness and to enable using non-DataJoint schemas.
A mapped foreign key is declared as
In this form,
pk1, ..., pknis a subset of the primary key attributes ofReferencedTable. This declaration adds the new attributesalias1,...,aliasnto the new table definition matching the attribute definitions ofpk1,...,pkn. Then all the other new primary key attributes ofReferencedTableare also added to the declaration.The ordering of aliased attributes matters: attributes will be added in the order they are listed in the foreign key declaration. After all the implicitly added attributes.
Thus
can be abbreviated as
but the latter declaration will add the attributes in order (
pk2,alias1,alias3) rather than (alias1,pk2,alias3)Mapped foreign keys allow referencing to itself as a dependent attribute. For example, an
Employeetable might have a reference to the manager who is also an employee:Note that although the
Employeeprimary key is (organization_id,employee_id), it is sufficient to reference theemployee_idif the manager is within the organization. If the manager can be from another organization, then the foreign key needs to beMapped foreign keys are generally not datajoiuous approaches that have much more elegant DataJoint-like solutions. The only reason to use mapped foreign keys is to adopt an existing non-DataJoint schema.
dimitri-yatsenko commentedon Jun 13, 2016
Fixed in PR #208.
dimitri-yatsenko commentedon Oct 20, 2017