Exclude constraint in _sql_constraints

Support Developer
1

Hola!

Tengo el siguiente problema, necesito que permitir un campo nulo en una tabla dónde ese campo tiene una restricción Unique. Si ya tengo algún registro con ese campo nulo, al guardar otro registro, obtengo un mensaje de error debido a que considera esos registros como iguales. Intento utilizar una condición Exclude() para que exclya la restriciión unique en registros con Null en el campo webcode, en _sql_constraints pero no entiendo la sintaxis a usar, siempre tengo distintos errores. Este es el código relevante:

class Template(metaclass=PoolMeta):
    __name__ = 'product.template'

    webcode = fields.Char('Code')

    @classmethod
    def __setup__(cls):
        super().__setup__()
        t = cls.__table__()
        cls._sql_constraints += [
            ('webcode_uniq', Unique(t, t.webcode),
                msg_template_webcode_unique')
            ]

Utilizo Tryton 6.0

Saludos!
Luis

2

In supported SQL NULL can not trigger unique constraint because NULL is different from NULL.
So I guess your table is not filled with NULL but an empty string.

If you do want to exclude also empty string from the unique constraint, you can use:
Exclude(t, (NullIf(t.webcode, ''), Equal)).

3

Hi Cédric!

I can’t find NullIf operator or function neither from sql.operators nor from sql (I’m using Python 3.7)

Regards
Luis

4

It is in sql.conditionals.

It is no more supported since 1,5 years: Status of Python versions

5

There is some workaround to mimic Nullif?

6

sorry my bad! conditionals!

7

I’m doing something wrong:

@classmethod
def __setup__(cls):
    super().__setup__()
    t = cls.__table__()
    cls._sql_constraints += [
        ('webcode_uniq', Unique(t, t.webcode),
            'lantos.msg_template_webcode_unique'),
            Exclude(t, (NullIf(t.webcode, ''), Equal))
        ]

File “/home/lims/src/venv_test/lib/python3.7/site-packages/trytond/model/modelsql.py”, line 306, in register
for ident, constraint, _ in cls._sql_constraints:
TypeError: cannot unpack non-iterable Exclude object

8

You must replace the Unique instance by the Exclude instance.

9

Thanks it works!!!

I had to do it in 2 steps, first in database this ALTER TABLE product_template DROP CONSTRAINT product_template_wecode_unique, and then using these code:

@classmethod
def __setup__(cls):
    super().__setup__()
    t = cls.__table__()
    cls._sql_constraints += [
        ('webcode_uniq', 
            Exclude(t, (NullIf(t.webcode, ''), Equal)),
            'msg_template_webcode_unique')
        ]

This index was created in database:

“product_template_webcode_uniq” EXCLUDE USING btree (NULLIF(webcode::text, ‘’::text) WITH =)

Thanks Cédric you are very pacient and kindly
Regards
Luis

10

This topic was automatically closed 24 hours after the last reply. New replies are no longer allowed.