unique constraint of multiple columns (at database level)

[canna]

hello everybody!

I created a table of contacts that requires the first name+last name
to be unique
the table:

db.define_table("Contacts",
Field("FirstName", "string", length=45, notnull=True),
Field("LastName", "string", length=45, notnull=True),
Field("Title", "string", length=45, default=None),
Field("Department", "string", length=45, default=None),
Field("Address", "string", length=255, default=None),
Field("Phone", "string", length=45, default=None),
Field("Fax", "string", length=45, default=None),
Field("Email", "string", length=45, default=None),
Field("Comments", "text", default=None), migrate=True)

how do I instruct the DAL to create unique constraint on FirstName
+LastName when in creates the table in the database?
I have lots of tables that have unique constrain on 2-3 fields, so I
prefer not to do this manually.....
any help will be appreciated!

[ron_m]

I would like to respectfully ask how a first and last name combined could be unique in the real world? There are lots of people with identical first and last names.

A better candidate in your table is Email.

I know that doesn't answer your question but I think it is worth consieration.

Ron

[Luther Goh Lu Feng]

Is there a reason why you are not using Auth, if you aren't?

http://www.web2py.com/book/default/chapter/08#Authentication

Anyway, assuming that you truly want to add the unique constraint,
then I am guessing that you could construct virtual fields

http://www.web2py.com/book/default/chapter/06#Virtual-Fields

and apply the constraint http://www.web2py.com/book/default/docstring/IS_NOT_IN_DB

db.page.title.requires = IS_NOT_IN_DB(db, 'page.title') #http://
www.web2py.com/book/default/chapter/03

[canna]

Thank you guys for your answers,
ron - it was just an example
Luther - this table was migrated from our old data and we do intent to
use auth instead

still, it's good practice to always have unique field or combination
of fields in a table besides the id, id is for machines, not suitable
for the real world....

my understanding is that IS_NOT_IN_DB is not forced at the database
level like the "unique" keyword, I'm looking for something like the
unique keyword only for multiple fields, not just one
is there something like this?

[canna]

[Manuele Pesenti]

Il 28/12/2010 23:40, Luther Goh Lu Feng ha scritto:
> Is there a reason why you are not using Auth, if you aren't?
>
> http://www.web2py.com/book/default/chapter/08#Authentication
>
> Anyway, assuming that you truly want to add the unique constraint,
> then I am guessing that you could construct virtual fields
>
> http://www.web2py.com/book/default/chapter/06#Virtual-Fields
>
> and apply the constraint http://www.web2py.com/book/default/docstring/IS_NOT_IN_DB
>
> db.page.title.requires = IS_NOT_IN_DB(db, 'page.title') #http://
> www.web2py.com/book/default/chapter/03

interesting answer, but trying what is suggested ti seams not possible
to refer to a virtual field such as it's possible with real fields... I
mean trying this little example that's the result I get

>>> db.define_table('mytable',
... Field('field1','integer'),
... Field('field2','integer'),
... )
>>> class MyVirtualFields(object):
... def uniqueField(self):
... d = dict()
... for i in ['field1', 'field2']:
... d[db.mytable[i].name] = self.mytable[i]
... return d
...
>>> db.mytable.virtualfields.append(MyVirtualFields())
>>> db.mytable.uniqueField.requires = IS_NOT_IN_DB(db,
'mytable.uniqueField')
Traceback (most recent call last):
File "<console>", line 1, in <module>
File "/home/manuele/Progetti/web2py/gluon/sql.py", line 1688, in
__getattr__
return dict.__getitem__(self,key)
KeyError: 'uniqueField'

what's wrong in it?

thank you very much

Manuele

[mdipierro]

virtual fields are not computed fields. They do not exist in the db.
They are computed when records are retrieved. They cannot have
requirements or attributes because there no way to input their values
in a form. So this is simply imporssible:

db.mytable.uniqueField.requires = ...

On Dec 29, 5:57 am, Manuele Pesenti <manuele.pese...@gmail.com> wrote:
> Il 28/12/2010 23:40, Luther Goh Lu Feng ha scritto:> Is there a reason why you are not using Auth, if you aren't?
>
> >http://www.web2py.com/book/default/chapter/08#Authentication
>
> > Anyway, assuming that you truly want to add the unique constraint,
> > then I am guessing that you could construct virtual fields
>
> >http://www.web2py.com/book/default/chapter/06#Virtual-Fields
>

> > and apply the constrainthttp://www.web2py.com/book/default/docstring/IS_NOT_IN_DB

[Bruno Rocha]

Maybe not the best way, but just for testing, I tried this:

<model>
db.define_table('person',
                Field('unikey','id',compute=lambda r: r.fname+r.lname),
                Field('fname'),
                field('lname')
                )
</model>
got an error because the 'id' needs to be int

<error>
File "/home/rochacbruno/projects/web2py/gluon/dal.py", line 805, in expand
return self.represent(expression,field_type)
File "/home/rochacbruno/projects/web2py/gluon/dal.py", line 1096, in represent
return str(int(obj))
ValueError: invalid literal for int() with base 10:
</error>

Then I tried with integers and works ok.

person.unikey = 12

person.fname = 1

person.lname = 2

Is there a way to have string fields as primary keys?

[villas]

Hi Bruno,

