This does not create a unique constrain on 'a' and 'b', it is noting like
running the command ALTER TABLE umultiple ADD CONSTRAINT test_unique UNIQUE
(a, b)! it create and extra column in the column in the table alled 'ab'
and puts the unique constrain on that! if you follow this approch for every
unique constrain you need to make you going to have all these extra columns
with repetitive merged up data that now you have to maintain! is there
really no way to add unique key constrain on multiple columns using dal?!
On Monday, July 11, 2011 7:34:26 AM UTC-7, Richard wrote:
>
> Just to close this thread nicely, here the solution I found (thanks to
> Bruno for concatenation :)
>
> import datetime
> now = datetime.datetime.today()
> db.define_table('person',
> Field('name'),
> Field('registration_stamp', 'datetime', default=now))
> recent = db(db.person.registration_stamp>now-datetime.timedelta(10))
> db.person.name.requires = IS_NOT_IN_DB(recent, 'person.name') # From the
> book
>
> db.define_table('umultiple',
> Field('a','string'),
> Field('b','string'),
> Field('ab', unique=True, compute=lambda r: r.a + r.b)) # Multiple
> columns UNIQUE constraint like SQL : ALTER TABLE ref_fnaregistry ADD
> CONSTRAINT test_unique UNIQUE (a, b);
>
> db.umultiple.a.requires =
> IS_NOT_IN_DB(db(db.umultiple.ab==str(request.vars.a)+str(request.vars.b)),
> 'umultiple.a')
> db.umultiple.b.requires =
> IS_NOT_IN_DB(db(db.umultiple.ab==str(request.vars.a)+str(request.vars.b)),
> 'umultiple.b') # Multiple columns UNIQUE constraint validators repeated
> over each fields that have been declared unique
>
>
> So, as Bruno said the concatenate field 'ab' that use unique=True is a DB
> validation and serve to assign UNIQUE check constaint to this column...
> Then to be sure not get a ticket you need to add a validator to the fields
> that has to be unique (form level validation). Here we want 'a' and 'b' to
> be unique. So, we use the versatility of the IS_NOT_IN_DB() validator... It
> can check if a value is in a given table or in a given set here we are
> passing a set like this :
> db(db.umultiple.ab==str(request.vars.a)+str(request.vars.b)). To make sure
> the user is going to understand that there is many fields responsible to
> the error, we replicate the validator for all the fields that are
> concatenated... But only one is enough to ensure there is no duplicated
> value in the db.
>
> I attach a little app...
>
> Richard
>
> On Sat, Jul 2, 2011 at 2:25 PM, Nick Arnett <nick....@gmail.com<javascript:>
> > wrote:
>
>>
>>
>> On Fri, Jul 1, 2011 at 1:20 AM, Bruno Rocha <rocha...@gmail.com<javascript:>
>> > wrote:
>>
>>> Db.define_table('foo',Field('a'),Field('b'), Field('ab',unique=True,
>>> compute=lambda r: r.a + r.b))
>>>
>> This failed for me with sqlite and I see from other discussions that the
>> same is true with Django - sqlite throws an error that it cannot create a
>> unique field.
>>
>> However, it seems to work fine with MySQL; it did create a unique index
>> on the column. I wasn't really planning to use sqlite, anyway, just
>> thought I'd keep things simpler at first. I just switched to MySQL now and
>> it's working fine.
>>
>> Thanks for the help.
>>
>> Nick
>>
>
>
--
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.
