[web2py] Re: Foreign key error after adding new table instance

[Steve]

OK,

After searching the sqlite db and the views  problem solved.
DB inegrity was ok ... the view was wrong 




Op zondag 16 juni 2019 17:53:33 UTC+2 schreef Steve:
>
> Hi All,
>
> Busy with my platform, just added some tables.
> I have defined extra fields for the Auth table.
>
> In 1 of the aded fields i use a different table to get some info.
> 2 days ago i added 3 row's which are available for me to select. When i 
> add a new user i am able te select one of those rows and add the user ...NO 
> PROBLEM
>
> Today i added 1 extre row to the referenced field, and suddenly i get an 
> FOREIGN KEY error, and only when i select the newly added row. If i select 
> one of the other rows ... NO PROBLEM
>
> Anyone any idea why this can happen .
>
>
>
> db.define_table('caretype',
> Field('caretype', requires=IS_NOT_EMPTY(), 
> label=T('Caretype')),
> Field('description', requires=IS_NOT_EMPTY(), 
> label=T('Description')),
> auth.signature,format='%(description)s', migrate=False)
>
> added auth rows
>
> auth.settings.extra_fields['auth_user']= [
> Field('work_position',db.caretype, label=T('Profession')),
> Field('picture', 'upload', 
> default='static/images/user_icon.png'),]
>
> Steve
>

-- 
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.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/web2py/195ef526-dad9-40fc-b627-77d3d4ff4000%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Re: [web2py] Re: FOREIGN KEY constraint failed

[Santiago Cartasegna]

Glad that my idea helped!

2017-07-18 6:20 GMT-03:00 mostwanted :