Work was done (I believe by Denes) to introduce non-standard primary
keys. However, that was really for legacy DBs that could not be
migrated to the web2py conventions. In any case, it was only available
for certain DBs and I'm not sure the work has been migrated and tested
properly in the new DAL.

In the case of a new app, you should always try to have standard
integer primary keys (excepting uids in the case of combining tables/
databases).

I would suggest you try compute to produce a combined 'string' field
and then use IS_NOT_IN_DB. You might prefer to use all lower or upper
case to make it case-insensitive.

Alternatively you can create a unique index at the DB level. You
would have to use some raw SQL which would vary according to the DB
you use. That would stop duplicates effectively enough, but I guess
your web2py app would not handle it as smoothly.

-D

[Manuele Pesenti]

Trying quite the same solution I got a different error:

Traceback (most recent call last):

File "/home/manuele/Progetti/web2py/gluon/restricted.py", line 188, in
restricted
exec ccode in environment
File
"/home/manuele/Progetti/web2py/applications/wrf_dbi/models/wrfdb.py"
<http://localhost:8000/admin/default/edit/wrf_dbi/models/wrfdb.py>, line
109, in <module>
*genericFields+specificFields['wind']
File "/home/manuele/Progetti/web2py/gluon/sql.py", line 1365, in
define_table
t._create(migrate=migrate, fake_migrate=fake_migrate)
File "/home/manuele/Progetti/web2py/gluon/sql.py", line 1861, in _create
fake_migrate=fake_migrate)
File "/home/manuele/Progetti/web2py/gluon/sql.py", line 1934, in _migrate
self._db._execute(sub_query)
File "/home/manuele/Progetti/web2py/gluon/sql.py", line 983, in <lambda>
self._execute = lambda *a, **b: self._cursor.execute(*a, **b)
File "/usr/lib/pymodules/python2.6/MySQLdb/cursors.py", line 166, in execute
self.errorhandler(self, exc, value)
File "/usr/lib/pymodules/python2.6/MySQLdb/connections.py", line 35, in
defaulterrorhandler
raise errorclass, errorvalue
ProgrammingError: (1064, "You have an error in your SQL syntax; check
the manual that corresponds to your MySQL server version for the right
syntax to use near 'VARCHAR(512)' at line 1")

I used a string field as the unique key and the function called by
lambda returns a string.

Manuele

[Manuele Pesenti]

I beg your pardon... it works!
the problem was that I used 'unique' as the field name that is a not
valid name :)

so it coud be a solution to manage uniques

bye
M.

Il 05/01/2011 12:04, Manuele Pesenti ha scritto:
> Trying quite the same solution I got a different error:
>

> [omissis]

[dederocks]

Manuele,

May I ask you how you did it?
This is my model:
db.define_table('Materiau',
Field('mindex','string', compute=lambda r: '%s%s' %
(r.Nom,r.MType)),
Field('MType', label='Type'),
Field('Nom'),
format='%(MType)s %(Nom)s')
db.Materiau.MType.requires=IS_IN_SET(('Tête','Armoire','Chassis'))
db.Materiau.mindex.requires=IS_NOT_IN_DB(db, db.Materiau.mindex)

Well, If I try to insert a duplicate with the appadmin insert form, it
goes through without rejection.
Or do I expect too much from the admin insert form?

On 5 jan, 12:26, Manuele Pesenti <manuele.pese...@gmail.com> wrote:
> I beg your pardon... it works!
> the problem was that I used 'unique' as the field name that is a not
> valid name :)
>
> so it coud be a solution to manage uniques
>
> bye
>      M.
>
> Il 05/01/2011 12:04, Manuele Pesenti ha scritto:
>
> > Trying quite the same solution I got a different error:
>
> > [omissis]
>

> > I used a string field as theuniquekey and the function called by

[Manuele Pesenti]

Il 11/01/2011 10:05, dederocks ha scritto:
> Manuele,
>
> May I ask you how you did it?

First of all consider to define a function that returns a unique result
starting from your fields in the uniques list. If theese fields are all
integer (such as id of external references) you can simply append them
in a string following always the same order or if they are string you
car create a dictionary with the field names and values and transform it
in a string that will be unique. Let's call it getUnique().

def getUnique(**kwargs):
...
return out

now add this 'myid' field to your table as in the example below:

db.define_table('mytable',
Field('field1', ...),
Field('field2', ...),
...
Field('myid', length=256, unique=True,
compute=lambda r: getUnique(
**dict([(x, r[x]) for x in (field2, field3, ..., )])
)

I hope I've been clear

bye
Manuele

[dederocks]

Thanks!
I was actualy missing the 'unique=True' (IS_NOT_IN_DB(...) doesn't
work), and then I'll need to add a 'try.. except' sequence to trap an
attempt to insert a duplicate.

On 11 jan, 10:32, Manuele Pesenti <manuele.pese...@gmail.com> wrote:
> Il 11/01/2011 10:05, dederocks ha scritto:> Manuele,
>
> > May I ask you how you did it?
>
> First of all consider to define a function that returns auniqueresult
> starting from your fields in the uniques list. If theese fields are all
> integer (such as id of external references) you can simply append them
> in a string following always the same order or if they are string you
> car create a dictionary with the field names and values and transform it

> in a string that will beunique. Let's call it getUnique().