Definitively not possible. The DB does not allow it. You can make the reference field and integer and give it a IS_IN_DB validators pointing to the other table. It will work as you want to but bypass the DB restriction.
On Tuesday, 24 April 2012 08:17:21 UTC-5, Bruce Wade wrote: > > 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 > >