> I was able o solve the problem, when i was creating the tables i copied
> and pasted a code for the '*governmetFormPages' *table but to correct the
> error i had to delete the table and type its code word for word, that
> solved my problem. thanks for the input guys. Santiago Cartasegna  was
> right.
>
> On Sunday, July 2, 2017 at 10:37:22 AM UTC+2, mostwanted wrote:
>>
>> Hi guys, i need help, i'm pulling my hairs out over here, i have a
>> website where when i'm trying to record an entry i get an error:
>>  FOREIGN KEY constraint failed
>>
>>
>> I have been able to create 2 forms but other attempts to create more
>> forms fail!!
>>
>>
>> These are my tables;
>>
>>
>> db.define_table('government',
>> Field('govName', requires=IS_NOT_EMPTY()),
>> Field('tel', requires=IS_NOT_EMPTY()),
>> Field('email', requires=IS_NOT_EMPTY()),
>> format='%(govName)s',  migrate=False,fake_migrate=True)
>>
>>
>> db.define_table('governmenttForm',
>> Field('formImage', 'upload'),
>> Field('filledformImage', 'upload'),
>> Field('formName',requires=IS_NOT_EMPTY()),
>> Field('governmentName', 'reference government'),
>> format='%(formName)s',migrate=False,fake_migrate=True)
>>
>> db.define_table('governmetFormPages',
>> Field('formImage', 'upload'),
>> Field('formTitle',requires=IS_NOT_EMPTY()),
>> Field('formName', 'reference
>> govmntForm'),migrate=False,fake_migrate=True)
>>
>>
>> I am able to create a form for the governmentForm table but when i try to
>> load up the pages for governmentFormPages table i get the error
>>  FOREIGN KEY constraint failed
>>
>>
>> Below is the stack
>> Error ticket for "formulator" Ticket ID
>>
>> 127.0.0.1.2017-07-02.10-06-22.18c363e3-13c2-47f7-ace4-3692239493c4
>>  FOREIGN KEY constraint failed Version
>> web2py™ Version 2.14.6-stable+timestamp.2016.05.10.00.21.47 Traceback
>>
>> 1.
>> 2.
>> 3.
>> 4.
>> 5.
>> 6.
>> 7.
>> 8.
>> 9.
>> 10.
>> 11.
>> 12.
>> 13.
>> 14.
>> 15.
>> 16.
>>
>> Traceback (most recent call last):
>>   File "/home/mostwanted/web2py/gluon/restricted.py", line 227, in restricted
>> exec ccode in environment
>>   File 
>> "/home/mostwanted/web2py/applications/formulator/controllers/appadmin.py" 
>> ,
>>  line 703, in 
>>   File "/home/mostwanted/web2py/gluon/globals.py", line 417, in 
>> self._caller = lambda f: f()
>>   File 
>> "/home/mostwanted/web2py/applications/formulator/controllers/appadmin.py" 
>> ,
>>  line 151, in insert
>> if form.accepts(request.vars, session):
>>   File "/home/mostwanted/web2py/gluon/sqlhtml.py", line 1746, in accepts
>> self.vars.id = self.table.insert(**fields)
>>   File "/home/mostwanted/web2py/gluon/packages/dal/pydal/objects.py", line 
>> 726, in insert
>> ret = self._db._adapter.insert(self, self._listify(fields))
>>   File "/home/mostwanted/web2py/gluon/packages/dal/pydal/adapters/base.py", 
>> line 746, in insert
>> raise e
>> IntegrityError: FOREIGN KEY constraint failed
>>
>>
>> Please help me, i dont know what to do, i have tried to look for
>> solutions online but i just dont understand them, may someone please give
>> me light on what i'm doing wrong.
>>
> --
> 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 a topic in the
> Google Groups "web2py-users" group.
> To unsubscribe from this topic, visit https://groups.google.com/d/
> topic/web2py/CPmr9UHWvOA/unsubscribe.
> To unsubscribe from this group and all its topics, send an email to
> web2py+unsubscr...@googlegroups.com.
> For more options, visit https://groups.google.com/d/optout.
>



-- 
Santiago Cartasegna.-

-- 
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.

[web2py] Re: FOREIGN KEY constraint failed

[mostwanted]

I was able o solve the problem, when i was creating the tables i copied and 
pasted a code for the '*governmetFormPages' *table but to correct the error 
i had to delete the table and type its code word for word, that solved my 
problem. thanks for the input guys. Santiago Cartasegna  was right.

On Sunday, July 2, 2017 at 10:37:22 AM UTC+2, mostwanted wrote:
>
> Hi guys, i need help, i'm pulling my hairs out over here, i have a website 
> where when i'm trying to record an entry i get an error:
>  FOREIGN KEY constraint failed
>
>
> I have been able to create 2 forms but other attempts to create more forms 
> fail!!
>
>
> These are my tables;
>
>
> db.define_table('government',
> Field('govName', requires=IS_NOT_EMPTY()),
> Field('tel', requires=IS_NOT_EMPTY()),
> Field('email', requires=IS_NOT_EMPTY()),
> format='%(govName)s',  migrate=False,fake_migrate=True)
>
>
> db.define_table('governmenttForm',
> Field('formImage', 'upload'),
> Field('filledformImage', 'upload'),
> Field('formName',requires=IS_NOT_EMPTY()),
> Field('governmentName', 'reference government'), 
> format='%(formName)s',migrate=False,fake_migrate=True)
>
> db.define_table('governmetFormPages',
> Field('formImage', 'upload'),
> Field('formTitle',requires=IS_NOT_EMPTY()), 
> Field('formName', 'reference 
> govmntForm'),migrate=False,fake_migrate=True)
>
>
> I am able to create a form for the governmentForm table but when i try to 
> load up the pages for governmentFormPages table i get the error
>  FOREIGN KEY constraint failed 
>
>
> Below is the stack
> Error ticket for "formulator" Ticket ID 
>
> 127.0.0.1.2017-07-02.10-06-22.18c363e3-13c2-47f7-ace4-3692239493c4
>  FOREIGN KEY constraint failed Version 
> web2py™ Version 2.14.6-stable+timestamp.2016.05.10.00.21.47 Traceback 
>
> 1.
> 2.
> 3.
> 4.
> 5.
> 6.
> 7.
> 8.
> 9.
> 10.
> 11.
> 12.
> 13.
> 14.
> 15.
> 16.
>
> Traceback (most recent call last):
>   File "/home/mostwanted/web2py/gluon/restricted.py", line 227, in restricted
> exec ccode in environment
>   File 
> "/home/mostwanted/web2py/applications/formulator/controllers/appadmin.py" 
> ,
>  line 703, in 
>   File "/home/mostwanted/web2py/gluon/globals.py", line 417, in 
> self._caller = lambda f: f()
>   File 
> "/home/mostwanted/web2py/applications/formulator/controllers/appadmin.py" 
> ,
>  line 151, in insert
> if form.accepts(request.vars, session):
>   File "/home/mostwanted/web2py/gluon/sqlhtml.py", line 1746, in accepts
> self.vars.id = self.table.insert(**fields)
>   File "/home/mostwanted/web2py/gluon/packages/dal/pydal/objects.py", line 
> 726, in insert
> ret = self._db._adapter.insert(self, self._listify(fields))
>   File "/home/mostwanted/web2py/gluon/packages/dal/pydal/adapters/base.py", 
> line 746, in insert
> raise e
> IntegrityError: FOREIGN KEY constraint failed
>
>
> Please help me, i dont know what to do, i have tried to look for solutions 
> online but i just dont understand them, may someone please give me light on 
> what i'm doing wrong.
>

-- 
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.

[web2py] Re: FOREIGN KEY constraint failed

[Dave S]

On Tuesday, July 11, 2017 at 2:39:46 PM UTC-7, Scheme Lab wrote:
>
> I think the filename ends with .sqlite doesnt it?
>

That's the actual database (if you're using sqlite3).  The dot-table files 
are part of pydal, with schema information used by the driver, etc, and are 
present for any database engine.

/dps
 

>
> On Wednesday, July 5, 2017 at 3:07:46 AM UTC-4, Dave S wrote:
>>
>>
>>
>> On Tuesday, July 4, 2017 at 11:34:11 PM UTC-7, mostwanted wrote:
>>>
>>> Hey Santi, how do i go about doing that, deleting all the tables in the 
>>> database backup folder?
>>>
>>
>> The easiest way is to use a command line, and delete the .table files. 
>>  In a linux system, and picking the application name from the ticket,
>>
>> cd [web2pydir]/applications/formulator/databases
>> rm *.table
>>
>> For Windows, the same except substitute del for rm.
>>
>> (the "backup" mentioned may be a nod to a hosting service, where you 
>> might not manipulate the file system directly..  For instance, for heroku, 
>> I think you'd actually do a git delete of any dot-table files, but I've 
>> only done node.js on heroku, so I'm just waving my hands.)
>>
>> /dps
>>
>>
>>
>>> On Tuesday, July 4, 2017 at 12:08:44 AM UTC+2, Santiago Cartasegna wrote:

 Go to the database folder backup and delete all the .tables. Perhaps a 
 change was not reflected on the creation of the table.

 A similar error ocurred to me and it was on the creation of the id's 
 references on the forange. But your code looks ok

 El lunes, 3 de julio de 2017, 15:50:45 (UTC-3), mostwanted escribió:
>
> this is the actual code Santi:
>
> db.define_table('government',
> Field('govName', requires=IS_NOT_EMPTY()),
> Field('tel', requires=IS_NOT_EMPTY()),
> Field('email', requires=IS_NOT_EMPTY()),
> format='%(govName)s',  migrate=False,fake_migrate=True)
>
>
> db.define_table(govmntForm',
>
> Field('formImage', 'upload'),
> Field('filledformImage', 'upload'),
> Field('formName',requires=IS_NOT_EMPTY()),
> Field('governmentName', 'reference government'), 
> format='%(formName)s',migrate=False,fake_migrate=True)
>
> db.define_table('governmetFormPages',
> Field('formImage', 'upload'),
> Field('formTitle',requires=IS_NOT_EMPTY()), 
> Field('formName', 'reference 
> govmntForm'),migrate=False,fake_migrate=True)
>
>
> On Monday, July 3, 2017 at 4:51:47 PM UTC+2, Santiago Cartasegna wrote:
>>
>> Hello,
>>
>> You should reference the table with the same name that is created so 
>> the table sould be like:
>>
>> db.define_table('governmetFormPages',
>> Field('formImage', 'upload'),
>> Field('formTitle',requires=IS_NOT_EMPTY()), 
>> Field('formName', 'reference governmenttForm
>> '),migrate=False,fake_migrate=True)
>>
>> Best regards
>>
>>
>> El lunes, 3 de julio de 2017, 4:15:18 (UTC-3), mostwanted escribió:
>>>
>>> It is the actually code Tony, i just changed it after posting it. 
>>> The table name was initially *govmntForm* as it is referenced in 
>>> the *governmetFormPages* table.
>>>
>>> On Sunday, July 2, 2017 at 7:48:43 PM UTC+2, Anthony wrote:

 Not sure if you're showing your actual code, but the table name is 
 spelled "governmenttForm" (note the extra "t"), and for the field 
 type, you 
 have "reference govmntForm".

 Anthony

 On Sunday, July 2, 2017 at 4:37:22 AM UTC-4, mostwanted wrote:
>
> Hi guys, i need help, i'm pulling my hairs out over here, i have a 
> website where when i'm trying to record an entry i get an error:
>  FOREIGN KEY constraint failed
>
>
> I have been able to create 2 forms but other attempts to create 
> more forms fail!!
>
>
> These are my tables;
>
>
> db.define_table('government',
> Field('govName', requires=IS_NOT_EMPTY()),
> Field('tel', requires=IS_NOT_EMPTY()),
> Field('email', requires=IS_NOT_EMPTY()),
> format='%(govName)s',  
> migrate=False,fake_migrate=True)
>
>
> db.define_table('governmenttForm',
> Field('formImage', 'upload'),
> Field('filledformImage', 'upload'),
> Field('formName',requires=IS_NOT_EMPTY()),
> Field('governmentName', 'reference government'), 
> 
> format='%(formName)s',migrate=False,fake_migrate=True)
>

[web2py] Re: FOREIGN KEY constraint failed

[Scheme Lab]

I think the filename ends with .sqlite doesnt it?

On Wednesday, July 5, 2017 at 3:07:46 AM UTC-4, Dave S wrote:
>
>
>
> On Tuesday, July 4, 2017 at 11:34:11 PM UTC-7, mostwanted wrote:
>>
>> Hey Santi, how do i go about doing that, deleting all the tables in the 
>> database backup folder?
>>
>
> The easiest way is to use a command line, and delete the .table files.  In 
> a linux system, and picking the application name from the ticket,
>
> cd [web2pydir]/applications/formulator/databases
> rm *.table
>
> For Windows, the same except substitute del for rm.
>
> (the "backup" mentioned may be a nod to a hosting service, where you might 
> not manipulate the file system directly..  For instance, for heroku, I 
> think you'd actually do a git delete of any dot-table files, but I've only 
> done node.js on heroku, so I'm just waving my hands.)
>
> /dps
>
>
>
>> On Tuesday, July 4, 2017 at 12:08:44 AM UTC+2, Santiago Cartasegna wrote:
>>>
>>> Go to the database folder backup and delete all the .tables. Perhaps a 
>>> change was not reflected on the creation of the table.
>>>
>>> A similar error ocurred to me and it was on the creation of the id's 
>>> references on the forange. But your code looks ok
>>>
>>> El lunes, 3 de julio de 2017, 15:50:45 (UTC-3), mostwanted escribió:

 this is the actual code Santi:

 db.define_table('government',
 Field('govName', requires=IS_NOT_EMPTY()),
 Field('tel', requires=IS_NOT_EMPTY()),
 Field('email', requires=IS_NOT_EMPTY()),
 format='%(govName)s',  migrate=False,fake_migrate=True)


 db.define_table(govmntForm',

 Field('formImage', 'upload'),
 Field('filledformImage', 'upload'),
 Field('formName',requires=IS_NOT_EMPTY()),
 Field('governmentName', 'reference government'), 
 format='%(formName)s',migrate=False,fake_migrate=True)

 db.define_table('governmetFormPages',
 Field('formImage', 'upload'),
 Field('formTitle',requires=IS_NOT_EMPTY()), 
 Field('formName', 'reference 
 govmntForm'),migrate=False,fake_migrate=True)


 On Monday, July 3, 2017 at 4:51:47 PM UTC+2, Santiago Cartasegna wrote:
>
> Hello,
>
> You should reference the table with the same name that is created so 
> the table sould be like:
>
> db.define_table('governmetFormPages',
> Field('formImage', 'upload'),
> Field('formTitle',requires=IS_NOT_EMPTY()), 
> Field('formName', 'reference governmenttForm
> '),migrate=False,fake_migrate=True)
>
> Best regards
>
>
> El lunes, 3 de julio de 2017, 4:15:18 (UTC-3), mostwanted escribió:
>>
>> It is the actually code Tony, i just changed it after posting it. The 
>> table name was initially *govmntForm* as it is referenced in the 
>> *governmetFormPages* table.
>>
>> On Sunday, July 2, 2017 at 7:48:43 PM UTC+2, Anthony wrote:
>>>
>>> Not sure if you're showing your actual code, but the table name is 
>>> spelled "governmenttForm" (note the extra "t"), and for the field type, 
>>> you 
>>> have "reference govmntForm".
>>>
>>> Anthony
>>>
>>> On Sunday, July 2, 2017 at 4:37:22 AM UTC-4, mostwanted wrote:

 Hi guys, i need help, i'm pulling my hairs out over here, i have a 
 website where when i'm trying to record an entry i get an error:
  FOREIGN KEY constraint failed


 I have been able to create 2 forms but other attempts to create 
 more forms fail!!


 These are my tables;


 db.define_table('government',
 Field('govName', requires=IS_NOT_EMPTY()),
 Field('tel', requires=IS_NOT_EMPTY()),
 Field('email', requires=IS_NOT_EMPTY()),
 format='%(govName)s',  
 migrate=False,fake_migrate=True)


 db.define_table('governmenttForm',
 Field('formImage', 'upload'),
 Field('filledformImage', 'upload'),
 Field('formName',requires=IS_NOT_EMPTY()),
 Field('governmentName', 'reference government'), 
 
 format='%(formName)s',migrate=False,fake_migrate=True)

 db.define_table('governmetFormPages',
 Field('formImage', 'upload'),
 Field('formTitle',requires=IS_NOT_EMPTY()), 
 Field('formName', 'reference 
 govmntForm'),migrate=False,fake_migrate=True)


 I am able to create a form for the governmentForm table but when i 
 try to load up 

Re: [web2py] Re: FOREIGN KEY constraint failed

[Santiago Cartasegna]

That would do the trick.

The backup means thtat before deleteng the files, you backup the folder!

2017-07-05 4:07 GMT-03:00 Dave S :

>
>
> On Tuesday, July 4, 2017 at 11:34:11 PM UTC-7, mostwanted wrote:
>>
>> Hey Santi, how do i go about doing that, deleting all the tables in the
>> database backup folder?
>>
>
> The easiest way is to use a command line, and delete the .table files.  In
> a linux system, and picking the application name from the ticket,
>
> cd [web2pydir]/applications/formulator/databases
> rm *.table
>
> For Windows, the same except substitute del for rm.
>
> (the "backup" mentioned may be a nod to a hosting service, where you might
> not manipulate the file system directly..  For instance, for heroku, I
> think you'd actually do a git delete of any dot-table files, but I've only
> done node.js on heroku, so I'm just waving my hands.)
>
> /dps
>
>
>
>> On Tuesday, July 4, 2017 at 12:08:44 AM UTC+2, Santiago Cartasegna wrote:
>>>
>>> Go to the database folder backup and delete all the .tables. Perhaps a
>>> change was not reflected on the creation of the table.
>>>
>>> A similar error ocurred to me and it was on the creation of the id's
>>> references on the forange. But your code looks ok
>>>
>>> El lunes, 3 de julio de 2017, 15:50:45 (UTC-3), mostwanted escribió:

 this is the actual code Santi:

 db.define_table('government',
 Field('govName', requires=IS_NOT_EMPTY()),
 Field('tel', requires=IS_NOT_EMPTY()),
 Field('email', requires=IS_NOT_EMPTY()),
 format='%(govName)s',  migrate=False,fake_migrate=True)


 db.define_table(govmntForm',

 Field('formImage', 'upload'),
 Field('filledformImage', 'upload'),
 Field('formName',requires=IS_NOT_EMPTY()),
 Field('governmentName', 'reference government'),
 format='%(formName)s',migrate=False,fake_migrate=True)

 db.define_table('governmetFormPages',
 Field('formImage', 'upload'),
 Field('formTitle',requires=IS_NOT_EMPTY()),
 Field('formName', 'reference
 govmntForm'),migrate=False,fake_migrate=True)


 On Monday, July 3, 2017 at 4:51:47 PM UTC+2, Santiago Cartasegna wrote:
>
> Hello,
>
> You should reference the table with the same name that is created so
> the table sould be like:
>
> db.define_table('governmetFormPages',
> Field('formImage', 'upload'),
> Field('formTitle',requires=IS_NOT_EMPTY()),
> Field('formName', 'reference governmenttForm
> '),migrate=False,fake_migrate=True)
>
> Best regards
>
>
> El lunes, 3 de julio de 2017, 4:15:18 (UTC-3), mostwanted escribió:
>>
>> It is the actually code Tony, i just changed it after posting it. The
>> table name was initially *govmntForm* as it is referenced in the
>> *governmetFormPages* table.
>>
>> On Sunday, July 2, 2017 at 7:48:43 PM UTC+2, Anthony wrote:
>>>
>>> Not sure if you're showing your actual code, but the table name is
>>> spelled "governmenttForm" (note the extra "t"), and for the field type, 
>>> you
>>> have "reference govmntForm".
>>>
>>> Anthony
>>>
>>> On Sunday, July 2, 2017 at 4:37:22 AM UTC-4, mostwanted wrote:

 Hi guys, i need help, i'm pulling my hairs out over here, i have a
 website where when i'm trying to record an entry i get an error:
  FOREIGN KEY constraint failed


 I have been able to create 2 forms but other attempts to create
 more forms fail!!


 These are my tables;


 db.define_table('government',
 Field('govName', requires=IS_NOT_EMPTY()),
 Field('tel', requires=IS_NOT_EMPTY()),
 Field('email', requires=IS_NOT_EMPTY()),
 format='%(govName)s',
 migrate=False,fake_migrate=True)


 db.define_table('governmenttForm',
 Field('formImage', 'upload'),
 Field('filledformImage', 'upload'),
 Field('formName',requires=IS_NOT_EMPTY()),
 Field('governmentName', 'reference government'),
 format='%(formName)s',migrate=
 False,fake_migrate=True)

 db.define_table('governmetFormPages',
 Field('formImage', 'upload'),
 Field('formTitle',requires=IS_NOT_EMPTY()),
 Field('formName', 'reference
 govmntForm'),migrate=False,fake_migrate=True)


 I am able to create a form for the governmentForm table but when i
 try to load up the

[web2py] Re: FOREIGN KEY constraint failed

[Dave S]

On Tuesday, July 4, 2017 at 11:34:11 PM UTC-7, mostwanted wrote:
>
> Hey Santi, how do i go about doing that, deleting all the tables in the 
> database backup folder?
>

The easiest way is to use a command line, and delete the .table files.  In 
a linux system, and picking the application name from the ticket,

cd [web2pydir]/applications/formulator/databases
rm *.table

For Windows, the same except substitute del for rm.

(the "backup" mentioned may be a nod to a hosting service, where you might 
not manipulate the file system directly..  For instance, for heroku, I 
think you'd actually do a git delete of any dot-table files, but I've only 
done node.js on heroku, so I'm just waving my hands.)

/dps



> On Tuesday, July 4, 2017 at 12:08:44 AM UTC+2, Santiago Cartasegna wrote:
>>
>> Go to the database folder backup and delete all the .tables. Perhaps a 
>> change was not reflected on the creation of the table.
>>
>> A similar error ocurred to me and it was on the creation of the id's 
>> references on the forange. But your code looks ok
>>
>> El lunes, 3 de julio de 2017, 15:50:45 (UTC-3), mostwanted escribió:
>>>
>>> this is the actual code Santi:
>>>
>>> db.define_table('government',
>>> Field('govName', requires=IS_NOT_EMPTY()),
>>> Field('tel', requires=IS_NOT_EMPTY()),
>>> Field('email', requires=IS_NOT_EMPTY()),
>>> format='%(govName)s',  migrate=False,fake_migrate=True)
>>>
>>>
>>> db.define_table(govmntForm',
>>>
>>> Field('formImage', 'upload'),
>>> Field('filledformImage', 'upload'),
>>> Field('formName',requires=IS_NOT_EMPTY()),
>>> Field('governmentName', 'reference government'), 
>>> format='%(formName)s',migrate=False,fake_migrate=True)
>>>
>>> db.define_table('governmetFormPages',
>>> Field('formImage', 'upload'),
>>> Field('formTitle',requires=IS_NOT_EMPTY()), 
>>> Field('formName', 'reference 
>>> govmntForm'),migrate=False,fake_migrate=True)
>>>
>>>
>>> On Monday, July 3, 2017 at 4:51:47 PM UTC+2, Santiago Cartasegna wrote:

 Hello,

 You should reference the table with the same name that is created so 
 the table sould be like:

 db.define_table('governmetFormPages',
 Field('formImage', 'upload'),
 Field('formTitle',requires=IS_NOT_EMPTY()), 
 Field('formName', 'reference governmenttForm
 '),migrate=False,fake_migrate=True)

 Best regards


 El lunes, 3 de julio de 2017, 4:15:18 (UTC-3), mostwanted escribió:
>
> It is the actually code Tony, i just changed it after posting it. The 
> table name was initially *govmntForm* as it is referenced in the 
> *governmetFormPages* table.
>
> On Sunday, July 2, 2017 at 7:48:43 PM UTC+2, Anthony wrote:
>>
>> Not sure if you're showing your actual code, but the table name is 
>> spelled "governmenttForm" (note the extra "t"), and for the field type, 
>> you 
>> have "reference govmntForm".
>>
>> Anthony
>>
>> On Sunday, July 2, 2017 at 4:37:22 AM UTC-4, mostwanted wrote:
>>>
>>> Hi guys, i need help, i'm pulling my hairs out over here, i have a 
>>> website where when i'm trying to record an entry i get an error:
>>>  FOREIGN KEY constraint failed
>>>
>>>
>>> I have been able to create 2 forms but other attempts to create more 
>>> forms fail!!
>>>
>>>
>>> These are my tables;
>>>
>>>
>>> db.define_table('government',
>>> Field('govName', requires=IS_NOT_EMPTY()),
>>> Field('tel', requires=IS_NOT_EMPTY()),
>>> Field('email', requires=IS_NOT_EMPTY()),
>>> format='%(govName)s',  
>>> migrate=False,fake_migrate=True)
>>>
>>>
>>> db.define_table('governmenttForm',
>>> Field('formImage', 'upload'),
>>> Field('filledformImage', 'upload'),
>>> Field('formName',requires=IS_NOT_EMPTY()),
>>> Field('governmentName', 'reference government'), 
>>> 
>>> format='%(formName)s',migrate=False,fake_migrate=True)
>>>
>>> db.define_table('governmetFormPages',
>>> Field('formImage', 'upload'),
>>> Field('formTitle',requires=IS_NOT_EMPTY()), 
>>> Field('formName', 'reference 
>>> govmntForm'),migrate=False,fake_migrate=True)
>>>
>>>
>>> I am able to create a form for the governmentForm table but when i 
>>> try to load up the pages for governmentFormPages table i get the error
>>>  FOREIGN KEY constraint failed 
>>>
>>>
>>> Below is the stack
>>> Error ticket for "formulator" Ticket ID 
>>>
>>> 127.0.0.1.2017-07-02.10-06-22.18c363e3-13c2-47f7-ac

[web2py] Re: FOREIGN KEY constraint failed

[mostwanted]

Hey Santi, how do i go about doing that, deleting all the tables in the 
database backup folder?

On Tuesday, July 4, 2017 at 12:08:44 AM UTC+2, Santiago Cartasegna wrote:
>
> Go to the database folder backup and delete all the .tables. Perhaps a 
> change was not reflected on the creation of the table.
>
> A similar error ocurred to me and it was on the creation of the id's 
> references on the forange. But your code looks ok
>
> El lunes, 3 de julio de 2017, 15:50:45 (UTC-3), mostwanted escribió:
>>
>> this is the actual code Santi:
>>
>> db.define_table('government',
>> Field('govName', requires=IS_NOT_EMPTY()),
>> Field('tel', requires=IS_NOT_EMPTY()),
>> Field('email', requires=IS_NOT_EMPTY()),
>> format='%(govName)s',  migrate=False,fake_migrate=True)
>>
>>
>> db.define_table(govmntForm',
>>
>> Field('formImage', 'upload'),
>> Field('filledformImage', 'upload'),
>> Field('formName',requires=IS_NOT_EMPTY()),
>> Field('governmentName', 'reference government'), 
>> format='%(formName)s',migrate=False,fake_migrate=True)
>>
>> db.define_table('governmetFormPages',
>> Field('formImage', 'upload'),
>> Field('formTitle',requires=IS_NOT_EMPTY()), 
>> Field('formName', 'reference 
>> govmntForm'),migrate=False,fake_migrate=True)
>>
>>
>> On Monday, July 3, 2017 at 4:51:47 PM UTC+2, Santiago Cartasegna wrote:
>>>
>>> Hello,
>>>
>>> You should reference the table with the same name that is created so the 
>>> table sould be like:
>>>
>>> db.define_table('governmetFormPages',
>>> Field('formImage', 'upload'),
>>> Field('formTitle',requires=IS_NOT_EMPTY()), 
>>> Field('formName', 'reference governmenttForm
>>> '),migrate=False,fake_migrate=True)
>>>
>>> Best regards
>>>
>>>
>>> El lunes, 3 de julio de 2017, 4:15:18 (UTC-3), mostwanted escribió:

 It is the actually code Tony, i just changed it after posting it. The 
 table name was initially *govmntForm* as it is referenced in the 
 *governmetFormPages* table.

 On Sunday, July 2, 2017 at 7:48:43 PM UTC+2, Anthony wrote:
>
> Not sure if you're showing your actual code, but the table name is 
> spelled "governmenttForm" (note the extra "t"), and for the field type, 
> you 
> have "reference govmntForm".
>
> Anthony
>
> On Sunday, July 2, 2017 at 4:37:22 AM UTC-4, mostwanted wrote:
>>
>> Hi guys, i need help, i'm pulling my hairs out over here, i have a 
>> website where when i'm trying to record an entry i get an error:
>>  FOREIGN KEY constraint failed
>>
>>
>> I have been able to create 2 forms but other attempts to create more 
>> forms fail!!
>>
>>
>> These are my tables;
>>
>>
>> db.define_table('government',
>> Field('govName', requires=IS_NOT_EMPTY()),
>> Field('tel', requires=IS_NOT_EMPTY()),
>> Field('email', requires=IS_NOT_EMPTY()),
>> format='%(govName)s',  
>> migrate=False,fake_migrate=True)
>>
>>
>> db.define_table('governmenttForm',
>> Field('formImage', 'upload'),
>> Field('filledformImage', 'upload'),
>> Field('formName',requires=IS_NOT_EMPTY()),
>> Field('governmentName', 'reference government'), 
>> format='%(formName)s',migrate=False,fake_migrate=True)
>>
>> db.define_table('governmetFormPages',
>> Field('formImage', 'upload'),
>> Field('formTitle',requires=IS_NOT_EMPTY()), 
>> Field('formName', 'reference 
>> govmntForm'),migrate=False,fake_migrate=True)
>>
>>
>> I am able to create a form for the governmentForm table but when i 
>> try to load up the pages for governmentFormPages table i get the error
>>  FOREIGN KEY constraint failed 
>>
>>
>> Below is the stack
>> Error ticket for "formulator" Ticket ID 
>>
>> 127.0.0.1.2017-07-02.10-06-22.18c363e3-13c2-47f7-ace4-3692239493c4
>>  FOREIGN KEY constraint failed 
>> Version 
>> web2py™ Version 2.14.6-stable+timestamp.2016.05.10.00.21.47 Traceback 
>>
>> 1.
>> 2.
>> 3.
>> 4.
>> 5.
>> 6.
>> 7.
>> 8.
>> 9.
>> 10.
>> 11.
>> 12.
>> 13.
>> 14.
>> 15.
>> 16.
>>
>> Traceback (most recent call last):
>>   File "/home/mostwanted/web2py/gluon/restricted.py", line 227, in 
>> restricted
>> exec ccode in environment
>>   File 
>> "/home/mostwanted/web2py/applications/formulator/controllers/appadmin.py"
>>  
>> ,
>>  line 703, in 
>>   File "/home/mos

[web2py] Re: FOREIGN KEY constraint failed

[Santiago Cartasegna]

Go to the database folder backup and delete all the .tables. Perhaps a 
change was not reflected on the creation of the table.

A similar error ocurred to me and it was on the creation of the id's 
references on the forange. But your code looks ok

El lunes, 3 de julio de 2017, 15:50:45 (UTC-3), mostwanted escribió:
>
> this is the actual code Santi:
>
> db.define_table('government',
> Field('govName', requires=IS_NOT_EMPTY()),
> Field('tel', requires=IS_NOT_EMPTY()),
> Field('email', requires=IS_NOT_EMPTY()),
> format='%(govName)s',  migrate=False,fake_migrate=True)
>
>
> db.define_table(govmntForm',
>
> Field('formImage', 'upload'),
> Field('filledformImage', 'upload'),
> Field('formName',requires=IS_NOT_EMPTY()),
> Field('governmentName', 'reference government'), 
> format='%(formName)s',migrate=False,fake_migrate=True)
>
> db.define_table('governmetFormPages',
> Field('formImage', 'upload'),
> Field('formTitle',requires=IS_NOT_EMPTY()), 
> Field('formName', 'reference 
> govmntForm'),migrate=False,fake_migrate=True)
>
>
> On Monday, July 3, 2017 at 4:51:47 PM UTC+2, Santiago Cartasegna wrote:
>>
>> Hello,
>>
>> You should reference the table with the same name that is created so the 
>> table sould be like:
>>
>> db.define_table('governmetFormPages',
>> Field('formImage', 'upload'),
>> Field('formTitle',requires=IS_NOT_EMPTY()), 
>> Field('formName', 'reference governmenttForm
>> '),migrate=False,fake_migrate=True)
>>
>> Best regards
>>
>>
>> El lunes, 3 de julio de 2017, 4:15:18 (UTC-3), mostwanted escribió:
>>>
>>> It is the actually code Tony, i just changed it after posting it. The 
>>> table name was initially *govmntForm* as it is referenced in the 
>>> *governmetFormPages* table.
>>>
>>> On Sunday, July 2, 2017 at 7:48:43 PM UTC+2, Anthony wrote:

 Not sure if you're showing your actual code, but the table name is 
 spelled "governmenttForm" (note the extra "t"), and for the field type, 
 you 
 have "reference govmntForm".

 Anthony

 On Sunday, July 2, 2017 at 4:37:22 AM UTC-4, mostwanted wrote:
>
> Hi guys, i need help, i'm pulling my hairs out over here, i have a 
> website where when i'm trying to record an entry i get an error:
>  FOREIGN KEY constraint failed
>
>
> I have been able to create 2 forms but other attempts to create more 
> forms fail!!
>
>
> These are my tables;
>
>
> db.define_table('government',
> Field('govName', requires=IS_NOT_EMPTY()),
> Field('tel', requires=IS_NOT_EMPTY()),
> Field('email', requires=IS_NOT_EMPTY()),
> format='%(govName)s',  migrate=False,fake_migrate=True)
>
>
> db.define_table('governmenttForm',
> Field('formImage', 'upload'),
> Field('filledformImage', 'upload'),
> Field('formName',requires=IS_NOT_EMPTY()),
> Field('governmentName', 'reference government'), 
> format='%(formName)s',migrate=False,fake_migrate=True)
>
> db.define_table('governmetFormPages',
> Field('formImage', 'upload'),
> Field('formTitle',requires=IS_NOT_EMPTY()), 
> Field('formName', 'reference 
> govmntForm'),migrate=False,fake_migrate=True)
>
>
> I am able to create a form for the governmentForm table but when i try 
> to load up the pages for governmentFormPages table i get the error
>  FOREIGN KEY constraint failed 
>
>
> Below is the stack
> Error ticket for "formulator" Ticket ID 
>
> 127.0.0.1.2017-07-02.10-06-22.18c363e3-13c2-47f7-ace4-3692239493c4
>  FOREIGN KEY constraint failed Version 
> web2py™ Version 2.14.6-stable+timestamp.2016.05.10.00.21.47 Traceback 
>
> 1.
> 2.
> 3.
> 4.
> 5.
> 6.
> 7.
> 8.
> 9.
> 10.
> 11.
> 12.
> 13.
> 14.
> 15.
> 16.
>
> Traceback (most recent call last):
>   File "/home/mostwanted/web2py/gluon/restricted.py", line 227, in 
> restricted
> exec ccode in environment
>   File 
> "/home/mostwanted/web2py/applications/formulator/controllers/appadmin.py" 
> ,
>  line 703, in 
>   File "/home/mostwanted/web2py/gluon/globals.py", line 417, in 
> self._caller = lambda f: f()
>   File 
> "/home/mostwanted/web2py/applications/formulator/controllers/appadmin.py" 
> ,
>  line 151, in insert
> if form.accepts(request.vars, sessi

[web2py] Re: FOREIGN KEY constraint failed

[mostwanted]

this is the actual code Santi:

db.define_table('government',
Field('govName', requires=IS_NOT_EMPTY()),
Field('tel', requires=IS_NOT_EMPTY()),
Field('email', requires=IS_NOT_EMPTY()),
format='%(govName)s',  migrate=False,fake_migrate=True)


db.define_table(govmntForm',

Field('formImage', 'upload'),
Field('filledformImage', 'upload'),
Field('formName',requires=IS_NOT_EMPTY()),
Field('governmentName', 'reference government'), 
format='%(formName)s',migrate=False,fake_migrate=True)

db.define_table('governmetFormPages',
Field('formImage', 'upload'),
Field('formTitle',requires=IS_NOT_EMPTY()), 
Field('formName', 'reference 
govmntForm'),migrate=False,fake_migrate=True)


On Monday, July 3, 2017 at 4:51:47 PM UTC+2, Santiago Cartasegna wrote:
>
> Hello,
>
> You should reference the table with the same name that is created so the 
> table sould be like:
>
> db.define_table('governmetFormPages',
> Field('formImage', 'upload'),
> Field('formTitle',requires=IS_NOT_EMPTY()), 
> Field('formName', 'reference governmenttForm
> '),migrate=False,fake_migrate=True)
>
> Best regards
>
>
> El lunes, 3 de julio de 2017, 4:15:18 (UTC-3), mostwanted escribió:
>>
>> It is the actually code Tony, i just changed it after posting it. The 
>> table name was initially *govmntForm* as it is referenced in the 
>> *governmetFormPages* table.
>>
>> On Sunday, July 2, 2017 at 7:48:43 PM UTC+2, Anthony wrote:
>>>
>>> Not sure if you're showing your actual code, but the table name is 
>>> spelled "governmenttForm" (note the extra "t"), and for the field type, you 
>>> have "reference govmntForm".
>>>
>>> Anthony
>>>
>>> On Sunday, July 2, 2017 at 4:37:22 AM UTC-4, mostwanted wrote:

 Hi guys, i need help, i'm pulling my hairs out over here, i have a 
 website where when i'm trying to record an entry i get an error:
  FOREIGN KEY constraint failed


 I have been able to create 2 forms but other attempts to create more 
 forms fail!!


 These are my tables;


 db.define_table('government',
 Field('govName', requires=IS_NOT_EMPTY()),
 Field('tel', requires=IS_NOT_EMPTY()),
 Field('email', requires=IS_NOT_EMPTY()),
 format='%(govName)s',  migrate=False,fake_migrate=True)


 db.define_table('governmenttForm',
 Field('formImage', 'upload'),
 Field('filledformImage', 'upload'),
 Field('formName',requires=IS_NOT_EMPTY()),
 Field('governmentName', 'reference government'), 
 format='%(formName)s',migrate=False,fake_migrate=True)

 db.define_table('governmetFormPages',
 Field('formImage', 'upload'),
 Field('formTitle',requires=IS_NOT_EMPTY()), 
 Field('formName', 'reference 
 govmntForm'),migrate=False,fake_migrate=True)


 I am able to create a form for the governmentForm table but when i try 
 to load up the pages for governmentFormPages table i get the error
  FOREIGN KEY constraint failed 


 Below is the stack
 Error ticket for "formulator" Ticket ID 

 127.0.0.1.2017-07-02.10-06-22.18c363e3-13c2-47f7-ace4-3692239493c4
  FOREIGN KEY constraint failed Version 
 web2py™ Version 2.14.6-stable+timestamp.2016.05.10.00.21.47 Traceback 

 1.
 2.
 3.
 4.
 5.
 6.
 7.
 8.
 9.
 10.
 11.
 12.
 13.
 14.
 15.
 16.

 Traceback (most recent call last):
   File "/home/mostwanted/web2py/gluon/restricted.py", line 227, in 
 restricted
 exec ccode in environment
   File 
 "/home/mostwanted/web2py/applications/formulator/controllers/appadmin.py" 
 ,
  line 703, in 
   File "/home/mostwanted/web2py/gluon/globals.py", line 417, in 
 self._caller = lambda f: f()
   File 
 "/home/mostwanted/web2py/applications/formulator/controllers/appadmin.py" 
 ,
  line 151, in insert
 if form.accepts(request.vars, session):
   File "/home/mostwanted/web2py/gluon/sqlhtml.py", line 1746, in accepts
 self.vars.id = self.table.insert(**fields)
   File "/home/mostwanted/web2py/gluon/packages/dal/pydal/objects.py", line 
 726, in insert
 ret = self._db._adapter.insert(self, self._listify(fields))
   File 
 "/home/mostwanted/web2py/gluon/packages/dal/pydal/adapters/base.py", line 
 746, in insert
 raise e
 IntegrityError: FOREIGN KEY constrain

[web2py] Re: FOREIGN KEY constraint failed

[Santiago Cartasegna]

Hello,

You should reference the table with the same name that is created so the 
table sould be like:

db.define_table('governmetFormPages',
Field('formImage', 'upload'),
Field('formTitle',requires=IS_NOT_EMPTY()), 
Field('formName', 'reference governmenttForm
'),migrate=False,fake_migrate=True)

Best regards


El lunes, 3 de julio de 2017, 4:15:18 (UTC-3), mostwanted escribió:
>
> It is the actually code Tony, i just changed it after posting it. The 
> table name was initially *govmntForm* as it is referenced in the 
> *governmetFormPages* table.
>
> On Sunday, July 2, 2017 at 7:48:43 PM UTC+2, Anthony wrote:
>>
>> Not sure if you're showing your actual code, but the table name is 
>> spelled "governmenttForm" (note the extra "t"), and for the field type, you 
>> have "reference govmntForm".
>>
>> Anthony
>>
>> On Sunday, July 2, 2017 at 4:37:22 AM UTC-4, mostwanted wrote:
>>>
>>> Hi guys, i need help, i'm pulling my hairs out over here, i have a 
>>> website where when i'm trying to record an entry i get an error:
>>>  FOREIGN KEY constraint failed
>>>
>>>
>>> I have been able to create 2 forms but other attempts to create more 
>>> forms fail!!
>>>
>>>
>>> These are my tables;
>>>
>>>
>>> db.define_table('government',
>>> Field('govName', requires=IS_NOT_EMPTY()),
>>> Field('tel', requires=IS_NOT_EMPTY()),
>>> Field('email', requires=IS_NOT_EMPTY()),
>>> format='%(govName)s',  migrate=False,fake_migrate=True)
>>>
>>>
>>> db.define_table('governmenttForm',
>>> Field('formImage', 'upload'),
>>> Field('filledformImage', 'upload'),
>>> Field('formName',requires=IS_NOT_EMPTY()),
>>> Field('governmentName', 'reference government'), 
>>> format='%(formName)s',migrate=False,fake_migrate=True)
>>>
>>> db.define_table('governmetFormPages',
>>> Field('formImage', 'upload'),
>>> Field('formTitle',requires=IS_NOT_EMPTY()), 
>>> Field('formName', 'reference 
>>> govmntForm'),migrate=False,fake_migrate=True)
>>>
>>>
>>> I am able to create a form for the governmentForm table but when i try 
>>> to load up the pages for governmentFormPages table i get the error
>>>  FOREIGN KEY constraint failed 
>>>
>>>
>>> Below is the stack
>>> Error ticket for "formulator" Ticket ID 
>>>
>>> 127.0.0.1.2017-07-02.10-06-22.18c363e3-13c2-47f7-ace4-3692239493c4
>>>  FOREIGN KEY constraint failed Version 
>>> web2py™ Version 2.14.6-stable+timestamp.2016.05.10.00.21.47 Traceback 
>>>
>>> 1.
>>> 2.
>>> 3.
>>> 4.
>>> 5.
>>> 6.
>>> 7.
>>> 8.
>>> 9.
>>> 10.
>>> 11.
>>> 12.
>>> 13.
>>> 14.
>>> 15.
>>> 16.
>>>
>>> Traceback (most recent call last):
>>>   File "/home/mostwanted/web2py/gluon/restricted.py", line 227, in 
>>> restricted
>>> exec ccode in environment
>>>   File 
>>> "/home/mostwanted/web2py/applications/formulator/controllers/appadmin.py" 
>>> ,
>>>  line 703, in 
>>>   File "/home/mostwanted/web2py/gluon/globals.py", line 417, in 
>>> self._caller = lambda f: f()
>>>   File 
>>> "/home/mostwanted/web2py/applications/formulator/controllers/appadmin.py" 
>>> ,
>>>  line 151, in insert
>>> if form.accepts(request.vars, session):
>>>   File "/home/mostwanted/web2py/gluon/sqlhtml.py", line 1746, in accepts
>>> self.vars.id = self.table.insert(**fields)
>>>   File "/home/mostwanted/web2py/gluon/packages/dal/pydal/objects.py", line 
>>> 726, in insert
>>> ret = self._db._adapter.insert(self, self._listify(fields))
>>>   File "/home/mostwanted/web2py/gluon/packages/dal/pydal/adapters/base.py", 
>>> line 746, in insert
>>> raise e
>>> IntegrityError: FOREIGN KEY constraint failed
>>>
>>>
>>> Please help me, i dont know what to do, i have tried to look for 
>>> solutions online but i just dont understand them, may someone please give 
>>> me light on what i'm doing wrong.
>>>
>>

-- 
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.

[web2py] Re: FOREIGN KEY constraint failed

[mostwanted]

It is the actually code Tony, i just changed it after posting it. The table 
name was initially *govmntForm* as it is referenced in the 
*governmetFormPages* table.

On Sunday, July 2, 2017 at 7:48:43 PM UTC+2, Anthony wrote:
>
> Not sure if you're showing your actual code, but the table name is spelled 
> "governmenttForm" (note the extra "t"), and for the field type, you have 
> "reference govmntForm".
>
> Anthony
>
> On Sunday, July 2, 2017 at 4:37:22 AM UTC-4, mostwanted wrote:
>>
>> Hi guys, i need help, i'm pulling my hairs out over here, i have a 
>> website where when i'm trying to record an entry i get an error:
>>  FOREIGN KEY constraint failed
>>
>>
>> I have been able to create 2 forms but other attempts to create more 
>> forms fail!!
>>
>>
>> These are my tables;
>>
>>
>> db.define_table('government',
>> Field('govName', requires=IS_NOT_EMPTY()),
>> Field('tel', requires=IS_NOT_EMPTY()),
>> Field('email', requires=IS_NOT_EMPTY()),
>> format='%(govName)s',  migrate=False,fake_migrate=True)
>>
>>
>> db.define_table('governmenttForm',
>> Field('formImage', 'upload'),
>> Field('filledformImage', 'upload'),
>> Field('formName',requires=IS_NOT_EMPTY()),
>> Field('governmentName', 'reference government'), 
>> format='%(formName)s',migrate=False,fake_migrate=True)
>>
>> db.define_table('governmetFormPages',
>> Field('formImage', 'upload'),
>> Field('formTitle',requires=IS_NOT_EMPTY()), 
>> Field('formName', 'reference 
>> govmntForm'),migrate=False,fake_migrate=True)
>>
>>
>> I am able to create a form for the governmentForm table but when i try to 
>> load up the pages for governmentFormPages table i get the error
>>  FOREIGN KEY constraint failed 
>>
>>
>> Below is the stack
>> Error ticket for "formulator" Ticket ID 
>>
>> 127.0.0.1.2017-07-02.10-06-22.18c363e3-13c2-47f7-ace4-3692239493c4
>>  FOREIGN KEY constraint failed Version 
>> web2py™ Version 2.14.6-stable+timestamp.2016.05.10.00.21.47 Traceback 
>>
>> 1.
>> 2.
>> 3.
>> 4.
>> 5.
>> 6.
>> 7.
>> 8.
>> 9.
>> 10.
>> 11.
>> 12.
>> 13.
>> 14.
>> 15.
>> 16.
>>
>> Traceback (most recent call last):
>>   File "/home/mostwanted/web2py/gluon/restricted.py", line 227, in restricted
>> exec ccode in environment
>>   File 
>> "/home/mostwanted/web2py/applications/formulator/controllers/appadmin.py" 
>> ,
>>  line 703, in 
>>   File "/home/mostwanted/web2py/gluon/globals.py", line 417, in 
>> self._caller = lambda f: f()
>>   File 
>> "/home/mostwanted/web2py/applications/formulator/controllers/appadmin.py" 
>> ,
>>  line 151, in insert
>> if form.accepts(request.vars, session):
>>   File "/home/mostwanted/web2py/gluon/sqlhtml.py", line 1746, in accepts
>> self.vars.id = self.table.insert(**fields)
>>   File "/home/mostwanted/web2py/gluon/packages/dal/pydal/objects.py", line 
>> 726, in insert
>> ret = self._db._adapter.insert(self, self._listify(fields))
>>   File "/home/mostwanted/web2py/gluon/packages/dal/pydal/adapters/base.py", 
>> line 746, in insert
>> raise e
>> IntegrityError: FOREIGN KEY constraint failed
>>
>>
>> Please help me, i dont know what to do, i have tried to look for 
>> solutions online but i just dont understand them, may someone please give 
>> me light on what i'm doing wrong.
>>
>

-- 
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.

[web2py] Re: FOREIGN KEY constraint failed

[Anthony]

Not sure if you're showing your actual code, but the table name is spelled 
"governmenttForm" (note the extra "t"), and for the field type, you have 
"reference govmntForm".

Anthony

On Sunday, July 2, 2017 at 4:37:22 AM UTC-4, mostwanted wrote:
>
> Hi guys, i need help, i'm pulling my hairs out over here, i have a website 
> where when i'm trying to record an entry i get an error:
>  FOREIGN KEY constraint failed
>
>
> I have been able to create 2 forms but other attempts to create more forms 
> fail!!
>
>
> These are my tables;
>
>
> db.define_table('government',
> Field('govName', requires=IS_NOT_EMPTY()),
> Field('tel', requires=IS_NOT_EMPTY()),
> Field('email', requires=IS_NOT_EMPTY()),
> format='%(govName)s',  migrate=False,fake_migrate=True)
>
>
> db.define_table('governmenttForm',
> Field('formImage', 'upload'),
> Field('filledformImage', 'upload'),
> Field('formName',requires=IS_NOT_EMPTY()),
> Field('governmentName', 'reference government'), 
> format='%(formName)s',migrate=False,fake_migrate=True)
>
> db.define_table('governmetFormPages',
> Field('formImage', 'upload'),
> Field('formTitle',requires=IS_NOT_EMPTY()), 
> Field('formName', 'reference 
> govmntForm'),migrate=False,fake_migrate=True)
>
>
> I am able to create a form for the governmentForm table but when i try to 
> load up the pages for governmentFormPages table i get the error
>  FOREIGN KEY constraint failed 
>
>
> Below is the stack
> Error ticket for "formulator" Ticket ID 
>
> 127.0.0.1.2017-07-02.10-06-22.18c363e3-13c2-47f7-ace4-3692239493c4
>  FOREIGN KEY constraint failed Version 
> web2py™ Version 2.14.6-stable+timestamp.2016.05.10.00.21.47 Traceback 
>
> 1.
> 2.
> 3.
> 4.
> 5.
> 6.
> 7.
> 8.
> 9.
> 10.
> 11.
> 12.
> 13.
> 14.
> 15.
> 16.
>
> Traceback (most recent call last):
>   File "/home/mostwanted/web2py/gluon/restricted.py", line 227, in restricted
> exec ccode in environment
>   File 
> "/home/mostwanted/web2py/applications/formulator/controllers/appadmin.py" 
> ,
>  line 703, in 
>   File "/home/mostwanted/web2py/gluon/globals.py", line 417, in 
> self._caller = lambda f: f()
>   File 
> "/home/mostwanted/web2py/applications/formulator/controllers/appadmin.py" 
> ,
>  line 151, in insert
> if form.accepts(request.vars, session):
>   File "/home/mostwanted/web2py/gluon/sqlhtml.py", line 1746, in accepts
> self.vars.id = self.table.insert(**fields)
>   File "/home/mostwanted/web2py/gluon/packages/dal/pydal/objects.py", line 
> 726, in insert
> ret = self._db._adapter.insert(self, self._listify(fields))
>   File "/home/mostwanted/web2py/gluon/packages/dal/pydal/adapters/base.py", 
> line 746, in insert
> raise e
> IntegrityError: FOREIGN KEY constraint failed
>
>
> Please help me, i dont know what to do, i have tried to look for solutions 
> online but i just dont understand them, may someone please give me light on 
> what i'm doing wrong.
>

-- 
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.

[web2py] Re: Foreign key constraint failed

[pilzsuppe]

Hello Anthony, my eintrag_erstellen is a html view:

--

{{extend 'layout.html'}}

{{block header}}
 
{{end}}


Erstelle einen neuen Beitrag über das Thema {{=category.name.title()}}



{{=form}}

--

I gain access this way: 
1. I click this button in another view:

--

Neuer Eintrag

--

which leads me to this:

--

def erstellen():
rows = db(db.category).select()
form = SQLFORM(db.eintrag).process(next='eintrag_betrachten/[id]')
return locals()

--

and this view:

--

{{extend 'layout.html'}}

{{block header}}
 
{{end}}


Profil | Ausloggen | Mitgliedschaft beenden



Eintrag erstellen


{{for category in rows:}}


{{=A(category.name.title(),_class='btn',_href=URL('eintrag_erstellen',args=category.name))}}

{{pass}}

--

There all my categories got listed in a single button. When I click on one 
of them, I gain acces to my eintrag_erstellen view

-- 
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.

[web2py] Re: Foreign key constraint failed

[Anthony]

How are you generating the URL to access eintrag_erstellen, and are you 
sure the integer you are using in the URL is in fact the record ID of one 
of the records in db.category?

Anthony

On Tuesday, January 10, 2017 at 6:07:17 PM UTC-5, pilzsu...@emailn.de wrote:
>
> Good day,
>
> I was following the video tutorial of Massimo, just as other users here. 
> But when I want to submit a post I get the error
> >IntegrityError: foreign key constraint failed<
>
> ---My db.py:---
>
> db.define_table('category', 
> Field('name',requires=IS_IN_SET(['suess','sauer','frostig','fruchtig','schokoladig','einfach_lecker'])))
>
> db.define_table('eintrag',
> Field('category','reference category',writable=False),
> Field('eintragId', 
> 'integer',readable=False,writable=False),
> Field('standort','string',requires=IS_NOT_EMPTY()),
> Field('laden','string'),
> Field('eis', 
> requires=IS_IN_SET(['Schokolade','Vanille','Erdbeere','Pistazie','Banane','Andere',])),
> Field('eigenschaft','text'),
> auth.signature)
>
> ---My default.py:---
>
> def get_category():
> category_name = request.args(0)
> category = db.category(name=category_name)
> if not category:
> session.flash = 'Element nicht gefunden'
> redirect(URL('eis'))
> return category
>
> def eintrag_erstellen():
> category = get_category()
> db.eintrag.category.default = category.id
> form = SQLFORM(db.eintrag).process(next='eintrag_betrachten/[id]')
> return locals()
>
> ---My view:---
>
> {{extend 'layout.html'}}
>
> {{block header}}
>  
> {{end}}
>
> 
> Erstelle einen neuen Beitrag über das Thema {{=category.name.title()}}
> 
>
> 
>
> {{=form}}
>
>
>
> -
> this is exactly the same code as massimo used (just with german words 
> instead of english ones, but this does not cause the problem). I already 
> found threads about this problem and their solution was to create tabled 
> with appadmin. I did. But the same error. 
> If I delete the row in default.py >db.eintrag.category.default = 
> category.id< everything works just fine but the fact that the category 
> field is "None". 
>

-- 
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.

[web2py] Re: FOREIGN KEY constraint failed error when "Click to delete" user account

[Yi Liu]

I read more about "Foreign Key constraint" in sqlite docs (Should have done 
this in the begining).

The error cause is there is a reference key to the auth.user in another 
table. So sqlite cannot delete the user without "ON DELETE CASCADE".

Is there any place I can config "on delete cascade" or I just find a new 
bug? :)

Best, Yi 


On Monday, January 25, 2016 at 8:59:17 AM UTC-8, Massimo Di Pierro wrote:
>
> Do you still get the ticket if you remove the logic for the additional 
> form on the profile page?
>
> On Sunday, 24 January 2016 21:11:33 UTC-6, Yi Liu wrote:
>>
>> Dear fellow w2p users,
>>
>> First, thank you so much for providing this great app Web2Py for *free*.
>>
>> Recently I updated my app to allow user to delete account themselves.
>>
>> auth.settings.allow_delete_accounts= True
>>
>>
>> Today, I received feedback from user that that function is broken on my 
>> site.
>>
>> Then I realize there is a ticket when that function is called.
>>
>>  FOREIGN KEY constraint failed
>> Version
>> web2py™ Version 2.13.4-stable+timestamp.2015.12.26.04.59.39
>> Python Python 2.7.11: 
>> /home/liuyidh/.pyenv/versions/miniconda-3.16.0/bin/python (prefix: 
>> /home/liuyidh/.pyenv/versions/miniconda-3.16.0)
>> Traceback
>>
>> 1.
>> 2.
>> 3.
>> 4.
>> 5.
>> 6.
>> 7.
>> 8.
>> 9.
>> 10.
>> 11.
>> 12.
>> 13.
>> 14.
>> 15.
>> 16.
>> 17.
>> 18.
>> 19.
>> 20.
>>
>> Traceback (most recent call last):
>>   File "/home/liuyidh/lovelovebean.com/gluon/restricted.py", line 227, in 
>> restricted
>> exec ccode in environment
>>   File 
>> "/home/liuyidh/lovelovebean.com/applications/LLBean/controllers/default.py" 
>> , line 97, 
>> in 
>>   File "/home/liuyidh/lovelovebean.com/gluon/globals.py", line 412, in 
>> 
>> self._caller = lambda f: f()
>>   File 
>> "/home/liuyidh/lovelovebean.com/applications/LLBean/controllers/default.py" 
>> , line 55, 
>> in user
>> return dict(form=auth(), formFil=formFil)
>>   File "/home/liuyidh/lovelovebean.com/gluon/tools.py", line 1923, in 
>> __call__
>> return getattr(self, args[0])()
>>   File "/home/liuyidh/lovelovebean.com/gluon/tools.py", line 4025, in profile
>> self.log_event(log, self.user)
>>   File "/home/liuyidh/lovelovebean.com/gluon/tools.py", line 2521, in 
>> log_event
>> origin=origin, user_id=user_id)
>>   File "/home/liuyidh/lovelovebean.com/gluon/packages/dal/pydal/objects.py", 
>> line 731, in insert
>> ret = self._db._adapter.insert(self, self._listify(fields))
>>   File 
>> "/home/liuyidh/lovelovebean.com/gluon/packages/dal/pydal/adapters/base.py", 
>> line 746, in insert
>> raise e
>> IntegrityError: FOREIGN KEY constraint failed
>>
>>
>>
>> I searched stackoverflow for tips. It seems the causes are different. 
>> Some are due to migration, some due to wrong reference, others due to 
>> auth().
>>
>> I have a user form in the profile page:
>>
>> def user():
>> if request.args(0)=='profile':
>> db.auth_criteria.user_id.default = auth.user_id
>> record = db.auth_criteria(db.auth_criteria.user_id==auth.user_id)
>> formFil=SQLFORM(db.auth_criteria, 
>> record=record,
>> labels = {'salePrice':XML('TWO-A-DAY Clothing & Home 
>> Goods (By Sale Price)'), 
>>   'tgPrice':XML('ONE-A-DAY Travel and Gear  
>> (By Sale Price)'),
>>   'aveRev':XML('By Avereage Ratings'),
>>   'percSave':XML('By Percentage Saved'),
>>   'genderPref':XML('By Gender of Products 
>> AND-gate. Other filters apply')
>>   },
>> buttons = [TAG.button('Set Mine', _class='btn-primary')])
>> if formFil.process().accepted:
>> response.flash = XML('Your preference is recorded. Get 
>> ready for lovely beans.')
>> return dict(form=auth(), formFil=formFil)
>> else:
>> return dict(form=auth())
>>
>> I have a custom defined table:
>>
>> db.define_table('auth_criteria',
>>Field ('user_id', 
>> 'reference auth_user', readable=False, writable=False),
>>Field ('salePrice', 
>> 'integer', widget=SQLFORM 
>> .widgets.radio.widget, 
>> requires = IS_IN_SET 
>> (salePrice)),
>>Field ('tgPrice', 
>> 'integer', widget=SQLFORM 
>> .widgets.radio.widget, 
>> requires = IS_IN_SET 
>> (tgPrice)),
>>Field ('aveRev', 
>> 'integer', requires = IS_IN_SET 
>> (aveRev)),
>>Field 

[web2py] Re: FOREIGN KEY constraint failed error when "Click to delete" user account

[Yi Liu]

Thank you for your suggestion, Massimo.

I deleted the logic. The only thing under def user() is now:

return dict(form=auth())


I also modified the logic in user.html by ''' '''.


{{
'''
if request.args(0)=='profile':
=H2('Your Filters:')
=XML('Note: Filters are logically OR-gates (except for gender AND-gate). 
You will get an email when ANY of the "price", "ratings", or "percentage 
savings" criteria is matched. If you only want to use ratings filter, y
ou can set sale price to "Nope."')
=formFil
pass
'''
}}


Still get the same error.

Here is the sqlite argument list in the ticket, if it helps:

Function argument list  

(self=, 
table=, 
fields=[(, 'auth'), 
(, datetime.datetime(2016, 1, 25, 9, 
16, 33, 848792)), (, '164.67.9.184'), 
(, 207L), (, 'User 207 Profile updated')])





Thanks again.


On Monday, January 25, 2016 at 8:59:17 AM UTC-8, Massimo Di Pierro wrote:
>
> Do you still get the ticket if you remove the logic for the additional 
> form on the profile page?
>
>

-- 
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.

[web2py] Re: FOREIGN KEY constraint failed error when "Click to delete" user account

[Massimo Di Pierro]

Do you still get the ticket if you remove the logic for the additional form 
on the profile page?

On Sunday, 24 January 2016 21:11:33 UTC-6, Yi Liu wrote:
>
> Dear fellow w2p users,
>
> First, thank you so much for providing this great app Web2Py for *free*.
>
> Recently I updated my app to allow user to delete account themselves.
>
> auth.settings.allow_delete_accounts= True
>
>
> Today, I received feedback from user that that function is broken on my 
> site.
>
> Then I realize there is a ticket when that function is called.
>
>  FOREIGN KEY constraint failed
> Version
> web2py™ Version 2.13.4-stable+timestamp.2015.12.26.04.59.39
> Python Python 2.7.11: 
> /home/liuyidh/.pyenv/versions/miniconda-3.16.0/bin/python (prefix: 
> /home/liuyidh/.pyenv/versions/miniconda-3.16.0)
> Traceback
>
> 1.
> 2.
> 3.
> 4.
> 5.
> 6.
> 7.
> 8.
> 9.
> 10.
> 11.
> 12.
> 13.
> 14.
> 15.
> 16.
> 17.
> 18.
> 19.
> 20.
>
> Traceback (most recent call last):
>   File "/home/liuyidh/lovelovebean.com/gluon/restricted.py", line 227, in 
> restricted
> exec ccode in environment
>   File 
> "/home/liuyidh/lovelovebean.com/applications/LLBean/controllers/default.py" 
> , line 97, in 
> 
>   File "/home/liuyidh/lovelovebean.com/gluon/globals.py", line 412, in 
> 
> self._caller = lambda f: f()
>   File 
> "/home/liuyidh/lovelovebean.com/applications/LLBean/controllers/default.py" 
> , line 55, in 
> user
> return dict(form=auth(), formFil=formFil)
>   File "/home/liuyidh/lovelovebean.com/gluon/tools.py", line 1923, in __call__
> return getattr(self, args[0])()
>   File "/home/liuyidh/lovelovebean.com/gluon/tools.py", line 4025, in profile
> self.log_event(log, self.user)
>   File "/home/liuyidh/lovelovebean.com/gluon/tools.py", line 2521, in 
> log_event
> origin=origin, user_id=user_id)
>   File "/home/liuyidh/lovelovebean.com/gluon/packages/dal/pydal/objects.py", 
> line 731, in insert
> ret = self._db._adapter.insert(self, self._listify(fields))
>   File 
> "/home/liuyidh/lovelovebean.com/gluon/packages/dal/pydal/adapters/base.py", 
> line 746, in insert
> raise e
> IntegrityError: FOREIGN KEY constraint failed
>
>
>
> I searched stackoverflow for tips. It seems the causes are different. Some 
> are due to migration, some due to wrong reference, others due to auth().
>
> I have a user form in the profile page:
>
> def user():
> if request.args(0)=='profile':
> db.auth_criteria.user_id.default = auth.user_id
> record = db.auth_criteria(db.auth_criteria.user_id==auth.user_id)
> formFil=SQLFORM(db.auth_criteria, 
> record=record,
> labels = {'salePrice':XML('TWO-A-DAY Clothing & Home 
> Goods (By Sale Price)'), 
>   'tgPrice':XML('ONE-A-DAY Travel and Gear  
> (By Sale Price)'),
>   'aveRev':XML('By Avereage Ratings'),
>   'percSave':XML('By Percentage Saved'),
>   'genderPref':XML('By Gender of Products 
> AND-gate. Other filters apply')
>   },
> buttons = [TAG.button('Set Mine', _class='btn-primary')])
> if formFil.process().accepted:
> response.flash = XML('Your preference is recorded. Get 
> ready for lovely beans.')
> return dict(form=auth(), formFil=formFil)
> else:
> return dict(form=auth())
>
> I have a custom defined table:
>
> db.define_table('auth_criteria',
>Field ('user_id', 
> 'reference auth_user', readable=False, writable=False),
>Field ('salePrice', 
> 'integer', widget=SQLFORM 
> .widgets.radio.widget, 
> requires = IS_IN_SET 
> (salePrice)),
>Field ('tgPrice', 
> 'integer', widget=SQLFORM 
> .widgets.radio.widget, 
> requires = IS_IN_SET 
> (tgPrice)),
>Field ('aveRev', 
> 'integer', requires = IS_IN_SET 
> (aveRev)),
>Field ('percSave', 
> 'integer', requires = IS_IN_SET 
> (percSave)),
>
> ...

-- 
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...@

Re: [web2py] Re: foreign key constraint failed

[António Ramos]

yep.
you are right.
I was using amostra.n but should be amostra.id

Thank u for your time as usual Leonel.

Regards

2015-03-20 16:50 GMT+00:00 Leonel Câmara :

> In that case amostra.n is not pointing to a valid amostra.
>
> --
> 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.
>

-- 
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.

[web2py] Re: foreign key constraint failed

[Leonel Câmara]

In that case amostra.n is not pointing to a valid amostra.

-- 
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.

Re: [web2py] Re: foreign key constraint failed

[António Ramos]

Nop,
i´m user number 6.


2015-03-20 13:06 GMT+00:00 Leonel Câmara :

> I'm guessing there's no user with the id 6
>
> --
> 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.
>

-- 
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.

[web2py] Re: foreign key constraint failed

[Leonel Câmara]

I'm guessing there's no user with the id 6

-- 
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.

[web2py] Re: (foreign key constraint failed)

[Anthony]

Did this solve your problem?

On Wednesday, February 18, 2015 at 5:07:02 AM UTC-5, Smruti Prakash Mohanty 
wrote:
>
> Hi
>
> I am using the web2py default auth table for user registration.
>
> I am having an additional table defined as such:
>
> db.define_table('nsksystem',
> Field('email_id', db.auth_user,length=512, label = 'Email 
> ID'),
> Field('nskname', length=128, default='', label = 'Machine 
> Name'),
> Field('nskpassword', 'password', 
> length=512,readable=False, label='Machine Password'),
> Field('confirmnskpassword', 'password', 
> length=512,readable=False, label='Confirm Machine Password'),
> Field('nreleaseid',length=128, default='',label = 
> 'Release'),
> Field('isCordinator','boolean',default='', label = 'Is 
> Co-ordinator'))
>
>
> db.nsksystem.email_id.requires = 
> IS_NOT_EMPTY(error_message=auth.messages.is_empty)
> db.nsksystem.email_id.requires = IS_IN_DB(db,'auth_user.email','%(email)s')
> db.nsksystem.nreleaseid.requires = 
> IS_NOT_EMPTY(error_message=auth.messages.is_empty)
> db.nsksystem.nskname.requires = 
> IS_NOT_EMPTY(error_message=auth.messages.is_empty)
> db.nsksystem.confirmnskpassword.requires = IS_EXPR('value==%s' % 
> repr(request.vars.get('nskpassword', None)),error_message='Passwords do not 
> match')
> db.nsksystem.isCordinator.requires=IS_NOT_IN_DB(db(db.nsksystem.nreleaseid 
> == request.vars.nreleaseid), 'nsksystem.isCordinator', 
> error_message='Co-ordinator Already exist for specified release')
>
> In my Controller:
>
> def uregistration(): ##---> For my user registration
> form=SQLFORM(db.auth_user)
> if form.process().accepted:
> response.flash = 'User is registered. Redirecting to machine 
> registration'
> redirect(URL('mregistration')) #-> After registration it goes 
> to another page for machine registration.
> elif form.errors:
> response.flash = 'Form has errors'
> else:
> response.flash = 'Please fill out the form'
> return dict(form=form)
>
>
> def mregistration():  ##---> for machine registration page
> form=SQLFORM(db.nsksystem) 
> if form.process().accepted:
> response.flash = 'Machine is registered to user. Please go to 
> Login page.'
> redirect(URL('index'))
> elif form.errors:
> response.flash = 'Form has errors'
> else:
> response.flash = 'Please fill out the form'
> return dict(form=form)
>
> Now after I have registered the user, i am being redirected to Machine 
> registration page. After i enter all the details in Machine registration 
> page I get this error:
>
> Traceback (most recent call last):
>   File "gluon/restricted.py", line 224, in restricted
>   File "C:/web2py/applications/click/controllers/default.py" 
> , line 
> 63, in 
>   File "gluon/globals.py", line 393, in 
>   File "C:/web2py/applications/click/controllers/default.py" 
> , line 
> 30, in mregistration
> if form.process().accepted:
>   File "gluon/html.py", line 2303, in process
>   File "gluon/html.py", line 2240, in validate
>   File "gluon/sqlhtml.py", line 1677, in accepts
>   File "gluon/dal/objects.py", line 724, in insert
>   File "gluon/dal/adapters/base.py", line 715, in insert
> IntegrityError: foreign key constraint failed
>
> Error snapshot  (foreign key constraint 
> failed)  
>   Function argument list
>   
> (self=, 
> table= (id,email_id,nskname,nskpassword,confirmnskpassword,nreleaseid,isCordinator)>,
>  fields=[(, '123456'), 
> (, 'vodka.ind.codefactory.com'), 
> (, 0), 
> (, True), 
> (, 'AAA'), 
> (, '123456')])
>
>
> I wonder why i dont see the email address that i select in the machine 
> registration page. (, 0) in the Function 
> argument list refers to email id or only ID.
>
> I am confused. Unfortunately i cant turn the constraint validation off as it 
> is a requirement.
> 
>

-- 
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.

[web2py] Re: (foreign key constraint failed)

[Niphlod]

a little bit of wording around "unfortunately I can't turn off"

it's not that having constraints in place is limiting your app. It's 
because something REAALLY wrong with your application that that error pops 
up (and it'll pop up with any other backend). I'd be more inclined to say 
"thanks a lot web2py, I can't turn it off, or else, I'm going to be really 
screwed once I put this on production".

Back on topic

Field('email_id', db.auth_user,length=512, label = 'Email ID')

this is a FK to the field *id* of the table *auth_user*. length doesn't 
obviously go there (as it's an integer) and the validation you're imposing 
is totally wrong

db.nsksystem.email_id.requires = IS_IN_DB(db,'auth_user.email','%(email)s') 

Are you sure you planned your model the way you're supposed to use it ? 
IMHO you're expecting that field to contain an email, not an id.

-- 
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.

[web2py] Re: foreign key constraint failed

[Cliff Kachinske]

This post on StackOverflow may help 
http://stackoverflow.com/questions/9636053/is-there-a-way-to-get-the-constraints-of-a-table-in-sqlite

There is a code snippet you might try.

On Monday, September 22, 2014 5:01:11 PM UTC-4, Anna Kostikova wrote:
>
> Hi everyone,
>
> I am having an issue when editing content of one of the tables in my 
> database. When I try to edit a record in the table I have an error message 
> " foreign key constraint failed". The 
> bizarre thing is that it doesn't matter which column I am trying to edit 
> (via appadmin control panel) - i have exactly same message. For instance, I 
> have a column "description" that doesn't have any keys on it (or 
> references), but I can't edit the content of this field and I keep getting 
> the error above.
>
> What might be causing the issue?
> I am using python 2.7.7 and web2py 2.9.5
>
> Thanks
> Anna
>

-- 
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.

[web2py] Re: foreign key constraint failed

[Leonel Câmara]

Seems like a botched migration to me. Can you tell me what changes you made 
to your model, and a sample of the data you had there before?

-- 
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.

[web2py] Re: foreign key reference and "not null" mutually exclusive. by design, or a bug?

[Ivan]

It seems this bug is still present in 2.9.5


Il giorno venerdì 24 gennaio 2014 03:19:05 UTC+1, User ha scritto:
>
> Thanks.  Here is the issue in case anyone is interested:
>
> http://code.google.com/p/web2py/issues/detail?id=1395
>
>
> On Wednesday, January 22, 2014 3:39:56 PM UTC-5, Anthony wrote:
>
>> This is a bug, and an issue has been created. Yes, you should be able to 
>> make the change directly in the database.
>>
>> Anthony
>>
>> On Wednesday, January 22, 2014 1:13:30 PM UTC-5, User wrote:
>>>
>>> I also would like a non-null foreign key reference, but like OP 
>>> experienced web2py will not create one (I'm using postgresql).  Why is 
>>> this?  And is there a workaround to make reference fields not null?  Or 
>>> worst case can I add the not null constraint manually in the db without 
>>> breaking anything?
>>>
>>>
>>> On Tuesday, June 19, 2012 11:40:37 AM UTC-4, Ivan wrote:
>>>
 Same problem here!
 The "notnull=True" parameter doesn't append the "NOT NULL" clause for 
 reference fields.
 The IS_IN_DB function activates the check at form-level while the 
 notnull parameter should set the constraint at db-level.



-- 
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.

[web2py] Re: Foreign key

[Beat Kohler]

Hi Derek... you are right ... this is it.. works as designed:
Just modified the definition for the MATERIALS; added "format...":

db.define_table("MATERIALS",
  Field ("MATNR", 
"string", length=18, notnull=True, default=None),
  Field ("MAKTX", 
"string", length=30, notnull=True, default=None),
  Field ("MEINS", 
"string", length=5, notnull=True, default=None),
  Field ("WERKS", 
"string", length=5, notnull=True, default=None*), format='%(MATNR)s')*

Thanks a lot
Beat

Am Mittwoch, 30. April 2014 20:02:08 UTC+2 schrieb Derek:
>
> According to the documentation, you can't. Have you tried using a 
> 'represent' on the 'materials' table to show the matnr instead of the id?
>
> On Wednesday, April 30, 2014 7:47:30 AM UTC-7, Beat Kohler wrote:
>>
>> I am on the way to develop a small purchase requisition application. 
>> The main idea is: the user select from a list of materials a material 
>> number and enter the quantity. 
>> Finally he will confirm his requisition and send it to a external system. 
>> To create the purchase requisitions I use a simple smartgrid. 
>> My problem is now that in the requisition table instead of the material 
>> number only the id of the related table is stored.
>> Here my definitions:
>> *db.py*
>> *- Materials*
>> db.define_table("MATERIALS",
>>   Field("MATNR", "string", length=18, notnull=True, 
>> default=None), and more
>> *- Purchase requistions*
>> db.define_table("BANFTEMP",
>>  Field("Pippo", db.auth_user, default=auth.user_id, writable=False, 
>> update=auth.user_id),
>> * Field("MATNR_MATERIALS", db.MATERIALS, default=db.MATERIALS.MATNR, 
>> update=db.MATERIALS.MATNR)*, . and more
>> ..
>> db.BANFTEMP.MATNR_MATERIALS.requires=IS_IN_DB(db, db.MATERIALS.id, 
>> '%(MATNR)s')
>>
>> *default.py*
>>  grid = SQLFORM.grid(db.BANFTEMP)
>>
>> How I can convince WEB2PY to store the MATNR and not the id in 
>> BANFTEMP.MATNR_MATERIALS ??
>> Any help is welcome an a free beer offered (if you stay in Switzerland 
>> :-) )
>> Beat
>>
>

-- 
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.

[web2py] Re: Foreign key

[黄祥]

please try (not tested) :
Field('MATNR_MATERIALS', 'reference MATERIALS', requires = IS_IN_DB(db, 
db.MATERIALS.id, '%(MATNR)s') )

best regards,
stifan

>

-- 
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.

[web2py] Re: Foreign key

[Derek]

According to the documentation, you can't. Have you tried using a 
'represent' on the 'materials' table to show the matnr instead of the id?

On Wednesday, April 30, 2014 7:47:30 AM UTC-7, Beat Kohler wrote:
>
> I am on the way to develop a small purchase requisition application. 
> The main idea is: the user select from a list of materials a material 
> number and enter the quantity. 
> Finally he will confirm his requisition and send it to a external system. 
> To create the purchase requisitions I use a simple smartgrid. 
> My problem is now that in the requisition table instead of the material 
> number only the id of the related table is stored.
> Here my definitions:
> *db.py*
> *- Materials*
> db.define_table("MATERIALS",
>   Field("MATNR", "string", length=18, notnull=True, 
> default=None), and more
> *- Purchase requistions*
> db.define_table("BANFTEMP",
>  Field("Pippo", db.auth_user, default=auth.user_id, writable=False, 
> update=auth.user_id),
> * Field("MATNR_MATERIALS", db.MATERIALS, default=db.MATERIALS.MATNR, 
> update=db.MATERIALS.MATNR)*, . and more
> ..
> db.BANFTEMP.MATNR_MATERIALS.requires=IS_IN_DB(db, db.MATERIALS.id, 
> '%(MATNR)s')
>
> *default.py*
>  grid = SQLFORM.grid(db.BANFTEMP)
>
> How I can convince WEB2PY to store the MATNR and not the id in 
> BANFTEMP.MATNR_MATERIALS ??
> Any help is welcome an a free beer offered (if you stay in Switzerland :-) 
> )
> Beat
>

-- 
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.

[web2py] Re: foreign key reference and "not null" mutually exclusive. by design, or a bug?

[User]

Thanks.  Here is the issue in case anyone is interested:

http://code.google.com/p/web2py/issues/detail?id=1395


On Wednesday, January 22, 2014 3:39:56 PM UTC-5, Anthony wrote:

> This is a bug, and an issue has been created. Yes, you should be able to 
> make the change directly in the database.
>
> Anthony
>
> On Wednesday, January 22, 2014 1:13:30 PM UTC-5, User wrote:
>>
>> I also would like a non-null foreign key reference, but like OP 
>> experienced web2py will not create one (I'm using postgresql).  Why is 
>> this?  And is there a workaround to make reference fields not null?  Or 
>> worst case can I add the not null constraint manually in the db without 
>> breaking anything?
>>
>>
>> On Tuesday, June 19, 2012 11:40:37 AM UTC-4, Ivan wrote:
>>
>>> Same problem here!
>>> The "notnull=True" parameter doesn't append the "NOT NULL" clause for 
>>> reference fields.
>>> The IS_IN_DB function activates the check at form-level while the 
>>> notnull parameter should set the constraint at db-level.
>>>
>>>

-- 
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/groups/opt_out.

[web2py] Re: foreign key reference and "not null" mutually exclusive. by design, or a bug?

[Anthony]

This is a bug, and an issue has been created. Yes, you should be able to 
make the change directly in the database.

Anthony

On Wednesday, January 22, 2014 1:13:30 PM UTC-5, User wrote:
>
> I also would like a non-null foreign key reference, but like OP 
> experienced web2py will not create one (I'm using postgresql).  Why is 
> this?  And is there a workaround to make reference fields not null?  Or 
> worst case can I add the not null constraint manually in the db without 
> breaking anything?
>
>
> On Tuesday, June 19, 2012 11:40:37 AM UTC-4, Ivan wrote:
>
>> Same problem here!
>> The "notnull=True" parameter doesn't append the "NOT NULL" clause for 
>> reference fields.
>> The IS_IN_DB function activates the check at form-level while the notnull 
>> parameter should set the constraint at db-level.
>>
>>

-- 
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/groups/opt_out.

Re: [web2py] Re: foreign key reference and "not null" mutually exclusive. by design, or a bug?

[Richard Vézina]

Of cours you can change you db manually with SQL DLL...

You can still adjust your web2py model and do fake_migrate=True,
migrate=False to get thing in sync...

Richard


On Wed, Jan 22, 2014 at 1:13 PM, User  wrote:

> I also would like a non-null foreign key reference, but like OP
> experienced web2py will not create one.  Why is this?  And is there a
> workaround to make reference fields not null?  Or worst case can I add the
> not null constraint manually in the db without breaking anything?
>
>
> On Tuesday, June 19, 2012 11:40:37 AM UTC-4, Ivan wrote:
>
>> Same problem here!
>> The "notnull=True" parameter doesn't append the "NOT NULL" clause for
>> reference fields.
>> The IS_IN_DB function activates the check at form-level while the notnull
>> parameter should set the constraint at db-level.
>>
>>  --
> 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/groups/opt_out.
>

-- 
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/groups/opt_out.

[web2py] Re: foreign key reference and "not null" mutually exclusive. by design, or a bug?

[User]

I also would like a non-null foreign key reference, but like OP 
experienced web2py will not create one.  Why is this?  And is there a 
workaround to make reference fields not null?  Or worst case can I add the 
not null constraint manually in the db without breaking anything?


On Tuesday, June 19, 2012 11:40:37 AM UTC-4, Ivan wrote:

> Same problem here!
> The "notnull=True" parameter doesn't append the "NOT NULL" clause for 
> reference fields.
> The IS_IN_DB function activates the check at form-level while the notnull 
> parameter should set the constraint at db-level.
>
>

-- 
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/groups/opt_out.

[web2py] Re: foreign key mismatch

[Gael Princivalle]

Now it works, I've saved all data as CSV files, cancel all db files in the 
database folder and import again all data from CSV files.

Sqlite was really in tilt.

Il giorno mercoledì 25 dicembre 2013 01:13:30 UTC+1, Gael Princivalle ha 
scritto:
>
> Hi all.
>
> I'm going mad on this problem.
> Probably my lambda function for the represent products.brand is not 
> correct but I don't know where.
> Like that ny application works, but I cannot add some products rows, 
> web2py says:" foreign key mismatch"
> db.define_table('brands',
> Field('id_01', 'id'),
> Field('name'),
> format='%(name)s')
> db.define_table('products',
> Field('id_01', 'id'),
> Field('code'),
> Field('material_number'),
> Field('description'),
> #brand is an integer, the same as brands.id_01
> Field('brand', 'reference brands', requires = IS_IN_DB(db,db
> .brands.id_01, '%(name)s'),
>   represent=lambda id, r: db.brands[id].name),
> Field('pdf_path'),
> Field('price_list', type='double'),
> Field('in_stock', type='integer'),
> format='%(code)s - %(price_list).2f')
>
> Function argument list 
>
> (self=, table= material_number,description,brand,pdf_path,price_list,in_stock)>, fields
> =[(, '6636356'), (, 'ghgh'
> ), (, 'ghhfh'), (, 782), 
> (, 45.0), (, 0), ( dal.Field object>, 'r35455')])
>
> Code listing 
>
> 1306.
> 1307.
> 1308.
> 1309.
> 1310.
> 1311.
>
> 1312.
> 1313.
> 1314.
> 1315.
>
> self.execute(query)
> except Exception:
> e = sys.exc_info()[1]
> if hasattr(table,'_on_insert_error'):
> return table._on_insert_error(table,fields,e)
> raise e
>
> if hasattr(table,'_primarykey'):
> return dict([(k[0].name, k[1]) for k in fields \
>  if k[0].name in table._primarykey])
> id = self.lastrowid(table)
>
>
>
> Someone can help me ?
>
> Thanks.
>
>

-- 
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/groups/opt_out.

[web2py] Re: foreign key mismatch

[Gael Princivalle]

Hi Leonel.

Yes it was like that before and the problem was the same. 
requires = IS_IN_DB(db, db.brands.id, '%(name)s')

I've tried with id_01 because I want it to be my reference.

Il giorno mercoledì 25 dicembre 2013 01:13:30 UTC+1, Gael Princivalle ha 
scritto:
>
> Hi all.
>
> I'm going mad on this problem.
> Probably my lambda function for the represent products.brand is not 
> correct but I don't know where.
> Like that ny application works, but I cannot add some products rows, 
> web2py says:" foreign key mismatch"
> db.define_table('brands',
> Field('id_01', 'id'),
> Field('name'),
> format='%(name)s')
> db.define_table('products',
> Field('id_01', 'id'),
> Field('code'),
> Field('material_number'),
> Field('description'),
> #brand is an integer, the same as brands.id_01
> Field('brand', 'reference brands', requires = IS_IN_DB(db,db
> .brands.id_01, '%(name)s'),
>   represent=lambda id, r: db.brands[id].name),
> Field('pdf_path'),
> Field('price_list', type='double'),
> Field('in_stock', type='integer'),
> format='%(code)s - %(price_list).2f')
>
> Function argument list 
>
> (self=, table= material_number,description,brand,pdf_path,price_list,in_stock)>, fields
> =[(, '6636356'), (, 'ghgh'
> ), (, 'ghhfh'), (, 782), 
> (, 45.0), (, 0), ( dal.Field object>, 'r35455')])
>
> Code listing 
>
> 1306.
> 1307.
> 1308.
> 1309.
> 1310.
> 1311.
>
> 1312.
> 1313.
> 1314.
> 1315.
>
> self.execute(query)
> except Exception:
> e = sys.exc_info()[1]
> if hasattr(table,'_on_insert_error'):
> return table._on_insert_error(table,fields,e)
> raise e
>
> if hasattr(table,'_primarykey'):
> return dict([(k[0].name, k[1]) for k in fields \
>  if k[0].name in table._primarykey])
> id = self.lastrowid(table)
>
>
>
> Someone can help me ?
>
> Thanks.
>
>

-- 
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/groups/opt_out.

[web2py] Re: foreign key mismatch

[Leonel Câmara]

Why are you using your own id?

Just remove that field and change

 requires = IS_IN_DB(db, db.brands.id_01, '%(name)s')
 to

 requires = IS_IN_DB(db, db.brands.id, '%(name)s')

-- 
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/groups/opt_out.

[web2py] Re: foreign key constraint failed ???

[Niphlod]

foreign_keys = True keeps your data safe. Ignoring it means that your data 
doesn't respect referential integrity (meaning, it's faulty). Every "major" 
db engine forces it by default, SQLite instead needs an explicit 
declaration to enable it.
You'd really need to check if all of your data is consistent ^_^ 

On Sunday, December 8, 2013 6:20:50 PM UTC+1, Alan Etkin wrote:
>
> If possible I prefer to keep foreign_keys=True for db integrity.
>>
>
> Another similar thread 
>
> https://groups.google.com/d/msg/web2py/klspqXpha4E/mCwQ6HC5IhAJ
>

-- 
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/groups/opt_out.

[web2py] Re: foreign key constraint failed ???

[Alan Etkin]

>
> If possible I prefer to keep foreign_keys=True for db integrity.
>

Another similar thread 

https://groups.google.com/d/msg/web2py/klspqXpha4E/mCwQ6HC5IhAJ

-- 
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/groups/opt_out.

[web2py] Re: foreign key constraint failed ???

[Ivan Gazzola]

If possible I prefer to keep foreign_keys=True for db integrity.

Thx

Ivan

Il giorno domenica 8 dicembre 2013 15:41:03 UTC+1, Ivo ha scritto:
>
> though it may not be the preferred solution I resolved it by adding:
> adapter_args=dict(foreign_keys=False) to db = 
> DAL('sqlite://storage.sqlite') in db.py
>
>
> On Sunday, December 8, 2013 3:16:36 PM UTC+1, Ivan Gazzola wrote:
>>
>> I can't resolve this ticket in my app. I try to insert in 
>> Pazienti_Consulenza from appadmin but i've always this error:
>> "
>>  foreign key constraint failed
>> "
>>
>> This is my model:
>>
>> db.define_table('Nominativi',
>> Field('nome',notnull=True, represent=lambda nome:nome.title()),
>> Field('categoria',db.Categorie_Nominativi,default='1'),
>> Field('sesso',requires=IS_EMPTY_OR(IS_IN_SET(['maschio','femmina']))),
>> Field('mail',requires=IS_EMPTY_OR(IS_EMAIL(error_message=T('Inserire 
>> Una Mail Valida',
>> Field('indirizzo'),
>> Field('localita', label='Località'),
>> Field('cap',requires=IS_EMPTY_OR(IS_LENGTH(5,5, 
>> error_message=T('Inserire Un CAP valido',
>> Field('provincia',requires=IS_EMPTY_OR(IS_IN_SET(Elenco_Province, 
>> zero=T('Selezionare una Provincia',
>> Field('codice_fiscale',length=20),
>> Field('partita_iva',length=20),
>> Field('data_di_nascita','date'),
>> Field('professione'),
>> Field('padre'),
>> Field('madre'),
>> Field('inviato_da'),
>> Field('id_precendente','integer'),
>> format=lambda 
>> r:r.nome.title()+'-->'+db.Categorie_Nominativi(r.categoria)['categoria'])
>>
>> db.Nominativi.nome.filter_in = lambda value : 
>> w2p_encrypt(value,key=chiave)
>> db.Nominativi.nome.filter_out = lambda value : 
>> w2p_decrypt(value,key=chiave)
>> db.Nominativi.mail.filter_in = lambda value : 
>> w2p_encrypt(value,key=chiave)
>> db.Nominativi.mail.filter_out = lambda value : 
>> w2p_decrypt(value,key=chiave)
>> db.Nominativi.indirizzo.filter_in = lambda value : 
>> w2p_encrypt(value,key=chiave)
>> db.Nominativi.indirizzo.filter_out = lambda value : 
>> w2p_decrypt(value,key=chiave)
>> db.Nominativi.localita.filter_in = lambda value : 
>> w2p_encrypt(value,key=chiave)
>> db.Nominativi.localita.filter_out = lambda value : 
>> w2p_decrypt(value,key=chiave)
>> db.Nominativi.cap.filter_in = lambda value : w2p_encrypt(value,key=chiave)
>> db.Nominativi.cap.filter_out = lambda value : 
>> w2p_decrypt(value,key=chiave)
>> db.Nominativi.codice_fiscale.filter_in = lambda value : 
>> w2p_encrypt(value,key=chiave)
>> db.Nominativi.codice_fiscale.filter_out = lambda value : 
>> w2p_decrypt(value,key=chiave)
>> db.Nominativi.padre.filter_in = lambda value : 
>> w2p_encrypt(value,key=chiave)
>> db.Nominativi.padre.filter_out = lambda value : 
>> w2p_decrypt(value,key=chiave)
>> db.Nominativi.madre.filter_in = lambda value : 
>> w2p_encrypt(value,key=chiave)
>> db.Nominativi.madre.filter_out = lambda value : 
>> w2p_decrypt(value,key=chiave)
>>
>> db.Nominativi.categoria.requires=IS_IN_DB(db,'Categorie_Nominativi.id','%(categoria)s',zero=None)
>> db.Nominativi.categoria.represent = lambda id,row: row.categoria
>> db.Nominativi.nome.represent = lambda nome,row: nome.title()
>>
>> db.Nominativi.inviato_da.widget=SQLFORM.widgets.autocomplete(request,db.Nominativi.nome)
>>
>> db.define_table('Relazioni',
>> Field('nominativo',db.Nominativi,required=True,requires=[IS_IN_DB(db, 
>> 'Nominativi.id')]),
>> Field('referente',db.Nominativi,required=True,requires=[IS_IN_DB(db, 
>> 'Nominativi.id')]),
>> Field('tipo_relazione',label='Tipo di relazione'))
>>
>> db.Relazioni.tipo_relazione.widget = lambda field,value: \
>>  SQLFORM.widgets.string.widget(field,value,_placeholder="Inserire il 
>> tipo di relazione")
>>
>> db.Relazioni.referente.widget=SQLFORM.widgets.autocomplete(request,db.Nominativi.nome,id_field=
>> db.Nominativi.id,limitby=(0,10), min_length=3)
>>
>>
>> db.define_table("Consulenze",
>> Field('dentista',db.Nominativi,required=True),
>> Field('data_inizio_collaborazione','date'),
>> Field('data_fine_collaborazione','date'),
>> Field('id_precedente','integer'),
>> format=lambda r:db.Nominativi(r.dentista)['nome'])
>> odontoiatri=db(db.Nominativi.categoria==2)
>>
>> db.Consulenze.dentista.requires=IS_IN_DB(odontoiatri,'Nominativi.id','%(nome)s')
>>
>> db.define_table("Pazienti_Consulenza",
>> Field('dentista',db.Consulenze, required=True),
>> Field('paziente',db.Nominativi,required=True, unique=True))
>>
>> everything worked fine when I've added the table.
>> I can't see what is wrong in the last table ...
>>
>> Thx
>>
>

-- 
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+u

[web2py] Re: foreign key constraint failed ???

[Ivan Gazzola]

I changed all fields in 'referece field' format but nothing changes :(
For the validatos IS_IN_DB(db,db.table.field,format) is mistaken and return 
errors, indeed the correct form is IS_IN_DB(db,table.field,format) ...

Regards

Ivan

Il giorno domenica 8 dicembre 2013 17:20:15 UTC+1, 黄祥 ha scritto:
>
> had you already try using :
> 'reference Nominativi' instead of db.Nominativi 
> and 
> requires is set to db.Nominativi.id instead of 'Nominativi.id'?
>
> e.g. 
> db.define_table('Relazioni',
> Field('nominativo', 'reference Nominativi', 
> required=True,requires=[IS_IN_DB(db, db.Nominativi.id)]),
> Field('referente', reference Nominativi', 
> required=True,requires=[IS_IN_DB(db, db.Nominativi.id)]),
> Field('tipo_relazione',label='Tipo di relazione'))
>
> best regards,
> stifan
>

-- 
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/groups/opt_out.

[web2py] Re: foreign key constraint failed ???

[黄祥]

had you already try using :
'reference Nominativi' instead of db.Nominativi 
and 
requires is set to db.Nominativi.id instead of 'Nominativi.id'?

e.g. 
db.define_table('Relazioni',
Field('nominativo', 'reference Nominativi', 
required=True,requires=[IS_IN_DB(db, db.Nominativi.id)]),
Field('referente', reference Nominativi', 
required=True,requires=[IS_IN_DB(db, db.Nominativi.id)]),
Field('tipo_relazione',label='Tipo di relazione'))

best regards,
stifan

-- 
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/groups/opt_out.

[web2py] Re: foreign key constraint failed ???

[Ivo]

though it may not be the preferred solution I resolved it by adding:
adapter_args=dict(foreign_keys=False) to db = 
DAL('sqlite://storage.sqlite') in db.py


On Sunday, December 8, 2013 3:16:36 PM UTC+1, Ivan Gazzola wrote:
>
> I can't resolve this ticket in my app. I try to insert in 
> Pazienti_Consulenza from appadmin but i've always this error:
> "
>  foreign key constraint failed
> "
>
> This is my model:
>
> db.define_table('Nominativi',
> Field('nome',notnull=True, represent=lambda nome:nome.title()),
> Field('categoria',db.Categorie_Nominativi,default='1'),
> Field('sesso',requires=IS_EMPTY_OR(IS_IN_SET(['maschio','femmina']))),
> Field('mail',requires=IS_EMPTY_OR(IS_EMAIL(error_message=T('Inserire 
> Una Mail Valida',
> Field('indirizzo'),
> Field('localita', label='Località'),
> Field('cap',requires=IS_EMPTY_OR(IS_LENGTH(5,5, 
> error_message=T('Inserire Un CAP valido',
> Field('provincia',requires=IS_EMPTY_OR(IS_IN_SET(Elenco_Province, 
> zero=T('Selezionare una Provincia',
> Field('codice_fiscale',length=20),
> Field('partita_iva',length=20),
> Field('data_di_nascita','date'),
> Field('professione'),
> Field('padre'),
> Field('madre'),
> Field('inviato_da'),
> Field('id_precendente','integer'),
> format=lambda 
> r:r.nome.title()+'-->'+db.Categorie_Nominativi(r.categoria)['categoria'])
>
> db.Nominativi.nome.filter_in = lambda value : w2p_encrypt(value,key=chiave)
> db.Nominativi.nome.filter_out = lambda value : 
> w2p_decrypt(value,key=chiave)
> db.Nominativi.mail.filter_in = lambda value : w2p_encrypt(value,key=chiave)
> db.Nominativi.mail.filter_out = lambda value : 
> w2p_decrypt(value,key=chiave)
> db.Nominativi.indirizzo.filter_in = lambda value : 
> w2p_encrypt(value,key=chiave)
> db.Nominativi.indirizzo.filter_out = lambda value : 
> w2p_decrypt(value,key=chiave)
> db.Nominativi.localita.filter_in = lambda value : 
> w2p_encrypt(value,key=chiave)
> db.Nominativi.localita.filter_out = lambda value : 
> w2p_decrypt(value,key=chiave)
> db.Nominativi.cap.filter_in = lambda value : w2p_encrypt(value,key=chiave)
> db.Nominativi.cap.filter_out = lambda value : w2p_decrypt(value,key=chiave)
> db.Nominativi.codice_fiscale.filter_in = lambda value : 
> w2p_encrypt(value,key=chiave)
> db.Nominativi.codice_fiscale.filter_out = lambda value : 
> w2p_decrypt(value,key=chiave)
> db.Nominativi.padre.filter_in = lambda value : 
> w2p_encrypt(value,key=chiave)
> db.Nominativi.padre.filter_out = lambda value : 
> w2p_decrypt(value,key=chiave)
> db.Nominativi.madre.filter_in = lambda value : 
> w2p_encrypt(value,key=chiave)
> db.Nominativi.madre.filter_out = lambda value : 
> w2p_decrypt(value,key=chiave)
>
> db.Nominativi.categoria.requires=IS_IN_DB(db,'Categorie_Nominativi.id','%(categoria)s',zero=None)
> db.Nominativi.categoria.represent = lambda id,row: row.categoria
> db.Nominativi.nome.represent = lambda nome,row: nome.title()
>
> db.Nominativi.inviato_da.widget=SQLFORM.widgets.autocomplete(request,db.Nominativi.nome)
>
> db.define_table('Relazioni',
> Field('nominativo',db.Nominativi,required=True,requires=[IS_IN_DB(db, 
> 'Nominativi.id')]),
> Field('referente',db.Nominativi,required=True,requires=[IS_IN_DB(db, 
> 'Nominativi.id')]),
> Field('tipo_relazione',label='Tipo di relazione'))
>
> db.Relazioni.tipo_relazione.widget = lambda field,value: \
>  SQLFORM.widgets.string.widget(field,value,_placeholder="Inserire il 
> tipo di relazione")
>
> db.Relazioni.referente.widget=SQLFORM.widgets.autocomplete(request,db.Nominativi.nome,id_field=
> db.Nominativi.id,limitby=(0,10), min_length=3)
>
>
> db.define_table("Consulenze",
> Field('dentista',db.Nominativi,required=True),
> Field('data_inizio_collaborazione','date'),
> Field('data_fine_collaborazione','date'),
> Field('id_precedente','integer'),
> format=lambda r:db.Nominativi(r.dentista)['nome'])
> odontoiatri=db(db.Nominativi.categoria==2)
>
> db.Consulenze.dentista.requires=IS_IN_DB(odontoiatri,'Nominativi.id','%(nome)s')
>
> db.define_table("Pazienti_Consulenza",
> Field('dentista',db.Consulenze, required=True),
> Field('paziente',db.Nominativi,required=True, unique=True))
>
> everything worked fine when I've added the table.
> I can't see what is wrong in the last table ...
>
> Thx
>

-- 
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/groups/opt_out.

[web2py] Re: "Foreign Key Constraint"

[Derek]

You'd need to find out which tables are referencing the particular table 
that you can't truncate, and truncate those tables first.

On Tuesday, October 15, 2013 12:41:31 PM UTC-7, Auden RovelleQuartz wrote:
>
> thanks for the response Derek,
>
> I am using PostgreSQL with web2py
>
> how would I do what you recommend regarding "setting to cascade"?
>
> On Tuesday, October 15, 2013 1:36:55 PM UTC-5, Derek wrote:
>>
>> Well, I don't see the error in the code, but if the error you are getting 
>> is 'foreign key constraint' then the problem is you are trying to truncate 
>> tables that are used as foreign keys for other tables. You generally have 
>> to truncate those tables that are referencing the table in question first 
>> (the order of operations is important) and then you can truncate those 
>> tables. This is also a side effect of having the foreign key referential 
>> actions set to 'no action' or 'restrict'. You'd want it to 'cascade' if you 
>> want to delete items in this manner, otherwise, you must reorder your 
>> operations to delete the leaf rows first.
>>
>> On Tuesday, October 15, 2013 10:45:57 AM UTC-7, Auden RovelleQuartz wrote:
>>>
>>>
>>> when I run this function,
>>>
>>> *def truncate_db_tables(the_db):*
>>> * for table_name in the_db.tables():*
>>> * the_db[table_name].truncate()*
>>> * the_db.commit()*
>>> * return()*
>>>
>>>
>>> I get the following error (traceback provided below) - does anyone 
>>> understand what this means?
>>>
>>> Traceback
>>>
>>> 1.
>>> 2.
>>> 3.
>>> 4.
>>> 5.
>>> 6.
>>> 7.
>>> 8.
>>> 9.
>>> 10.
>>> 11.
>>> 12.
>>> 13.
>>> 14.
>>> 15.
>>> 16.
>>> 17.
>>> 18.
>>> 19.
>>> 20.
>>> 21.
>>> 22.
>>> 23.
>>> 24.
>>> 25.
>>> 26.
>>> 27.
>>> 28.
>>> 29.
>>> 30.
>>> 31.
>>> 32.
>>> 33.
>>> 34.
>>> 35.
>>> 36.
>>> 37.
>>> 38.
>>> 39.
>>> 40.
>>> 41.
>>> 42.
>>> 43.
>>> 44.
>>> 45.
>>> 46.
>>>
>>> Traceback (most recent call last):
>>>   File "/home/www-data/web2py/gluon/restricted.py", line 212, in restricted
>>> exec ccode in environment
>>>   File 
>>> "/home/www-data/web2py/applications/omniavx/controllers/developer.py" 
>>> , 
>>> line 353, in 
>>>   File "/home/www-data/web2py/gluon/globals.py", line 194, in 
>>> self._caller = lambda f: f()
>>>   File 
>>> "/home/www-data/web2py/applications/omniavx/controllers/developer.py" 
>>> , 
>>> line 115, in testview5
>>> default_database_state_dev00()
>>>   File "/home/www-data/web2py/applications/omniavx/models/res_alpha.py" 
>>> , line 
>>> 40, in default_database_state_dev00
>>> default_database_state_a00()
>>>   File "/home/www-data/web2py/applications/omniavx/models/res_alpha.py" 
>>> , line 
>>> 27, in default_database_state_a00
>>> default_database_state_dev99()
>>>   File "/home/www-data/web2py/applications/omniavx/models/res_alpha.py" 
>>> , line 
>>> 46, in default_database_state_dev99
>>> truncate_db_tables(db)
>>>   File "/home/www-data/web2py/applications/omniavx/models/res_alpha.py" 
>>> , line 
>>> 2718, in truncate_db_tables
>>> the_db[table_name].truncate()
>>>   File "/home/www-data/web2py/gluon/dal.py", line 8648, in truncate
>>> return self._db._adapter.truncate(self, mode)
>>>   File "/home/www-data/web2py/gluon/dal.py", line 1425, in truncate
>>> self.execute(query)
>>>   File "/home/www-data/web2py/gluon/dal.py", line 1784, in execute
>>> return self.log_execute(*a, **b)
>>>   File "/home/www-data/web2py/gluon/dal.py", line 1778, in log_execute
>>> ret = self.cursor.execute(command, *a[1:], **b)
>>>   File "/home/www-data/web2py/gluon/contrib/pg8000/dbapi.py", line 246, in 
>>> _fn
>>> return fn(self, *args, **kwargs)
>>>   File "/home/www-data/web2py/gluon/contrib/pg8000/dbapi.py", line 317, in 
>>> execute
>>> self._execute(operation, args)
>>>   File "/home/www-data/web2py/gluon/contrib/pg8000/dbapi.py", line 322, in 
>>> _execute
>>> self.cursor.execute(new_query, *new_args)
>>>   File "/home/www-data/web2py/gluon/contrib/pg8000/interface.py", line 399, 
>>> in execute
>>> self._stmt.execute(*args, **kwargs)
>>>   File "/home/www-data/web2py/gluon/contrib/pg8000/interface.py", line 169, 
>>> in execute
>>> self._row_desc, cmd = self.c.bind(self._portal_name, 
>>> self._statement_name, args, self._parse_row_desc, kwargs.get("stream"))
>>>   File "/home/www-data/web2py/gluon/contrib/pg8000/protocol.py", line 943, 
>>> in _fn
>>> return fn(self, *args, **kwargs)
>>>   File "/home/www-data/web2py/gluon/contrib/pg8000/protocol.py", line 1142, 
>>> in bind
>>> return reader.handle_messages()
>>>   File "/home/www-data/web2p

[web2py] Re: "Foreign Key Constraint"

[Niphlod]

well, in sql-ese truncate is not the same as delete. 
Putting in simple words, any backend doesn't enable you to truncate a table 
if that "truncation" leads to other table records's being deleted (and 
that's your case with a FK).
The main difference between truncate and delete (the faster execution of 
truncate is highlighted by this corner-case) is that by default truncation 
drops whatever is in the table without taking care of possible 
side-effects. delete instead goes row by row eventually considering side 
effects.
This leads the backend to mark in advance any table with a FK as 
non-truncable, because it has BY DEFAULT a side-effect: pruning all 
referenced records.
>
>


-- 
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/groups/opt_out.

[web2py] Re: "Foreign Key Constraint"

[Auden RovelleQuartz]

the rest of the error (sorry):

ProgrammingError: ('ERROR', '0A000', 'cannot truncate a table referenced in a 
foreign key constraint')


So what do I need to do (web2py syntax) to avoid this error?



On Tuesday, October 15, 2013 2:41:31 PM UTC-5, Auden RovelleQuartz wrote:
>
> thanks for the response Derek,
>
> I am using PostgreSQL with web2py
>
> how would I do what you recommend regarding "setting to cascade"?
>
> On Tuesday, October 15, 2013 1:36:55 PM UTC-5, Derek wrote:
>>
>> Well, I don't see the error in the code, but if the error you are getting 
>> is 'foreign key constraint' then the problem is you are trying to truncate 
>> tables that are used as foreign keys for other tables. You generally have 
>> to truncate those tables that are referencing the table in question first 
>> (the order of operations is important) and then you can truncate those 
>> tables. This is also a side effect of having the foreign key referential 
>> actions set to 'no action' or 'restrict'. You'd want it to 'cascade' if you 
>> want to delete items in this manner, otherwise, you must reorder your 
>> operations to delete the leaf rows first.
>>
>> On Tuesday, October 15, 2013 10:45:57 AM UTC-7, Auden RovelleQuartz wrote:
>>>
>>>
>>> when I run this function,
>>>
>>> *def truncate_db_tables(the_db):*
>>> * for table_name in the_db.tables():*
>>> * the_db[table_name].truncate()*
>>> * the_db.commit()*
>>> * return()*
>>>
>>>
>>> I get the following error (traceback provided below) - does anyone 
>>> understand what this means?
>>>
>>> Traceback
>>>
>>> 1.
>>> 2.
>>> 3.
>>> 4.
>>> 5.
>>> 6.
>>> 7.
>>> 8.
>>> 9.
>>> 10.
>>> 11.
>>> 12.
>>> 13.
>>> 14.
>>> 15.
>>> 16.
>>> 17.
>>> 18.
>>> 19.
>>> 20.
>>> 21.
>>> 22.
>>> 23.
>>> 24.
>>> 25.
>>> 26.
>>> 27.
>>> 28.
>>> 29.
>>> 30.
>>> 31.
>>> 32.
>>> 33.
>>> 34.
>>> 35.
>>> 36.
>>> 37.
>>> 38.
>>> 39.
>>> 40.
>>> 41.
>>> 42.
>>> 43.
>>> 44.
>>> 45.
>>> 46.
>>>
>>> Traceback (most recent call last):
>>>   File "/home/www-data/web2py/gluon/restricted.py", line 212, in restricted
>>> exec ccode in environment
>>>   File 
>>> "/home/www-data/web2py/applications/omniavx/controllers/developer.py" 
>>> , 
>>> line 353, in 
>>>   File "/home/www-data/web2py/gluon/globals.py", line 194, in 
>>> self._caller = lambda f: f()
>>>   File 
>>> "/home/www-data/web2py/applications/omniavx/controllers/developer.py" 
>>> , 
>>> line 115, in testview5
>>> default_database_state_dev00()
>>>   File "/home/www-data/web2py/applications/omniavx/models/res_alpha.py" 
>>> , line 
>>> 40, in default_database_state_dev00
>>> default_database_state_a00()
>>>   File "/home/www-data/web2py/applications/omniavx/models/res_alpha.py" 
>>> , line 
>>> 27, in default_database_state_a00
>>> default_database_state_dev99()
>>>   File "/home/www-data/web2py/applications/omniavx/models/res_alpha.py" 
>>> , line 
>>> 46, in default_database_state_dev99
>>> truncate_db_tables(db)
>>>   File "/home/www-data/web2py/applications/omniavx/models/res_alpha.py" 
>>> , line 
>>> 2718, in truncate_db_tables
>>> the_db[table_name].truncate()
>>>   File "/home/www-data/web2py/gluon/dal.py", line 8648, in truncate
>>> return self._db._adapter.truncate(self, mode)
>>>   File "/home/www-data/web2py/gluon/dal.py", line 1425, in truncate
>>> self.execute(query)
>>>   File "/home/www-data/web2py/gluon/dal.py", line 1784, in execute
>>> return self.log_execute(*a, **b)
>>>   File "/home/www-data/web2py/gluon/dal.py", line 1778, in log_execute
>>> ret = self.cursor.execute(command, *a[1:], **b)
>>>   File "/home/www-data/web2py/gluon/contrib/pg8000/dbapi.py", line 246, in 
>>> _fn
>>> return fn(self, *args, **kwargs)
>>>   File "/home/www-data/web2py/gluon/contrib/pg8000/dbapi.py", line 317, in 
>>> execute
>>> self._execute(operation, args)
>>>   File "/home/www-data/web2py/gluon/contrib/pg8000/dbapi.py", line 322, in 
>>> _execute
>>> self.cursor.execute(new_query, *new_args)
>>>   File "/home/www-data/web2py/gluon/contrib/pg8000/interface.py", line 399, 
>>> in execute
>>> self._stmt.execute(*args, **kwargs)
>>>   File "/home/www-data/web2py/gluon/contrib/pg8000/interface.py", line 169, 
>>> in execute
>>> self._row_desc, cmd = self.c.bind(self._portal_name, 
>>> self._statement_name, args, self._parse_row_desc, kwargs.get("stream"))
>>>   File "/home/www-data/web2py/gluon/contrib/pg8000/protocol.py", line 943, 
>>> in _fn
>>> return fn(self, *args, **kwargs)
>>>   File "/home/www-data/web2py/gluon/contrib/pg8000/protocol.py", line 1142, 
>>> in bind
>>> 

[web2py] Re: "Foreign Key Constraint"

[Auden RovelleQuartz]

thanks for the response Derek,

I am using PostgreSQL with web2py

how would I do what you recommend regarding "setting to cascade"?

On Tuesday, October 15, 2013 1:36:55 PM UTC-5, Derek wrote:
>
> Well, I don't see the error in the code, but if the error you are getting 
> is 'foreign key constraint' then the problem is you are trying to truncate 
> tables that are used as foreign keys for other tables. You generally have 
> to truncate those tables that are referencing the table in question first 
> (the order of operations is important) and then you can truncate those 
> tables. This is also a side effect of having the foreign key referential 
> actions set to 'no action' or 'restrict'. You'd want it to 'cascade' if you 
> want to delete items in this manner, otherwise, you must reorder your 
> operations to delete the leaf rows first.
>
> On Tuesday, October 15, 2013 10:45:57 AM UTC-7, Auden RovelleQuartz wrote:
>>
>>
>> when I run this function,
>>
>> *def truncate_db_tables(the_db):*
>> * for table_name in the_db.tables():*
>> * the_db[table_name].truncate()*
>> * the_db.commit()*
>> * return()*
>>
>>
>> I get the following error (traceback provided below) - does anyone 
>> understand what this means?
>>
>> Traceback
>>
>> 1.
>> 2.
>> 3.
>> 4.
>> 5.
>> 6.
>> 7.
>> 8.
>> 9.
>> 10.
>> 11.
>> 12.
>> 13.
>> 14.
>> 15.
>> 16.
>> 17.
>> 18.
>> 19.
>> 20.
>> 21.
>> 22.
>> 23.
>> 24.
>> 25.
>> 26.
>> 27.
>> 28.
>> 29.
>> 30.
>> 31.
>> 32.
>> 33.
>> 34.
>> 35.
>> 36.
>> 37.
>> 38.
>> 39.
>> 40.
>> 41.
>> 42.
>> 43.
>> 44.
>> 45.
>> 46.
>>
>> Traceback (most recent call last):
>>   File "/home/www-data/web2py/gluon/restricted.py", line 212, in restricted
>> exec ccode in environment
>>   File "/home/www-data/web2py/applications/omniavx/controllers/developer.py" 
>> , 
>> line 353, in 
>>   File "/home/www-data/web2py/gluon/globals.py", line 194, in 
>> self._caller = lambda f: f()
>>   File "/home/www-data/web2py/applications/omniavx/controllers/developer.py" 
>> , 
>> line 115, in testview5
>> default_database_state_dev00()
>>   File "/home/www-data/web2py/applications/omniavx/models/res_alpha.py" 
>> , line 
>> 40, in default_database_state_dev00
>> default_database_state_a00()
>>   File "/home/www-data/web2py/applications/omniavx/models/res_alpha.py" 
>> , line 
>> 27, in default_database_state_a00
>> default_database_state_dev99()
>>   File "/home/www-data/web2py/applications/omniavx/models/res_alpha.py" 
>> , line 
>> 46, in default_database_state_dev99
>> truncate_db_tables(db)
>>   File "/home/www-data/web2py/applications/omniavx/models/res_alpha.py" 
>> , line 
>> 2718, in truncate_db_tables
>> the_db[table_name].truncate()
>>   File "/home/www-data/web2py/gluon/dal.py", line 8648, in truncate
>> return self._db._adapter.truncate(self, mode)
>>   File "/home/www-data/web2py/gluon/dal.py", line 1425, in truncate
>> self.execute(query)
>>   File "/home/www-data/web2py/gluon/dal.py", line 1784, in execute
>> return self.log_execute(*a, **b)
>>   File "/home/www-data/web2py/gluon/dal.py", line 1778, in log_execute
>> ret = self.cursor.execute(command, *a[1:], **b)
>>   File "/home/www-data/web2py/gluon/contrib/pg8000/dbapi.py", line 246, in 
>> _fn
>> return fn(self, *args, **kwargs)
>>   File "/home/www-data/web2py/gluon/contrib/pg8000/dbapi.py", line 317, in 
>> execute
>> self._execute(operation, args)
>>   File "/home/www-data/web2py/gluon/contrib/pg8000/dbapi.py", line 322, in 
>> _execute
>> self.cursor.execute(new_query, *new_args)
>>   File "/home/www-data/web2py/gluon/contrib/pg8000/interface.py", line 399, 
>> in execute
>> self._stmt.execute(*args, **kwargs)
>>   File "/home/www-data/web2py/gluon/contrib/pg8000/interface.py", line 169, 
>> in execute
>> self._row_desc, cmd = self.c.bind(self._portal_name, 
>> self._statement_name, args, self._parse_row_desc, kwargs.get("stream"))
>>   File "/home/www-data/web2py/gluon/contrib/pg8000/protocol.py", line 943, 
>> in _fn
>> return fn(self, *args, **kwargs)
>>   File "/home/www-data/web2py/gluon/contrib/pg8000/protocol.py", line 1142, 
>> in bind
>> return reader.handle_messages()
>>   File "/home/www-data/web2py/gluon/contrib/pg8000/protocol.py", line 911, 
>> in handle_messages
>> retval = handler(msg, *args, **kwargs)
>>   File "/home/www-data/web2py/gluon/contrib/pg8000/protocol.py", line 1181, 
>> in _bind_nodata
>> reader.handle_messages()
>>   File "/home/www-data/web2py/gluon/contrib/pg8000/protocol.py", line 916, 
>> in handle_messages
>> raise exc

[web2py] Re: "Foreign Key Constraint"

[Derek]

Well, I don't see the error in the code, but if the error you are getting 
is 'foreign key constraint' then the problem is you are trying to truncate 
tables that are used as foreign keys for other tables. You generally have 
to truncate those tables that are referencing the table in question first 
(the order of operations is important) and then you can truncate those 
tables. This is also a side effect of having the foreign key referential 
actions set to 'no action' or 'restrict'. You'd want it to 'cascade' if you 
want to delete items in this manner, otherwise, you must reorder your 
operations to delete the leaf rows first.

On Tuesday, October 15, 2013 10:45:57 AM UTC-7, Auden RovelleQuartz wrote:
>
>
> when I run this function,
>
> *def truncate_db_tables(the_db):*
> * for table_name in the_db.tables():*
> * the_db[table_name].truncate()*
> * the_db.commit()*
> * return()*
>
>
> I get the following error (traceback provided below) - does anyone 
> understand what this means?
>
> Traceback
>
> 1.
> 2.
> 3.
> 4.
> 5.
> 6.
> 7.
> 8.
> 9.
> 10.
> 11.
> 12.
> 13.
> 14.
> 15.
> 16.
> 17.
> 18.
> 19.
> 20.
> 21.
> 22.
> 23.
> 24.
> 25.
> 26.
> 27.
> 28.
> 29.
> 30.
> 31.
> 32.
> 33.
> 34.
> 35.
> 36.
> 37.
> 38.
> 39.
> 40.
> 41.
> 42.
> 43.
> 44.
> 45.
> 46.
>
> Traceback (most recent call last):
>   File "/home/www-data/web2py/gluon/restricted.py", line 212, in restricted
> exec ccode in environment
>   File "/home/www-data/web2py/applications/omniavx/controllers/developer.py" 
> , 
> line 353, in 
>   File "/home/www-data/web2py/gluon/globals.py", line 194, in 
> self._caller = lambda f: f()
>   File "/home/www-data/web2py/applications/omniavx/controllers/developer.py" 
> , 
> line 115, in testview5
> default_database_state_dev00()
>   File "/home/www-data/web2py/applications/omniavx/models/res_alpha.py" 
> , line 
> 40, in default_database_state_dev00
> default_database_state_a00()
>   File "/home/www-data/web2py/applications/omniavx/models/res_alpha.py" 
> , line 
> 27, in default_database_state_a00
> default_database_state_dev99()
>   File "/home/www-data/web2py/applications/omniavx/models/res_alpha.py" 
> , line 
> 46, in default_database_state_dev99
> truncate_db_tables(db)
>   File "/home/www-data/web2py/applications/omniavx/models/res_alpha.py" 
> , line 
> 2718, in truncate_db_tables
> the_db[table_name].truncate()
>   File "/home/www-data/web2py/gluon/dal.py", line 8648, in truncate
> return self._db._adapter.truncate(self, mode)
>   File "/home/www-data/web2py/gluon/dal.py", line 1425, in truncate
> self.execute(query)
>   File "/home/www-data/web2py/gluon/dal.py", line 1784, in execute
> return self.log_execute(*a, **b)
>   File "/home/www-data/web2py/gluon/dal.py", line 1778, in log_execute
> ret = self.cursor.execute(command, *a[1:], **b)
>   File "/home/www-data/web2py/gluon/contrib/pg8000/dbapi.py", line 246, in _fn
> return fn(self, *args, **kwargs)
>   File "/home/www-data/web2py/gluon/contrib/pg8000/dbapi.py", line 317, in 
> execute
> self._execute(operation, args)
>   File "/home/www-data/web2py/gluon/contrib/pg8000/dbapi.py", line 322, in 
> _execute
> self.cursor.execute(new_query, *new_args)
>   File "/home/www-data/web2py/gluon/contrib/pg8000/interface.py", line 399, 
> in execute
> self._stmt.execute(*args, **kwargs)
>   File "/home/www-data/web2py/gluon/contrib/pg8000/interface.py", line 169, 
> in execute
> self._row_desc, cmd = self.c.bind(self._portal_name, 
> self._statement_name, args, self._parse_row_desc, kwargs.get("stream"))
>   File "/home/www-data/web2py/gluon/contrib/pg8000/protocol.py", line 943, in 
> _fn
> return fn(self, *args, **kwargs)
>   File "/home/www-data/web2py/gluon/contrib/pg8000/protocol.py", line 1142, 
> in bind
> return reader.handle_messages()
>   File "/home/www-data/web2py/gluon/contrib/pg8000/protocol.py", line 911, in 
> handle_messages
> retval = handler(msg, *args, **kwargs)
>   File "/home/www-data/web2py/gluon/contrib/pg8000/protocol.py", line 1181, 
> in _bind_nodata
> reader.handle_messages()
>   File "/home/www-data/web2py/gluon/contrib/pg8000/protocol.py", line 916, in 
> handle_messages
> raise exc
> P
>
>

-- 
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 em

[web2py] Re: Foreign Key not showing up in form (One table only)

[Massimo Di Pierro]

db.define_table('orders',
...
format = '%(id)s'
)

tables need a format for the record else the automatic validators for the 
dropdown cannot be created.

On Saturday, 1 September 2012 00:54:52 UTC-5, Kevin C wrote:
>
> We're having a strange issue here. We have defined around 20 tables 
> already with foreign keys everywhere and working fine.  Today we created 
> two additional tables but the foreign key dropdown isn't working for one of 
> them. (This is just using the database manager through appadmin)
>
> Tables are:
> db.define_table('orders',
> Field('stores_id', db.stores),
> Field('customer_id', db.auth_user),
> Field('status', requires=IS_IN_SET(['New', 'Paid', 
> 'In-Progress', 'Shipped', 'Cancelled'])),
> Field('subtotal', 'double'),
> Field('shipping', 'double'),
> Field('discount', 'double'),
> Field('order_time', 'datetime')
> )
>
> db.define_table('order_details',
>  Field('orders_id', db.orders),
>  Field('product_name'),
>  Field('product_options'),
>  Field('quantity', 'integer'),
>  Field('price', 'double')
>  )
>
> When adding a new order_details item, the orders_id field is a text input 
> instead of a foreign key dropdown.  The foreign key was created 
> automatically in the database, but it's not being reflected in the form. 
>  This isn't a huge problem since this won't be something you use a form to 
> administer, but it's still worrying that something so key appears to be 
> broken for this table only. 
>
> Did I make a typo somewhere that I'm missing?
>
>
>

-- 

[web2py] Re: Foreign Key fields as null?

[Mandar Vaze]

I too am getting similar error (am using sqlform.grid, not smartgrid)
I've posted a query here 
: https://groups.google.com/d/msg/web2py/SxTKewH-nS8/G5iT8ycUAKgJ
Waiting for feedback

Thanks,
-Mandar

On Saturday, July 21, 2012 1:17:33 AM UTC+5:30, joe wrote:
>
> That hasn't worked for me.  When I try, I get the error: 'exceptions.KeyError'> 'name'
>
> It points to the smartgrid I made, with this code:  grid = 
> SQLFORM
> .smartgrid(db.dog)
>
> Thanks!
> -Joe Peacock
>
>
> On Tuesday, July 17, 2012 6:22:53 PM UTC-5, Anthony wrote:
>>
>> You should probably make it a reference field. By default, if you don't 
>> specify any "requires", you'll automatically get an IS_IN_DB validator as 
>> well as a "represent" attribute that displays whatever is specifying by the 
>> "format" attribute of the referenced table. However, if you specify your 
>> own validator(s), then you don't get any default "represent" -- so just add 
>> your own explicit represent:
>>
>> db.define_table('dog',
>> Field ('name'), 
>> Field ('owner', db.
>> person, 
>> requires=IS_EMPTY_OR
>> (IS_IN_DB (db, '
>> person.id', '%(name)')),
>>   represent=lambda id, row: db.person(id).name),
>> format = '%(name)s')
>>
>> Anthony
>>
>> On Tuesday, July 17, 2012 10:26:52 AM UTC-4, joe wrote:
>>>
>>> Is there any way to make a field in a form referencing a foreign key 
>>> null, while storing a foreign key as a foreign key, but making dropdowns 
>>> corrospond to the format.  Here is my example:
>>>
>>> Model:
>>>
>>> db = DAL 
>>> ('sqlite://storage.sqlite')
>>>
>>> db.define_table('person',
>>> Field ('name'),
>>> Field ('email'),
>>> format = '%(name)s')
>>> 
>>> db.define_table('dog',
>>> Field ('name'), 
>>> Field ('owner', 
>>> requires = IS_EMPTY_OR 
>>> (IS_IN_DB 
>>> (db,db.person))), #I 
>>> have also tried default=None, and required = False, no difference
>>> format = '%(name)s')
>>>
>>>
>>> Controller:
>>>
>>> *from gluon.tools import Crud
>>> crud = Crud(db)
>>>
>>> def index():
>>> form = SQLFORM(db.person)
>>> if form.process().accepted:
>>> response.flash = 'success'
>>> return dict(form=form)
>>> 
>>> def add_dog():
>>> form = SQLFORM(db.dog)
>>> if form.process().accepted:
>>> response.flash = 'success'
>>> return dict(form=form)
>>>
>>> def view():
>>> grid = SQLFORM.smartgrid(db.dog)
>>> grid2 = crud.select(db.person)
>>> return dict(grid=grid, grid2=grid2)
>>> *
>>>
>>> *
>>> *
>>>
>>> When I have the above code, a foreign key will be stored in the database, 
>>> but all views of that key will be rendered as the numerical value of the 
>>> foreign key, and not the name.  In the table definition, if I just have 
>>> *db.person* instead of requires = *IS_EMPTY_OR*(*IS_IN_DB*(db,db.person)), 
>>> It requires the field.  
>>>
>>>
>>> Are there any workarounds?
>>>
>>> -Joe Peacock
>>>
>>>

-- 

[web2py] Re: Foreign Key fields as null?

[joe]

The key error was because it was '%(name)' not '%(name)s'.  Everything 
works now! Thanks!

On Friday, July 20, 2012 3:37:11 PM UTC-5, Anthony wrote:
>
> Actually, I would think that would have caused an AttributeError (trying 
> to access an attribute of a NoneType object). Not sure where the KeyError 
> is arising.
>
> Anthony
>
> On Friday, July 20, 2012 4:31:52 PM UTC-4, Anthony wrote:
>>
>> Oh, right, since you allow empty values, you'll need to test for that 
>> before doing the query:
>>
>> represent=lambda id, row: db.person(id).name if id else 'Nobody owns 
>> this dog'
>>
>> Anthony
>>
>> On Friday, July 20, 2012 3:47:33 PM UTC-4, joe wrote:
>>>
>>> That hasn't worked for me.  When I try, I get the error:>> 'exceptions.KeyError'> 'name'
>>>
>>> It points to the smartgrid I made, with this code:  grid = 
>>> SQLFORM
>>> .smartgrid(db.dog)
>>>
>>> Thanks!
>>> -Joe Peacock
>>>
>>>
>>> On Tuesday, July 17, 2012 6:22:53 PM UTC-5, Anthony wrote:

 You should probably make it a reference field. By default, if you don't 
 specify any "requires", you'll automatically get an IS_IN_DB validator as 
 well as a "represent" attribute that displays whatever is specifying by 
 the 
 "format" attribute of the referenced table. However, if you specify your 
 own validator(s), then you don't get any default "represent" -- so just 
 add 
 your own explicit represent:

 db.define_table('dog',
 Field ('name'), 
 Field ('owner',db
 .person, 
 requires=IS_EMPTY_OR
 (IS_IN_DB (db, '
 person.id', '%(name)')),
   represent=lambda id, row: db.person(id).name),
 format = '%(name)s')

 Anthony

 On Tuesday, July 17, 2012 10:26:52 AM UTC-4, joe wrote:
>
> Is there any way to make a field in a form referencing a foreign key 
> null, while storing a foreign key as a foreign key, but making dropdowns 
> corrospond to the format.  Here is my example:
>
> Model:
>
> db = DAL 
> ('sqlite://storage.sqlite')
>
> db.define_table('person',
> Field ('name'),
> Field ('email'),
> format = '%(name)s')
> 
> db.define_table('dog',
> Field ('name'), 
> Field ('owner', 
> requires = IS_EMPTY_OR 
> (IS_IN_DB 
> (db,db.person))), #I 
> have also tried default=None, and required = False, no difference
> format = '%(name)s')
>
>
> Controller:
>
> *from gluon.tools import Crud
> crud = Crud(db)
>
> def index():
> form = SQLFORM(db.person)
> if form.process().accepted:
> response.flash = 'success'
> return dict(form=form)
> 
> def add_dog():
> form = SQLFORM(db.dog)
> if form.process().accepted:
> response.flash = 'success'
> return dict(form=form)
>
> def view():
> grid = SQLFORM.smartgrid(db.dog)
> grid2 = crud.select(db.person)
> return dict(grid=grid, grid2=grid2)
> *
>
> *
> *
>
> When I have the above code, a foreign key will be stored in the database, 
> but all views of that key will be rendered as the numerical value of the 
> foreign key, and not the name.  In the table definition, if I just have 
> *db.person* instead of requires = 
> *IS_EMPTY_OR*(*IS_IN_DB*(db,db.person)), It requires the field.  
>
>
> Are there any workarounds?
>
> -Joe Peacock
>
>

-- 

[web2py] Re: Foreign Key fields as null?

[Anthony]

Actually, I would think that would have caused an AttributeError (trying to 
access an attribute of a NoneType object). Not sure where the KeyError is 
arising.

Anthony

On Friday, July 20, 2012 4:31:52 PM UTC-4, Anthony wrote:
>
> Oh, right, since you allow empty values, you'll need to test for that 
> before doing the query:
>
> represent=lambda id, row: db.person(id).name if id else 'Nobody owns this 
> dog'
>
> Anthony
>
> On Friday, July 20, 2012 3:47:33 PM UTC-4, joe wrote:
>>
>> That hasn't worked for me.  When I try, I get the error:> 'exceptions.KeyError'> 'name'
>>
>> It points to the smartgrid I made, with this code:  grid = 
>> SQLFORM
>> .smartgrid(db.dog)
>>
>> Thanks!
>> -Joe Peacock
>>
>>
>> On Tuesday, July 17, 2012 6:22:53 PM UTC-5, Anthony wrote:
>>>
>>> You should probably make it a reference field. By default, if you don't 
>>> specify any "requires", you'll automatically get an IS_IN_DB validator as 
>>> well as a "represent" attribute that displays whatever is specifying by the 
>>> "format" attribute of the referenced table. However, if you specify your 
>>> own validator(s), then you don't get any default "represent" -- so just add 
>>> your own explicit represent:
>>>
>>> db.define_table('dog',
>>> Field ('name'), 
>>> Field ('owner', db
>>> .person, 
>>> requires=IS_EMPTY_OR
>>> (IS_IN_DB (db, '
>>> person.id', '%(name)')),
>>>   represent=lambda id, row: db.person(id).name),
>>> format = '%(name)s')
>>>
>>> Anthony
>>>
>>> On Tuesday, July 17, 2012 10:26:52 AM UTC-4, joe wrote:

 Is there any way to make a field in a form referencing a foreign key 
 null, while storing a foreign key as a foreign key, but making dropdowns 
 corrospond to the format.  Here is my example:

 Model:

 db = DAL 
 ('sqlite://storage.sqlite')

 db.define_table('person',
 Field ('name'),
 Field ('email'),
 format = '%(name)s')
 
 db.define_table('dog',
 Field ('name'), 
 Field ('owner', 
 requires = IS_EMPTY_OR 
 (IS_IN_DB 
 (db,db.person))), #I 
 have also tried default=None, and required = False, no difference
 format = '%(name)s')


 Controller:

 *from gluon.tools import Crud
 crud = Crud(db)

 def index():
 form = SQLFORM(db.person)
 if form.process().accepted:
 response.flash = 'success'
 return dict(form=form)
 
 def add_dog():
 form = SQLFORM(db.dog)
 if form.process().accepted:
 response.flash = 'success'
 return dict(form=form)

 def view():
 grid = SQLFORM.smartgrid(db.dog)
 grid2 = crud.select(db.person)
 return dict(grid=grid, grid2=grid2)
 *

 *
 *

 When I have the above code, a foreign key will be stored in the database, 
 but all views of that key will be rendered as the numerical value of the 
 foreign key, and not the name.  In the table definition, if I just have 
 *db.person* instead of requires = *IS_EMPTY_OR*(*IS_IN_DB*(db,db.person)), 
 It requires the field.  


 Are there any workarounds?

 -Joe Peacock



-- 

[web2py] Re: Foreign Key fields as null?

[Anthony]

Oh, right, since you allow empty values, you'll need to test for that 
before doing the query:

represent=lambda id, row: db.person(id).name if id else 'Nobody owns this 
dog'

Anthony

On Friday, July 20, 2012 3:47:33 PM UTC-4, joe wrote:
>
> That hasn't worked for me.  When I try, I get the error: 'exceptions.KeyError'> 'name'
>
> It points to the smartgrid I made, with this code:  grid = 
> SQLFORM
> .smartgrid(db.dog)
>
> Thanks!
> -Joe Peacock
>
>
> On Tuesday, July 17, 2012 6:22:53 PM UTC-5, Anthony wrote:
>>
>> You should probably make it a reference field. By default, if you don't 
>> specify any "requires", you'll automatically get an IS_IN_DB validator as 
>> well as a "represent" attribute that displays whatever is specifying by the 
>> "format" attribute of the referenced table. However, if you specify your 
>> own validator(s), then you don't get any default "represent" -- so just add 
>> your own explicit represent:
>>
>> db.define_table('dog',
>> Field ('name'), 
>> Field ('owner', db.
>> person, 
>> requires=IS_EMPTY_OR
>> (IS_IN_DB (db, '
>> person.id', '%(name)')),
>>   represent=lambda id, row: db.person(id).name),
>> format = '%(name)s')
>>
>> Anthony
>>
>> On Tuesday, July 17, 2012 10:26:52 AM UTC-4, joe wrote:
>>>
>>> Is there any way to make a field in a form referencing a foreign key 
>>> null, while storing a foreign key as a foreign key, but making dropdowns 
>>> corrospond to the format.  Here is my example:
>>>
>>> Model:
>>>
>>> db = DAL 
>>> ('sqlite://storage.sqlite')
>>>
>>> db.define_table('person',
>>> Field ('name'),
>>> Field ('email'),
>>> format = '%(name)s')
>>> 
>>> db.define_table('dog',
>>> Field ('name'), 
>>> Field ('owner', 
>>> requires = IS_EMPTY_OR 
>>> (IS_IN_DB 
>>> (db,db.person))), #I 
>>> have also tried default=None, and required = False, no difference
>>> format = '%(name)s')
>>>
>>>
>>> Controller:
>>>
>>> *from gluon.tools import Crud
>>> crud = Crud(db)
>>>
>>> def index():
>>> form = SQLFORM(db.person)
>>> if form.process().accepted:
>>> response.flash = 'success'
>>> return dict(form=form)
>>> 
>>> def add_dog():
>>> form = SQLFORM(db.dog)
>>> if form.process().accepted:
>>> response.flash = 'success'
>>> return dict(form=form)
>>>
>>> def view():
>>> grid = SQLFORM.smartgrid(db.dog)
>>> grid2 = crud.select(db.person)
>>> return dict(grid=grid, grid2=grid2)
>>> *
>>>
>>> *
>>> *
>>>
>>> When I have the above code, a foreign key will be stored in the database, 
>>> but all views of that key will be rendered as the numerical value of the 
>>> foreign key, and not the name.  In the table definition, if I just have 
>>> *db.person* instead of requires = *IS_EMPTY_OR*(*IS_IN_DB*(db,db.person)), 
>>> It requires the field.  
>>>
>>>
>>> Are there any workarounds?
>>>
>>> -Joe Peacock
>>>
>>>

-- 

[web2py] Re: Foreign Key fields as null?

[joe]

That hasn't worked for me.  When I try, I get the error: 'name'

It points to the smartgrid I made, with this code:  grid = 
SQLFORM
.smartgrid(db.dog)

Thanks!
-Joe Peacock


On Tuesday, July 17, 2012 6:22:53 PM UTC-5, Anthony wrote:
>
> You should probably make it a reference field. By default, if you don't 
> specify any "requires", you'll automatically get an IS_IN_DB validator as 
> well as a "represent" attribute that displays whatever is specifying by the 
> "format" attribute of the referenced table. However, if you specify your 
> own validator(s), then you don't get any default "represent" -- so just add 
> your own explicit represent:
>
> db.define_table('dog',
> Field ('name'), 
> Field ('owner', db.
> person, 
> requires=IS_EMPTY_OR
> (IS_IN_DB (db, '
> person.id', '%(name)')),
>   represent=lambda id, row: db.person(id).name),
> format = '%(name)s')
>
> Anthony
>
> On Tuesday, July 17, 2012 10:26:52 AM UTC-4, joe wrote:
>>
>> Is there any way to make a field in a form referencing a foreign key 
>> null, while storing a foreign key as a foreign key, but making dropdowns 
>> corrospond to the format.  Here is my example:
>>
>> Model:
>>
>> db = DAL 
>> ('sqlite://storage.sqlite')
>>
>> db.define_table('person',
>> Field ('name'),
>> Field ('email'),
>> format = '%(name)s')
>> 
>> db.define_table('dog',
>> Field ('name'), 
>> Field ('owner', 
>> requires = IS_EMPTY_OR 
>> (IS_IN_DB 
>> (db,db.person))), #I 
>> have also tried default=None, and required = False, no difference
>> format = '%(name)s')
>>
>>
>> Controller:
>>
>> *from gluon.tools import Crud
>> crud = Crud(db)
>>
>> def index():
>> form = SQLFORM(db.person)
>> if form.process().accepted:
>> response.flash = 'success'
>> return dict(form=form)
>> 
>> def add_dog():
>> form = SQLFORM(db.dog)
>> if form.process().accepted:
>> response.flash = 'success'
>> return dict(form=form)
>>
>> def view():
>> grid = SQLFORM.smartgrid(db.dog)
>> grid2 = crud.select(db.person)
>> return dict(grid=grid, grid2=grid2)
>> *
>>
>> *
>> *
>>
>> When I have the above code, a foreign key will be stored in the database, 
>> but all views of that key will be rendered as the numerical value of the 
>> foreign key, and not the name.  In the table definition, if I just have 
>> *db.person* instead of requires = *IS_EMPTY_OR*(*IS_IN_DB*(db,db.person)), 
>> It requires the field.  
>>
>>
>> Are there any workarounds?
>>
>> -Joe Peacock
>>
>>

-- 

[web2py] Re: Foreign Key fields as null?

[Anthony]

You should probably make it a reference field. By default, if you don't 
specify any "requires", you'll automatically get an IS_IN_DB validator as 
well as a "represent" attribute that displays whatever is specifying by the 
"format" attribute of the referenced table. However, if you specify your 
own validator(s), then you don't get any default "represent" -- so just add 
your own explicit represent:

db.define_table('dog',
Field ('name'), 
Field ('owner', db.
person, 
requires=IS_EMPTY_OR
(IS_IN_DB (db, 
'person.id', '%(name)')),
  represent=lambda id, row: db.person(id).name),
format = '%(name)s')

Anthony

On Tuesday, July 17, 2012 10:26:52 AM UTC-4, joe wrote:
>
> Is there any way to make a field in a form referencing a foreign key null, 
> while storing a foreign key as a foreign key, but making dropdowns 
> corrospond to the format.  Here is my example:
>
> Model:
>
> db = DAL 
> ('sqlite://storage.sqlite')
>
> db.define_table('person',
> Field ('name'),
> Field ('email'),
> format = '%(name)s')
> 
> db.define_table('dog',
> Field ('name'), 
> Field ('owner', 
> requires = IS_EMPTY_OR 
> (IS_IN_DB 
> (db,db.person))), #I 
> have also tried default=None, and required = False, no difference
> format = '%(name)s')
>
>
> Controller:
>
> *from gluon.tools import Crud
> crud = Crud(db)
>
> def index():
> form = SQLFORM(db.person)
> if form.process().accepted:
> response.flash = 'success'
> return dict(form=form)
> 
> def add_dog():
> form = SQLFORM(db.dog)
> if form.process().accepted:
> response.flash = 'success'
> return dict(form=form)
>
> def view():
> grid = SQLFORM.smartgrid(db.dog)
> grid2 = crud.select(db.person)
> return dict(grid=grid, grid2=grid2)
> *
>
> *
> *
>
> When I have the above code, a foreign key will be stored in the database, but 
> all views of that key will be rendered as the numerical value of the foreign 
> key, and not the name.  In the table definition, if I just have *db.person* 
> instead of requires = *IS_EMPTY_OR*(*IS_IN_DB*(db,db.person)), It requires 
> the field.  
>
>
> Are there any workarounds?
>
> -Joe Peacock
>
>

-- 

[web2py] Re: foreign key reference and "not null" mutually exclusive. by design, or a bug?

[Ivan]

Same problem here!
The "notnull=True" parameter doesn't append the "NOT NULL" clause for 
reference fields.
The IS_IN_DB function activates the check at form-level while the notnull 
parameter should set the constraint at db-level.

[web2py] Re: foreign key reference and "not null" mutually exclusive. by design, or a bug?

[Cliff]

Check out IS_IN_DB and IS_NOT_IN_DB here:
http://www.web2py.com/book/default/chapter/07#Database-Validators

Are you creating the tables in native SQLite?


On Dec 8, 3:22 pm, nick name  wrote:
> I want to have a non-null foreign key reference, e.g.
>
> owner = db.define_table('owner'', Field('name'))
> package = db.define_table('package', Field('owner_id', owner,
> notnull=True), Field('name'))
>
> SQLite for example has no problem with this:
>
> sqlite> create table owner(id int primary key, name text);
> sqlite> create table package(id int primary key, owner_id references owner
> not null, name text);
> sqlite> insert into owner values(1, "web2py");
> sqlite> insert into package values(1, 1, "markmin");
> sqlite> insert into package values(2, null, "hello");
> Error: package.owner_id may not be NULL
>
> However, web2py will not put a "not null" qualifier on the foreign key
> reference; there is a specific check to NOT add a "not null" if the field
> is an id field or it is a reference field. Why is that?
>
> A bug in my app relating to this was just discovered by a user; I was
> relying on the database to signal an IntegrityError and fail to insert
> records that have a null reference, but they got in anyway.
>
> validate_and_insert will actually catch this -- but, why doesn't web2py
> append the NOT NULL?

[web2py] Re: Foreign key name getting other value, images app

[Paul]

Anthony, many thanks. I see that it is the image table format = '%(title)s 
that causes this to display.

I originally though it was the constraint db.comment.image_id.requires line 
with specified '%(title)s', but it does not seem to be.

Thank you for your help along this path. My apologies for such trivial 
beginner questions.

[web2py] Re: Foreign key name getting other value, images app

[Anthony]

Check out http://web2py.com/book/default/chapter/06#Record-Representation. 
Perhaps the format argument of the image table is set to %(title)s, which 
will display the title of the referenced image record rather than the id in 
dropdowns and tables.

Anthony

On Saturday, October 22, 2011 12:40:41 AM UTC-4, Paul wrote:
>
> When I use appadmin to display the COMMENT table, the comment.image_id 
> field displays the title field from the image table.
>
> Looking in sql.log, I see that comment.image_id is an integer referencing 
> image.id.
>
> image_id INTEGER REFERENCES image(id) ON DELETE CASCADE,
>
> I was expecting to see the integer id value, but the text of the title field 
> is displayed.
>
> How/why does the comment.image_id field display the text of title? What in 
> db.py connects those two?
>
>

[web2py] Re: Foreign Key Constraints with SQLite

[ron_m]

SQLite was embedded in the Python standard library since Python 2.5 so it 
would depend on which version of SQLite was pulled in for the versions of 
Python. Taking a quick look I could not find which version is included with 
the different versions of Python.

i noticed the SQLite docs are saying the pragma value currently defaults to 
off but should be explicitly set if there is a dependency on the setting in 
the application. Reading the pragma will determine whether or not the 
setting is actually there - no value vs 0 / 1 returned - here is a cut from 
their docs

sqlite> PRAGMA foreign_keys;
0

Tip: If the command "PRAGMA foreign_keys" returns no data instead of a 
single row containing "0" or "1", then the version of SQLite you are using 
does not support foreign keys (either because it is older than 3.6.19 or 
because it was compiled with 
SQLITE_OMIT_FOREIGN_KEYor 
SQLITE_OMIT_TRIGGER defined). 
The Python docs

http://docs.pysqlite.googlecode.com/hg/sqlite3.html

You should be able to open a connection in a Python shell and run the pragma 
in a statement to find out if it exists in your setup.

I don't use this database so maybe someone else has a definitive answer off 
the top.

Ron

[web2py] Re: Foreign Key

[Mirek Zvolský]

> about 2, deleting integrity. You can do:
>db.define_table(...,Field('name','reference
>blabla',ondelete='cascade'))

yes, you are true. I think, ON DELETE command keeps the integrity
inside the backend database (web2py generates just one DELETE
command).
My idea was to keep the integrity at web2py level (generate separate
DELETE commands /or UPDATE foreignkey=Null/ for all children), however
this would be slow and not necessary if all supported databases
support ON DELETE CASCADE and ON DELETE SET NULL.

cascade is default.
--- And what other possibilities are and their syntax? Same as in SQL,
f.e. can/should I write ondelete='set null' ?

Problem for me is, that with 'reference...' type foreign key cannot
point to the table, which I will define later /at this time/. This
means that more complex database models cannot be implemented. --- I
know, I repeat this always, I apologize for this :-)

[web2py] Re: Foreign Key

[mdipierro]

Will tink about 1.

about 2. You can do

db.define_table(...,Field('name','reference
blabla',ondelete='cascade'))

cascade is default.

On Nov 26, 8:39 am, Mirek Zvolský  wrote:
> >> Could you elaborate on what you'd expect from
> >> join expressions aren't automatically created based on the DAL definition
>
> 1) - answer to your question
> 2) - idea to deleting integrity - I think this could be very useful
> and easy to add to web2py core
>
> 1)
>
> SQL language is very clever, but I always wondered, why millions of
> developer always thousand times have to write:
> JOIN dog ON dog.person_id=person.id
> or in web2y:
> db(db.dog.person_id==person.id)
> if they want records from both tables. Because this "connecting"
> expression is already known from the database definition (from
> definition of foreign keys).
>
> So, it would be nice, if we have tool, which can create such
> expressions automatically, when we ask for fields/records from linked
> tables.
>
> I don't know, if this idea is implemented somewhere in Java,
> Microsofts, python,.. utilities.
> I have implemented it partially in Visual FoxPro, where my ideas were
> following:
>
> Each SQL command has its "basic" table (in FROM..) and "linked" tables
> (in JOINs). Inside linked tables one table from database can however
> occur more times (we need more than one table alias for table), from 2
> reasons:
> -- table can have 2 or more foreign keys pointing to same table,
> -- through more joins we can come back to an already used table /I
> know you don't believe this much :-)/
> So, I decided to find possibility, how I can name all possible table
> aliases in automatic way. I had a metadata table of all joins in
> database, so there were primary keys join.id
> I created table aliases like: dog ("basic" table), dog_15 ("linked"
> table person", join.id==15 was primary key of the foreign key
> dogs.preson_id in metadata).
> That way I can created all possible table aliases, and the name isn't
> very long, f.e. dog_15_4_8 is shorter as
> dog__dog_person_id__person_city_id__city_country_id
> From metadata table I can then take all what I need to automatically
> create join expressions:  table1.foreignkey==table2.primarykey
> So I can create query designers, where user can choose everything in
> database (every field in every table) - he can always choose from
> fields in current table + from joins outgoing from current table (list
> of joins I can get easy, when I select records (means joins) from
> metadata table WHERE table=currenttable). And whatever user will
> choose, SQL SELECT will be created automaticaly to receive required
> data.
>
> But I stopped this work in Visual FoxPro and switched to web2py. I
> would like to continue sometimes later with this in web2py, to create
> a plugin for this. Maybe after 6 months, but now I'm new in python and
> web2py to do such work. Or maybe somebody here can try go in this way,
> if he is interested...
>
> If in web2py, maybe there is not necessary to use metadata table of
> joins.
> Maybe this can be implemented as properties of foreign key, f.e.:
> db.dog.person_id.joinname='15'   # just a symbol to create aliases;
> target table we know from 'reference..' field type, target key is
> always primary key .id
>
> 2)
>
> Similar it would be nice to implement the deleting integrity at the
> application level:
> db.dog.person_id.ondelete='setnull'   # 'setnull' (default?),
> 'cascade', 'restrict' - says what to do when person (dog owner) is to
> be deleted

[web2py] Re: Foreign Key

[Mirek Zvolský]

>> Could you elaborate on what you'd expect from
>> join expressions aren't automatically created based on the DAL definition

1) - answer to your question
2) - idea to deleting integrity - I think this could be very useful
and easy to add to web2py core

1)

SQL language is very clever, but I always wondered, why millions of
developer always thousand times have to write:
JOIN dog ON dog.person_id=person.id
or in web2y:
db(db.dog.person_id==person.id)
if they want records from both tables. Because this "connecting"
expression is already known from the database definition (from
definition of foreign keys).

So, it would be nice, if we have tool, which can create such
expressions automatically, when we ask for fields/records from linked
tables.

I don't know, if this idea is implemented somewhere in Java,
Microsofts, python,.. utilities.
I have implemented it partially in Visual FoxPro, where my ideas were
following:

Each SQL command has its "basic" table (in FROM..) and "linked" tables
(in JOINs). Inside linked tables one table from database can however
occur more times (we need more than one table alias for table), from 2
reasons:
-- table can have 2 or more foreign keys pointing to same table,
-- through more joins we can come back to an already used table /I
know you don't believe this much :-)/
So, I decided to find possibility, how I can name all possible table
aliases in automatic way. I had a metadata table of all joins in
database, so there were primary keys join.id
I created table aliases like: dog ("basic" table), dog_15 ("linked"
table person", join.id==15 was primary key of the foreign key
dogs.preson_id in metadata).
That way I can created all possible table aliases, and the name isn't
very long, f.e. dog_15_4_8 is shorter as
dog__dog_person_id__person_city_id__city_country_id
>From metadata table I can then take all what I need to automatically
create join expressions:  table1.foreignkey==table2.primarykey
So I can create query designers, where user can choose everything in
database (every field in every table) - he can always choose from
fields in current table + from joins outgoing from current table (list
of joins I can get easy, when I select records (means joins) from
metadata table WHERE table=currenttable). And whatever user will
choose, SQL SELECT will be created automaticaly to receive required
data.

But I stopped this work in Visual FoxPro and switched to web2py. I
would like to continue sometimes later with this in web2py, to create
a plugin for this. Maybe after 6 months, but now I'm new in python and
web2py to do such work. Or maybe somebody here can try go in this way,
if he is interested...

If in web2py, maybe there is not necessary to use metadata table of
joins.
Maybe this can be implemented as properties of foreign key, f.e.:
db.dog.person_id.joinname='15'   # just a symbol to create aliases;
target table we know from 'reference..' field type, target key is
always primary key .id

2)

Similar it would be nice to implement the deleting integrity at the
application level:
db.dog.person_id.ondelete='setnull'   # 'setnull' (default?),
'cascade', 'restrict' - says what to do when person (dog owner) is to
be deleted

[web2py] Re: Foreign Key

[Mirek Zvolský]

-> Richard Vézina & all others:  so much words from me, and just
because I don't know 'id' field type of DAL.

[web2py] Re: Foreign Key

[DenesL]

@Johann
you missed this in the book
"Note that currently this is only available for DB2, MS-SQL, Ingres
and Informix, but others can be easily added."

but even if Postgres was supported you still could not reference
regular tables from keyed ones and viceversa, as specified in
http://groups.google.com/group/web2py/msg/c9848792a8999c5f

1) primarykey is a list of the field names that make up the primary
key
2) all primarykey fields will have NOT NULL set even if not specified
3) references are to other keyed tables only
4) references must use tablename.fieldname format, as shown above
5) update_record function is not available
6) web2py rev.1354 (or higher) has appadmin support for keyed tables.

Denes.

On Nov 25, 9:51 am, Johann Spies  wrote:
> Hallo Richard,
>
> On 25 November 2010 16:26, Richard Vézina wrote:
>
> > I think it cover in as keyed table in the book
>
> > I  am not sure how that will solve my problem.
>
> It creates new problems:
>
> signature = db.Table(db, 'signature',
>  Field('created_on', 'datetime', default=request.now,
>readable = False, writable = False),
>  Field('created_by', db.auth_user, default=auth.user_id,
>readable = False, writable = False),
>  Field('updated_on', 'datetime', default=request.now,
>readable = False, writable = False),
>  Field('updated_by', db.auth_user, update=auth.user_id,
>readable = False, writable = False)
>  )
>
> db.define_table('journal',
> Field('ouid','integer'),
> Field('journal',unique=True),
> Field('issn', length=10),
> Field('notes', type='text'),
> signature,
> primarykey = ['ouid'],
> format=lambda r: r.journal,
> migrate = False
> )
>
> Traceback (most recent call last):
>   File "/home/js/web2py/gluon/restricted.py", line 188, in restricted
> exec ccode in environment
>   File "/home/js/web2py/applications/init/models/kb4.py"
> , line
> 36, in 
> migrate = False
>   File "/home/js/web2py/gluon/sql.py", line 1375, in define_table
> t._create_references()
>   File "/home/js/web2py/gluon/sql.py", line 2303, in _create_references
> raise SyntaxError, 'invalid reference: %s' %ref
> SyntaxError: invalid reference: auth_user
>
> Regards
> Johann
> --
>  May grace and peace be yours in abundance through the full knowledge of God
> and of Jesus our Lord!  His divine power has given us everything we need for
> life and godliness through the full knowledge of the one who called us by
> his own glory and excellence.
> 2 Pet. 1:2b,3a

[web2py] Re: Foreign Key

[mdipierro]

good points. Could you elaborate on what you'd expect from

> 2) join expressions aren't automatically created based on the DAL definition

Massimo

On Nov 25, 9:48 am, Mirek Zvolský  wrote:
> I think in relational databases (=in standard database world) foreign
> key always should point to the primary key.
>
> Maybe PostgreSQL allows such something, maybe PostgreSQL (or other
> database) allows a construction of primary key from more as one
> fields, and so on, but I think it is not good idea to use such
> 'feature'. Web2py is more strict and in this case I think this is good
> (one reason, why I have chosen web2py).
>
> What you wait from such foreign key?
>
> I think foreign keys have only 2 goals:
> 1) if record with some primary key is to be deleted, records with
> connected foreign keys should be automatically delete (Cascade
> integrity) or cleared (SetNull integrity).
> 2) foreign key together with associated primary key can help
> automatically prepare join expressions, if we use DAL(database
> abstraction layer) instead of raw SQL.
>
> To previous 2 points I think, Web2py has problems or things which can
> be improved in the future:
> 1) SetNull integrity is not supported yet
> 2) join expressions aren't automatically created based on the DAL
> definition
> there is a third problem here,
> 3) Web2py turns on references support immediately after each table
> definition (correct solution is: after all tables are defined). From
> that reason you never can implement more complex database models in
> web2py.
>
> So we can see, using of 'reference..' field type give us no advantage
> at this time.
> You can use 'integer' type instead. The only thing you need to program
> is deleting integrity at application level.
>
> So I think using of 'integer' for the foreign key is solution for your
> problem.
>
> Or, 2-nd possible solution, you can have, if you will re-evaluated
> article.journal_id to point to journal.id. It's possible, because
> journal.id is unique of course, and journal.ouid should be unique too
> (because otherwise you have serious problems).
> Such re-evelauting is same, as you can find in Web2py sources, where
> replication is handled.
> See "CSV and Remote Database Synchronization" in 6-th chapter of
> Web2py book. uuid there is in similar role as your journal.ouid, but
> foreign key in this example always point to .id field.

[web2py] Re: Foreign Key

[Mirek Zvolský]

I think in relational databases (=in standard database world) foreign
key always should point to the primary key.

Maybe PostgreSQL allows such something, maybe PostgreSQL (or other
database) allows a construction of primary key from more as one
fields, and so on, but I think it is not good idea to use such
'feature'. Web2py is more strict and in this case I think this is good
(one reason, why I have chosen web2py).

What you wait from such foreign key?

I think foreign keys have only 2 goals:
1) if record with some primary key is to be deleted, records with
connected foreign keys should be automatically delete (Cascade
integrity) or cleared (SetNull integrity).
2) foreign key together with associated primary key can help
automatically prepare join expressions, if we use DAL(database
abstraction layer) instead of raw SQL.

To previous 2 points I think, Web2py has problems or things which can
be improved in the future:
1) SetNull integrity is not supported yet
2) join expressions aren't automatically created based on the DAL
definition
there is a third problem here,
3) Web2py turns on references support immediately after each table
definition (correct solution is: after all tables are defined). From
that reason you never can implement more complex database models in
web2py.

So we can see, using of 'reference..' field type give us no advantage
at this time.
You can use 'integer' type instead. The only thing you need to program
is deleting integrity at application level.

So I think using of 'integer' for the foreign key is solution for your
problem.

Or, 2-nd possible solution, you can have, if you will re-evaluated
article.journal_id to point to journal.id. It's possible, because
journal.id is unique of course, and journal.ouid should be unique too
(because otherwise you have serious problems).
Such re-evelauting is same, as you can find in Web2py sources, where
replication is handled.
See "CSV and Remote Database Synchronization" in 6-th chapter of
Web2py book. uuid there is in similar role as your journal.ouid, but
foreign key in this example always point to .id field.