On Mon, Jan 12, 2009 at 6:15 PM, Oleg Broytmann <phd@phd.pp.ru> 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 <davide.alberani@gmail.com>  [PGP KeyID: 0x465BFD47]
http://erlug.linux.it/~da/