#157 Wrong exception type generated on foreign key violation

MySQLdb (285)

Using Python 2.3 and (debian linux) mysqldb 1.2.1c2-1:

Generated a table with a foreign key constraint, and intentionally
violated the constraint during testing. Output was:

Exception in thread Thread-1:Traceback (most recent call last):
[useless stuff removed]
File "/usr/lib/python2.3/site-packages/MySQLdb/cursors.py", line
137, in execute
self.errorhandler(self, exc, value)
File "/usr/lib/python2.3/site-packages/MySQLdb/
connections.py", line 33, in defaulterrorhandler
raise errorclass, errorvalue
OperationalError: (1216, 'Cannot add or update a child row: a
foreign key constraint fails')

According to the Python DB API 2.0 (http://www.python.org/peps/
pep-0249.html), a foreign key constraint violation should throw an
IntegrityError, not an OperationalError. (relevant paragraphs
quoted below for convenience)


        Exception raised for errors that are related to the
        database's operation and not necessarily under the control
        of the programmer, e.g. an unexpected disconnect

the data source name is not found, a transaction could
be processed, a memory allocation error occurred during
processing, etc. It must be a subclass of DatabaseError.


        Exception raised when the relational integrity of the
        database is affected, e.g. a foreign key check fails.  It
        must be a subclass of DatabaseError.

As it is, I can't tell in my code (without examining the text string
returned in the exception) whether a commit failed because of
bogus data, or because of a database disconnect, which makes it
hard to decide how to proceed... :(

(submitter: John Martinez - 'jvm at snarkhunter dot com')

Thanks for your time.


  • Andy Dustman

    Andy Dustman - 2005-10-16

    Logged In: YES

    OK. I think this is a relatively new error. I'll update this
    in the next release. OperationalError is used for anything I
    didn't specifically catch for something else, i.e. there are
    a couple specific codes it looks for for IntegrityError, and
    this one should be added.

  • Andy Dustman

    Andy Dustman - 2006-02-27

    Logged In: YES

    Should appear in 1.2.1c4, plus some others.

  • Elvis Pfutzenreuter

    Logged In: YES

    BTW the additional error codes that should raise
    IntegrityError instead of OperationalError are:

    ER_ROW_IS_REFERENCED_2 <- I discovered the problem via this
    one (error 1451)

  • Andy Dustman

    Andy Dustman - 2006-07-19

    Logged In: YES

    OK, the _2 errors did not make it in 1.2.1 as I was unaware
    of them. I'll put them in _p3.

  • Andy Dustman

    Andy Dustman - 2006-08-26

    Logged In: YES

    Fixed in development version; please test 1.2.2b1 when it is
    released (mysql-python-test).


Log in to post a comment.

Get latest updates about Open Source Projects, Conferences and News.

Sign up for the SourceForge newsletter:

JavaScript is required for this form.

No, thanks