adding a foreign key constraint to an existing table

[Manlio Perillo]

Ok, I'm recidive ;-).

I want to add a foreign key constraint to "bind" to tables that came
from two separate packages.

I have done:
calendar.components.append_constraint(
sq.ForeignKeyConstraint(
[calendar.components.c.organizer],
[account.accounts.c.username],
ondelete='CASCADE', onupdate='CASCADE'
)
)

but I obtain:
Traceback (most recent call last):
File "bin/initdb.py", line 236, in ?
ondelete='CASCADE', onupdate='CASCADE' # XXX
File "/usr/lib/python2.4/site-packages/sqlalchemy/schema.py", line
258, in append_constraint
constraint._set_parent(self)
File "/usr/lib/python2.4/site-packages/sqlalchemy/schema.py", line
729, in _set_parent
self.append_element(c,r)
File "/usr/lib/python2.4/site-packages/sqlalchemy/schema.py", line
734, in append_element
fk._set_parent(self.table.c[col])
File "/usr/lib/python2.4/site-packages/sqlalchemy/util.py", line 106,
in __getitem__
return self._data[key]
File "/usr/lib/python2.4/site-packages/sqlalchemy/util.py", line 179,
in __getitem__
return dict.__getitem__(self, key)
KeyError: Column('organizer',String(length=50),nullable=False)

A simple solution is to add the constraint after tables creation, with
migrate.changeset.
However when I try to delete the tables I obtain an error since
SQLAlchemy is not aware of the foreign key constraint (as far as I can
understand).

By the way: is it possible to tell the drop method (both for metadata
and table) to use DROP CASCADE?

Thanks and regards Manlio Perillo

[Michael Bayer]

1. again, the "constraints" collection wasnt really designed to be
mutable, as other initialization steps are performed when you first
specify ForeignKeyConstraint (or ForeignKey) to Table. if you really
need to use a builder pattern here, make your own collection of columns
and constraints, build that up, then define a function that creates a
Table from that collection once its finished.

2. no support for "CASCADE" in the "DROP" statement right now. someone
fill me in, is "DROP CASCADE" part of the sql standard ?

[Jonathan Ellis]

On 1/15/07, Michael Bayer <zzz...@gmail.com> wrote:
> 2. no support for "CASCADE" in the "DROP" statement right now. someone
> fill me in, is "DROP CASCADE" part of the sql standard ?

yes, at least for tables.