[TIP] Foreign Key Constraint and model testing

[Carl Meyer]

Hi Sylvain,

On 09/16/2014 06:33 AM, Sylvain Le Beux wrote:
> This is my first post and even though I have been lurking for a while
> into test driven development, it's been quite new that I dived into it.
> The problem that caused me a great deal recently is the fact that I am
> doing some functional tests against a mysql database, build thanks to an
> sqlalchemy model.
> There are of course numbers of foreign key and my I discovered that the
> model objects are being created only if there are no foreign key
> constraints on the database.
> To make it more clear, I tested to run the tests with a setup where all
> foreign constraints are present (it is actually a dump of the database)
> and in another setup, the foreign key constraints are deleted, all other
> things being equal.
> In the first configuration, the test fails and for the latter the test
> is a success.

It's hard to say why that might be without seeing test code. Generally,
you'll need to set up test data in a way that conforms to the
constraints of your database schema. If there's a (NOT NULL) ForeignKey
from table A to table B, you'll need to create a row in table B before
you can create a row in table A.

> Thus my interrogations :
> - Is this common to delete the foreign key constraints while testing
> against a database model ?

Not in my experience, no. I would consider this a problematic divergence
between the system under test and the production system.

> - If the test fails while the foreign key constraints are present, does
> this means the constraints are badly implemented ?

Possibly, but not necessarily; it more likely indicates a problem with
how the test is implemented.

> - How do you tests the foreign key constraints themselves ?

If a database INSERT or UPDATE violates a schema constraint, I would
expect the database adapter to raise an error of some kind (which is why
your description of your situation, which sounds like a silent failure,
seems odd to me). Most test frameworks provide a way to assert that a
certain operation raises a certain exception; in unittest it's
assertRaises().

> If you have any documentation or open source projects examples that deal
> with these issues, I would be very glad you'd point them to me ?

Tests for any application with a database will probably deal with these
issues in some way. It's hard to know where to point you without seeing
some code, error output, or something to indicate what specific problem
you are having.

Carl