OpenERP Unique Constraint

Question

I have a table in OpenERP/PostgreSQL with the following columns: name and description.

I added the following validation for unique name:

_sql_constraints = [('unique_name', 'unique(name)', 'A record with the same name already exists.')]

It works fine but it is case sensitive. Currently, it accepts values such as "Mickey", "MICKEY" and "mickey":

Wrong Way:
--------------------------
| name   | description   |
--------------------------
| mickey | not a mouse   |
--------------------------
| MICKEY | not a mouse   |
--------------------------
| Mickey | not a mouse   |
--------------------------

Is there a way to revise the validation code so that it will not allow users to add several values such as "Mickey", "MICKEY" and "mickey"? How can I make the unique key validation case insensitive?

Right Way:
--------------------------------
| name         | description   |
--------------------------------
| mickey       | not a mouse   |
--------------------------------
| mickey mouse | is a mouse    |
--------------------------------
| donald       | is a duck     |
--------------------------------

score 16 · Accepted Answer · edited May 13 '14 at 21:38

16

For case insensitive constraints check out HERE else you can always use Openerp Constraints instead of SQL .

for openerp Constraints

check the example

def _check_unique_insesitive(self, cr, uid, ids, context=None):
    sr_ids = self.search(cr, 1 ,[], context=context)
    lst = [
            x.FIELD.lower() for x in self.browse(cr, uid, sr_ids, context=context)
            if x.FIELD and x.id not in ids
          ]
    for self_obj in self.browse(cr, uid, ids, context=context):
        if self_obj.FILD and self_obj.FILD.lower() in  lst:
            return False
    return True

_constraints = [(_check_unique_insesitive, 'Error: UNIQUE MSG', ['FIELD'])]

edited May 13 '14 at 21:38

Ethan Furman

63,992
20
159
237

answered Nov 07 '12 at 05:25

Ruchir Shukla

805
6
13

Hi Ruchir! thanks for the prompt response. Can you give me an example how I can implement the desired validation using OpenERP Constraints? – codemickeycode Nov 07 '12 at 05:37
List out all Data for Specific field in one list. Convert it in either Lower or upper Case, Then check for the New Value with in operator , and return the opposite of result . – Ruchir Shukla Nov 07 '12 at 05:38
It worked. Thanks Ruchir! I just modified the list to exclude the last entry – codemickeycode Nov 07 '12 at 07:31
1

you mean the current object's data ? then last entry will not work always if you have different sorting order, Please change this lst = [x.FIELD.lower() for x in self.browse(cr, uid, sr_ids, context=context) if x.FIELD] to lst = [x.FIELD.lower() for x in self.browse(cr, uid, sr_ids, context=context) if x.FIELD and if x.id not in ids] – Ruchir Shukla Nov 08 '12 at 06:24
Yes Ruchir, the data being entered/inputted in the form. I did this: lst = [x.name.lower() for x in self.browse(cr, uid, sr_ids, context=context) if x.name and x.id !=last_rec_id] Thanks for the tip – codemickeycode Nov 08 '12 at 10:35

score 1 · Answer 2 · answered Apr 26 '18 at 15:33

This way without read all data from database:

def _check_unique_insesitive(self, cr, uid, ids, context=None):

    for self_obj in self.browse(cr, uid, ids, context=context):
        if self_obj.name and self.search_count(cr, uid, [('name', '=ilike', self_obj.name), ('id', '!=', self_obj.id)], context=context) != 0:
            return False

    return True

_constraints = [(_check_unique_insesitive, _('The name must be unique!'), ['name'])]

Jaime Crato · Answer 3 · 2018-02-23T00:15:17.430

-1

using constrains in Odoo 8.0 or above in a simpler way. get all records of the model and check the desired field value with lower() and excluding the self record.

@api.constrains('code')
def _check_duplicate_code(self):
    codes = self.search([])
        for c in codes:
            if self.code.lower() == c.code.lower() and self.id != c.id:
                raise exceptions.ValidationError("Error: code must be unique")

edited Feb 23 '18 at 00:15

answered Feb 22 '18 at 22:59

Jaime Crato

1
1

Answers should contain description of what code does not just a block of code. Answers from questions that are more than a year old with other answers especially, should include reasons as to why this answer is relevant due to any changes in the language or differences between the other posted answers. – Matthew Ciaramitaro Feb 22 '18 at 23:06

OpenERP Unique Constraint

3 Answers3