Massimo,
Thanks for your instant reply, I am afraid I am missing some line of
code to get the multi column constraint to work properly.
In my model I have got the following tables:
db.define_table('company',
SQLField('name', length=50, default='', notnull=True),
SQLField('coc_number', length=8, default='', notnull=True),
SQLField('subdossiernumber', length=4, default='0000', notnull=True),
SQLField('legalform', db.rechtsvorm, ondelete='RESTRICT'),
SQLField('status', length=1, default='0', notnull=True),
SQLField('ranking', length=3, default='999', notnull=True),
migrate='company.table')
db.define_table('training',
SQLField('number', type='integer', length=6, default='', notnull=True,
unique=True),
SQLField('name', length=40, default='', notnull=True, unique=True),
migrate='training.table')
Since the relationship between company and training is of type n-to-m
I added a third table:
db.define_table('companytraining',
SQLField('company', db.company, default='', notnull=True),
SQLField('training', length=40, default='', notnull=True),
migrate='companytraining.table')
db.companytraining.training.requires=IS_IN_DB(db, '
training.name','%
(name)s')
The multi column unique constraint should prevent users from entering
a training more than once for the same company. The
IS_NOT_IN_DB(db(db.table.field1==request.field1),'table.field2')
statement contains a request variable, which in my case doesn't exist,
furthermore, validators start with
db.companytraining.company.requires= .... expresion which in case of a
two column unique constraint doesn't seem to make sense. I thought the
third table definition would read something like:
db.define_table('companytraining',
SQLField('company', db.company, default='', notnull=True),
SQLField('training', length=40, default='', notnull=True),
migrate='companytraining.table')
db.companytraining.training.requires=IS_IN_DB(db, '
training.name','%
(name)s')
IS_NOT_IN_DB(db
(db.companytraining.company,db.companytraining.training))
However, neither this line nor IS_NOT_IN_DB(db
(db.companytraining.company==request.company),'companytraining.training')
enforce a two column unique constraint at web2py level. I my
illustration is helpful in solving this problem.
Best regards,
Annet.