OK! Let's dance
This doesn't work (with SQLite at least):
db.define_table('a_tbl', Field('id', 'integer'), Field('name'),
primarykey=['id'] ) # one keyed table
db.define_table('b_tbl', Field('id', 'reference a_tbl.id'), Field('name'),
primarykey=['id']) # another keyed table
<class 'sqlite3.OperationalError'> near "name": syntax error
sql.log:
*CREATE TABLE b_tbl(*
* id INTEGER, CONSTRAINT "FK_b_tbl_id__constraint" FOREIGN KEY (id)
REFERENCES a_tbl (id) ON DELETE CASCADE,** name CHAR(512),** PRIMARY
KEY(id)) ;*
This doesn't work too:
db.define_table('a_tbl', Field('id', 'integer'), Field('name'),
primarykey=['id'] ) # one keyed table
db.define_table('b_tbl', Field('id', db.a_tbl), Field('name'),
primarykey=['id']) # another keyed table
it produces the same sql.log and causes the same error - expected
But this works!:
db.define_table('a_tbl', Field('id', 'id'), Field('name') ) # not keyed
table
db.define_table('b_tbl', Field('id', 'reference a_tbl'), Field('name'),
primarykey=['id']) # keyed table that references to not keyed table
it produces another sql.log:
CREATE TABLE b_tbl(
id INTEGER REFERENCES a_tbl (id) ON DELETE CASCADE NOT NULL ,
name CHAR(512),
PRIMARY KEY(id)) ;
On Saturday, March 12, 2016 at 8:54:37 PM UTC+3, Anthony wrote:
>
> Not sure if it's a bug, but the book does say:
>
> - Keyed tables can only reference other keyed tables.
> - Referencing fields must use the reference tablename.fieldname format.
>
> Anthony
>
> On Saturday, March 12, 2016 at 4:52:53 AM UTC-5, Val K wrote:
>>
>> Hi!
>> I have a problem - look at example:
>>
>> db.define_table('a_tbl', Field('name'))
>> db.define_table('b_tbl', Field('id', 'reference a_tbl') ,Field('name'),
>> primarykey=['id'])
>> db.define_table('c_tbl', Field('b_tbl_id', 'reference b_tbl')
>> ,Field('name'))
>>
>>
>> *c_tbl* definition causes an error (see below) because there is
>> *b_tbl_id* field that references to *b_tbl* that has *id* reference to
>> *a_tbl*
>>
>> Is this a bug?
>>
>>
>>
>>
>> ----------------------------------------------------------------------------------------------------------
>>
>> <type 'exceptions.KeyError'> 'foreign_key'
>>
>> web2py™Version 2.13.4-stable+timestamp.2015.12.26.04.59.39PythonPython
>> 2.7.9:
>>
>>
>> *File D:\web2py_last\web2py\gluon\packages\dal\pydal\adapters\base.py in
>> create_table at line 303*
>> Function argument list
>>
>> (self=<pydal.adapters.sqlite.SQLiteAdapter object>, table=<Table c_tbl
>> (id,name)>, migrate=True, fake_migrate=False, polymodel=None)
>> Code listing
>>
>> 298.
>> 299.
>> 300.
>> 301.
>> 302.
>> 303.
>>
>> 304.
>> 305.
>> 306.
>> 307.
>>
>>
>> # must be PK reference or unique
>> if getattr(rtable, '_primarykey', None) and rfieldname in
>> rtable._primarykey or \
>> rfield.unique:
>> ftype = types[rfield.type[:9]] % \
>> dict(length=rfield.length)
>>
>> # multicolumn primary key reference?
>> if not rfield.unique and len(rtable._primarykey)>1:
>> # then it has to be a table level FK
>> if rtablename not in TFK:
>>
>>
>>
>>
>>
--
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
---
You received this message because you are subscribed to the Google Groups
"web2py-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to web2py+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
