DataJoint: IntegrityError while dropping table

Question

I am in the process of designing our database and have a table called Session in the common_exp schema with the following definition:

@schema
class Session(dj.Manual):
    definition = """ # Information about the session and experimental setup
    -> common_mice.Mouse
    day             : date           # Date of the experimental session (YYYY-MM-DD)
    trial           : tinyint        # Counter of experimental sessions on the same day (base 1)
    ---
    id              : varchar(128)   # Unique identifier
    path            : varchar(256)   # Relative path of this session on the server
    counter         : smallint       # Overall counter of all sessions across mice (base 0)
    experimenter    : varchar(128)   # Who actually performed the experiment, must be a username from Investigator
    -> Anesthesia
    -> Setup
    -> Task
    notes           : varchar(2048)  # description of important things that happened
    """

I want to change the names of some attributes and thus want to drop the table. However, I am being greeted by this error:

common_exp.Session().drop()
`common_exp`.`session` (0 tuples)
Proceed? [yes, No]: >? yes

Traceback (most recent call last):
  File "C:\Anaconda3\envs\datajoint_wahl\lib\site-packages\IPython\core\interactiveshell.py", line 3441, in run_code
    exec(code_obj, self.user_global_ns, self.user_ns)
  File "<ipython-input-4-bce682713228>", line 1, in <module>
    common_exp.Session().drop()
  File "C:\Anaconda3\envs\datajoint_wahl\lib\site-packages\datajoint\table.py", line 474, in drop
    FreeTable(self.connection, table).drop_quick()
  File "C:\Anaconda3\envs\datajoint_wahl\lib\site-packages\datajoint\table.py", line 450, in drop_quick
    self.connection.query(query)
  File "C:\Anaconda3\envs\datajoint_wahl\lib\site-packages\datajoint\connection.py", line 302, in query
    self._execute_query(cursor, query, args, suppress_warnings)
  File "C:\Anaconda3\envs\datajoint_wahl\lib\site-packages\datajoint\connection.py", line 268, in _execute_query
    raise translate_query_error(err, query)
  File "C:\Anaconda3\envs\datajoint_wahl\lib\site-packages\datajoint\connection.py", line 266, in _execute_query
    cursor.execute(query, args)
  File "C:\Anaconda3\envs\datajoint_wahl\lib\site-packages\pymysql\cursors.py", line 148, in execute
    result = self._query(query)
  File "C:\Anaconda3\envs\datajoint_wahl\lib\site-packages\pymysql\cursors.py", line 310, in _query
    conn.query(q)
  File "C:\Anaconda3\envs\datajoint_wahl\lib\site-packages\pymysql\connections.py", line 548, in query
    self._affected_rows = self._read_query_result(unbuffered=unbuffered)
  File "C:\Anaconda3\envs\datajoint_wahl\lib\site-packages\pymysql\connections.py", line 775, in _read_query_result
    result.read()
  File "C:\Anaconda3\envs\datajoint_wahl\lib\site-packages\pymysql\connections.py", line 1156, in read
    first_packet = self.connection._read_packet()
  File "C:\Anaconda3\envs\datajoint_wahl\lib\site-packages\pymysql\connections.py", line 725, in _read_packet
    packet.raise_for_error()
  File "C:\Anaconda3\envs\datajoint_wahl\lib\site-packages\pymysql\protocol.py", line 221, in raise_for_error
    err.raise_mysql_exception(self._data)
  File "C:\Anaconda3\envs\datajoint_wahl\lib\site-packages\pymysql\err.py", line 143, in raise_mysql_exception
    raise errorclass(errno, errval)
pymysql.err.IntegrityError: (1217, 'Cannot delete or update a parent row: a foreign key constraint fails')

As you can see, the table is empty, and has no further dependencies. The error message also does not tell me which keys create the problem, or which other table, so I am a bit confused where the problem might be.

I am accessing the database with the root account, so privileges should not be an issue. Dropping tables from other schemas works, just this schema creates this error.

score 3 · Accepted Answer · answered Jun 22 '21 at 16:08

Thanks for the question -

Based on the fact there are no records in the table but that the drop fails with this error, it does seem like there are downstream tables defined with a foreign key back to the session table, so dropping the session table would leave these tables 'orphaned' and thus create an integrity error. It's not clear from the example that this is not the case.

The following SQL queries check forward/reverse dependencies at a schema level (answers the question: "which schema depends on which other schemas"):

    SELECT distinct(UNIQUE_CONSTRAINT_SCHEMA)
    FROM information_schema.REFERENTIAL_CONSTRAINTS
    where constraint_schema='my_schema'; -- forward dependencies for 'my_schema'

    SELECT distinct(CONSTRAINT_SCHEMA)
    FROM information_schema.REFERENTIAL_CONSTRAINTS
    where unique_constraint_schema='my_schema'; -- reverse dependencies for 'my_schema'

I unfortunately don't recall the precise columns, but the table-level information is also available in the information schema.

Thank you for your help, Chris and @DimitriYatsenko! I found indeed an old schema that I created at the very beginning, but had since deleted the .py file. The schema was still ghosting around in the database however, but since the .py file was not there anymore, DJ could not drop it. I could drop it manually directly through the SQL query `DROP SCHEMA old_schema;`, and now I can drop the `Session` table again. — Hendrik Heiser, Jun 22 '21 at 19:57
You do not need to have the original python module to work with a schema in the database. You can use `dj.list_schemas()` to list all the schemas. You can then `dj.create_virtual_module` to create a "virtual" module containing all the table classes. — Dimitri Yatsenko, Jun 30 '21 at 14:52

score 1 · Answer 2 · answered Jun 22 '21 at 15:56

This error indicates that there is a dependent table downstream from common_exp.Session that needs to be dropped before Session can be dropped.

Typically, DataJoint will cascade and drop all the downstream tables. However, sometimes DataJoint does not see the dependent tables because they are in a different schema that has not been loaded. You will need to load the schema that makes this reference in order to allow DataJoint to cascade the drop. You can use dj.list_schemas() to see all the available schemas.

DataJoint: IntegrityError while dropping table

2 Answers2