web2py defining db tables and requires - alter table?

Question

I'm trying to use a legacy sqlite db in web2py. I cannot change the schema as the db is currently in use by a couple other apps.

My problem is that the db tables have requires that look like this: TableC requires TableB, TableB requires TableA, TableA requires Table C.

So, of course I get an error when the table required isn't defined yet. I thought I would try defining a portion of TableC first, then adding the other columns by altering the table later in the db.py. Would this work? If so, how do I alter the table in the db.py code?

Does anyone have other ideas that might work?

Answer 1

From wikipedia (http://en.wikipedia.org/wiki/Circular_reference) "Most relational databases such as Oracle and SQL Server do not allow circular referencing because there is always a problem when deleting a row from a table having dependencies to another row from another table (foreign key) which refers to the row being deleted."

Web2py does not allow circular references - at the database level - for the same reason (it does allow self references). Normally if you try a circular reference you need a many-to-many relation instead via a link table.

Anyway. There is a workaround. Make the last reference field and integer and enforce the reference at the web2py level (IS_IN_DB) instead than at the db level.