Is it possible to alter a table to include reference to an upstream table in datajoint for python?

Question

We want to alter a table to include a non-primary key reference to a new table. The old definition is:

@schema
class SpikeSortingParameters(dj.Manual):
    definition = """
    # Table for holding parameters for each spike sorting run
    -> SortGroup
    -> SpikeSorterParameters
    -> SortInterval
    ---
    -> SpikeSortingMetrics
    -> IntervalList
    import_path = '': varchar(200) # optional path to previous curated sorting output
    """

We'd like to add

-> SpikeSortingArtifactParameters

as a non primary key, and before we spent time trying to get this to work, we wanted to know if it was possible given that we don't know of a way to assign a default value here.

thanks in advance...

Loren

score 1 · Answer 1 · answered Apr 05 '21 at 00:04

Unfortunately, foreign key updates are not currently supported with table.alter(). There is a workaround that can be used but there are a few steps and those should be taken carefully. It would be best to file this as a feature request in the issue tracker.

Using Alter

For instance, consider the following:

If you have 2 tables defined as follows:

import datajoint as dj

schema = dj.Schema('rguzman_alter_example')

@schema
class Student(dj.Lookup):
    definition = """
    student_first_name: varchar(30)
    student_last_name: varchar(30)
    ---
    student_location: varchar(30)
    """
    contents = [('Joe', 'Schmoe', 'Los Angeles, CA'), ('Suzie', 'Queue', 'Miami, FL')]

@schema
class Assignment (dj.Lookup):
    definition = """
    assignment_id: int
    ---
    assignment_due_date: date
    #-> [nullable] Student # Standard way to define a foreign key on secondary attributes with NULL as default
    """
    contents = [dict(assignment_id=100, assignment_due_date='2021-04-21')]

Now suppose that you'd like to have a foreign key on secondary attributes with NULL as the default. You can pass options to foreign keys in secondary attributes (see the comment above where we allow it to default to NULL). Initializing a table from scratch in this way works just fine. For the case where we want to add a foreign key on a secondary attribute after a table has been initialized with existing data, Assignment.alter() would be the best means to achieve this so long as we establish a default value to fill existing records with. Let's see what happens when we uncomment the foreign key on secondary attributes, redefine the Assignment table class, and try to alter.

---------------------------------------------------------------------------
NotImplementedError                       Traceback (most recent call last)
<ipython-input-23-09997168281c> in <module>
----> 1 Assignment.alter()

~/.local/lib/python3.7/site-packages/datajoint/table.py in alter(self, prompt, context)
     84             del frame
     85         old_definition = self.describe(context=context, printout=False)
---> 86         sql, external_stores = alter(self.definition, old_definition, context)
     87         if not sql:
     88             if prompt:

~/.local/lib/python3.7/site-packages/datajoint/declare.py in alter(definition, old_definition, context)
    367         raise NotImplementedError('table.alter cannot alter the primary key (yet).')
    368     if foreign_key_sql != foreign_key_sql_:
--> 369         raise NotImplementedError('table.alter cannot alter foreign keys (yet).')
    370     if index_sql != index_sql_:
    371         raise NotImplementedError('table.alter cannot alter indexes (yet)')

NotImplementedError: table.alter cannot alter foreign keys (yet).

Oh, there's an exception... So it turns out that it is not implemented yet for this use case. However, there is a manual workaround that can be leveraged so let me detail those steps until there is support.

Workaround

Instead of defining the foreign key on secondary attributes as above, define it in this way:

@schema
class Assignment (dj.Lookup):
    definition = f"""
    assignment_id: int
    ---
    assignment_due_date: date
    {newline.join(['' + a.name + '=null: ' + a.type for a in Student.heading.attributes.values() if a.in_key])}
    """
    contents = [dict(assignment_id=100, assignment_due_date='2021-04-21')]

This 'copies' over the primary attributes of the parent table into the secondary attributes of your child table.

Perform alter as normal: Assignment.alter()

Manually add the foreign key using a SQL query directly. Like this:

q = """
    ALTER TABLE {table}
    ADD FOREIGN KEY ({fk})
    REFERENCES {ref} ({pk})
    ON UPDATE CASCADE
    ON DELETE RESTRICT
    """.format(table=Assignment.full_table_name,
               fk=','.join([f'`{k}`' for k in Student.primary_key]),
               ref=f'`{Student.table_name}`',
               pk=','.join([f'`{k}`' for k in Student.primary_key]))
dj.conn().query(q)

Make sure to remove the portion that was added in step 1 and replace it with the proper specification i.e. -> [nullable] Student
Restart your kernel
To verify it has been properly set, check Assignment.describe(). If everything worked properly, the result should be:
```
assignment_id        : int                          
---
assignment_due_date  : date                         
-> [nullable] Student
```
Additionally, any pre-existing records should now be prefilled with NULL.

Is it possible to alter a table to include reference to an upstream table in datajoint for python?

1 Answers1

Using Alter

Workaround