I'll illustrate with a variant of the dog example schema (see below): I
have a 'sire_id' field that refers back to the 'dog' table. However I
have the ondelete attribute set to 'RESTRICT' [1]. I'd like to prevent
dogs that have sired other dogs being deleted. When I attempt this it
looks liek it has worked (i.e one less row in the grid), but on refresh
I see the alleged deleted row back again.
The database log shows:
ERROR: update or delete on table "dog" violates foreign key constraint
"dog_sire_id_fkey" on table "dog"
DETAIL: Key (id)=(1) is still referenced from table "dog".
STATEMENT: DELETE FROM dog WHERE (dog.id = 1);
Shouldn't the form detect that the delete failed?
regards
Mark
model:
---------
db.define_table(
'dog',
Field('owner_id', 'reference person'),
Field('name'),
Field('type'),
Field('sire_id', 'reference dog', ondelete='RESTRICT'),
Field('picture', 'upload', default=''),
format = '%(name)s',
singular = 'Dog',
plural = 'Dogs',
)
db.dog.name.requires = IS_NOT_EMPTY()
db.dog.type.requires = IS_IN_SET(('small', 'medium', 'large'))
db.dog.sire_id.requires = IS_EMPTY_OR(IS_IN_DB(db,'dog.id','%(name)s'))
[1] I'm using postgres here but sqlite will work too if you do:
sqlite> pragma foreign_keys=on;
Controller:
--------------
def register_dog():
form=SQLFORM.grid(db.dog, csv=False, paginate=5, user_signature=False)
return dict(form=form)
