I am doing the same kind of thing but using programming logic to map the two databases. I don't think it is possible to have a foreign key in one table point to another table.
On Tue, Apr 24, 2012 at 12:37 AM, Vincenzo Ampolo <[email protected] > wrote: > Hi, > > I'm trying to build a relation between two database of the same > postgresql instance. > > I have these two files: > > a_database.py > > db.define_table('title', > Field('title'), > Field('year'), > migrate = False, > fake_migrate = True > ) > > main.py > > import a_database > > db.define_table('users', > Field('name'), > Field('email') > ) > > db.define_table('ratings', > Field('user', db.users), > Field('item', a_database.db.title), > Field('rating', 'double') > ) > > Note that db in a_database.py is different form the db in main.py by the > connection string. > > The error i see is: > > Traceback (most recent call last): > File "imdb_utils.py", line 23, in <module> > Field('rating', 'double') > File "/usr/lib/pymodules/python2.7/gluon/dal.py", line 4235, in > define_table > polymodel=polymodel) > File "/usr/lib/pymodules/python2.7/gluon/dal.py", line 636, in > create_table > self.create_sequence_and_triggers(query,table) > File "/usr/lib/pymodules/python2.7/gluon/dal.py", line 1701, in > create_sequence_and_triggers > self.execute(query) > File "/usr/lib/pymodules/python2.7/gluon/dal.py", line 1251, in execute > return self.log_execute(*a, **b) > File "/usr/lib/pymodules/python2.7/gluon/dal.py", line 1246, in > log_execute > ret = self.cursor.execute(*a,**b) > psycopg2.ProgrammingError: syntax error at or near "user" > LINE 3: user INTEGER REFERENCES users(id) ON DELETE CASCADE, > ^ > Which is quite strange, can't i use the 'user' column name in a database? > > If i go on and change column name 'user' to 'euser', drop the db and > create it again, (the one in main.py) and delete all the *.table files i > end up with: > > Traceback (most recent call last): > File "imdb_utils.py", line 23, in <module> > Field('rating', 'double') > File "/usr/lib/pymodules/python2.7/gluon/dal.py", line 4235, in > define_table > polymodel=polymodel) > File "/usr/lib/pymodules/python2.7/gluon/dal.py", line 636, in > create_table > self.create_sequence_and_triggers(query,table) > File "/usr/lib/pymodules/python2.7/gluon/dal.py", line 1701, in > create_sequence_and_triggers > self.execute(query) > File "/usr/lib/pymodules/python2.7/gluon/dal.py", line 1251, in execute > return self.log_execute(*a, **b) > File "/usr/lib/pymodules/python2.7/gluon/dal.py", line 1246, in > log_execute > ret = self.cursor.execute(*a,**b) > psycopg2.ProgrammingError: relation "title" does not exist > > Which makes me suggest that what i'm trying to do is unfeasible. Am i > right? > > Thanks. > > -- > Vincenzo Ampolo > http://vincenzo-ampolo.net > http://goshawknest.wordpress.com > -- -- Regards, Bruce Wade http://ca.linkedin.com/in/brucelwade http://www.wadecybertech.com http://www.fittraineronline.com - Fitness Personal Trainers Online http://www.warplydesigned.com
