Allow renaming the referencing attribute in a foreign key

[dimitri-yatsenko]

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.

[dimitri-yatsenko]

We may also allow renaming multiple attributes if the referenced table has multiple new attributes:

class Room(dj.Manual): 
    definition = """    # rooms in buildings
    building_name :char(8)  
    room_name :char(6)  
    """

class Animal(dj.Manual):
    definition = """ # lab animal
    animal_id :int    
    --- 
    housing_building="unknown", housing_room="unknown"  -> Room 
    experiment_building, experiment_room -> Room
    """

The order of the new attributes must correspond to the order of the new attributes in the referenced table.

[dimitri-yatsenko]

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:

-> ReferencedTable

This line has the following effect: All primary key attributes from ReferencedTable are 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

aliased_attribute -> ReferencedTable

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). Then aliased_attribute will 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

alias1, ..., aliasn -> ReferencedTable(pk1, ..., pkn)

In this form, pk1, ..., pkn is a subset of the primary key attributes of ReferencedTable. This declaration adds the new attributes alias1,...,aliasn to the new table definition matching the attribute definitions of pk1,...,pkn. Then all the other new primary key attributes of ReferencedTable are 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

alias1, pk2, alias3 -> ReferencedTable(pk1, pk2, pk3)

can be abbreviated as

alias1, alias3 -> ReferencedTable(pk1, pk3)

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 Employee table might have a reference to the manager who is also an employee:

-> Organization
employee_id
---
manager_id -> Employee(employee_id)

Note that although the Employee primary key is (organization_id, employee_id), it is sufficient to reference the employee_id if the manager is within the organization. If the manager can be from another organization, then the foreign key needs to be

manager_organization, manager_id -> Employee(organization_id, employee_id)

Mapped 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]

Fixed in PR #208.

[dimitri-yatsenko]

Yes, this feature is implemented but the syntax differed from what was in the issue. You can see the correct syntax here: https://docs.datajoint.io/data-definition/Foreign-keys.html#renamed-foreign-keys

…

On Fri, Oct 20, 2017 at 8:17 AM, Sophie ***@***.***> wrote: Is this feature actually implemented in the latest release of data joint? I get parsing errors for python when trying to define a table as such: @Schema class Neuron(dj.Computed): definition = """ -> SkeletonFile reduction -> ReductionMethod(id) --- soma_pos : blob # Soma position in microns soma_rad : float # Soma radius in microns """ — You are receiving this because you modified the open/close state. Reply to this email directly, view it on GitHub <#206 (comment)>, or mute the thread <https://github.com/notifications/unsubscribe-auth/ABC56Qp9dIlVD95PuKKq6buOx_erR4Mtks5suJ10gaJpZM4IejMu> .