Re: [SQLObject] How to add a foreign key at runtime

[Davide A. <dav...@gm...>]

On Mon, Jan 12, 2009 at 6:15 PM, Oleg Broytmann <ph...@ph...> wrote:

> > In short, I need something like that:
> > "ALTER TABLE foo ADD FOREIGN KEY (dest_id) REFERENCES dest_type"
> >
> > Is this possible with SQLObject?
>
>   Foo.sqlmeta.addColumn(
>      ForeignKey("DestType", name='dest', default=None),
>   changeSchema=True)
>

Hi,
this is not exactly what we were trying to do, since we already
have a column that must be "made" foreign key.

A simple example:
=============================================================
from sqlobject import *

URI = 'mysql://USER:PWD@localhost/DB'

class InfoType(SQLObject):
    kind = UnicodeCol(notNone=True)

class MyInfo(SQLObject):
    # For a series of reasons we don't want to specify 'kind' as ForeignKey,
    # at creation time.
    kindID = IntCol(notNone=True)
    info = UnicodeCol(notNone=True)

# Connect.
conn = connectionForURI(URI, debug=True)
InfoType.setConnection(conn)
MyInfo.setConnection(conn)

# Drop/create tables.
InfoType.dropTable(ifExists=True)
MyInfo.dropTable(ifExists=True)
InfoType.createTable(ifNotExists=True)
MyInfo.createTable(ifNotExists=True)

# The foreign key, pointing to InfoType.id.
fk = ForeignKey('InfoType', name='kind', default=None)
MyInfo.sqlmeta.addColumn(fk, changeSchema=True)
=============================================================

The above code raises an AssertionError:
  AssertionError: The class __main__.MyInfo already has a column 'kindID'
(<IntCol 895364c kindID>), you cannot add the column <ForeignKey 89b588c
kind>

What we need is a way to execute a SQL statement like (for MySQL):
  ALTER TABLE my_info ADD FOREIGN KEY (kind_id) REFERENCES info_type;

Notice that the my_info table already exists and is full of data, so
we can't drop/recreate it.

Is there any way to do this, using SQLObject?


Thanks!
-- 
Davide Alberani <dav...@gm...>  [PGP KeyID: 0x465BFD47]
http://erlug.linux.it/~da/