# Sql-constraint on Char: allow empty values or make Char default to NULL

**URL:** <https://discuss.tryton.org/t/sql-constraint-on-char-allow-empty-values-or-make-char-default-to-null/4601>\
**Category:** Developer\
**Created:** [September 8, 2021, 7:43pm UTC](https://discuss.tryton.org/t/sql-constraint-on-char-allow-empty-values-or-make-char-default-to-null/4601 "2021-09-08T19:43:22Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![htgoebel](https://discuss-cdn.tryton.org/user_avatar/discuss.tryton.org/htgoebel/32/1256_2.png) [@htgoebel](https://discuss.tryton.org/u/htgoebel)\
**Post date:** [September 8, 2021, 7:43pm UTC](https://discuss.tryton.org/t/sql-constraint-on-char-allow-empty-values-or-make-char-default-to-null/4601/1 "2021-09-08T19:43:22Z")

</div>

Mittelab’s implementation of “association” (see [Association blueprint](https://discuss.tryton.org/t/association-blueprint/1282)) has I minor issue, I try to solve: One can not have two Members in “draft” state. This is caused by the fact that when saving a Member, it will get `code` “” (empty string) - anyhow `code` is defined as “unique”. This inhibits saving a second Member in “draft” state.

How can I make “code” to be NULL instead of an empty string by default? Or is there any other solution?

This is the relevant part of `Member`, defining `code`: [complete code](https://git.mittelab.org/infra/tryton/mittelab_association/-/blob/master/association.py#L42)

```auto
class Member(…):
    …
    code = fields.Char("Code", select=True,
        states={
            'readonly': Eval('code_readonly', True),
            'required': Eval('state') != 'draft'
            },
        depends=_depends + ['code_readonly'],
        help="The internal identifier for the associate.")
    code_readonly = fields.Function(fields.Boolean("Code Readonly"),
                                    'get_code_readonly')
    @classmethod
    def __setup__ (cls):
        …
        cls._sql_constraints = [
            ('code_uniq',
              Unique(t, t.code),
              'association.msg_member_code_unique'
            ),
        ]

```

@wifasoi

---

<div class="post-metadata">

**Author:** ![udono](https://discuss-cdn.tryton.org/user_avatar/discuss.tryton.org/udono/32/2114_2.png) [@udono](https://discuss.tryton.org/u/udono)\
**Post date:** [September 8, 2021, 8:03pm UTC](https://discuss.tryton.org/t/sql-constraint-on-char-allow-empty-values-or-make-char-default-to-null/4601/2 "2021-09-08T20:03:01Z")

</div>

As there is no technical reason to have a unique constraint on the code field, I realy would avoid it. Even the invoice number has no such a constraint, as it is driven by an `ir.sequence`. An alternative approach would be using [ModelStorage.validate](https://docs.tryton.org/projects/server/en/latest/ref/models/models.html?highlight=validate#trytond.model.ModelStorage.validate) there you can use an informative error message with additional information which number is already used, to ease the detection of possible double entries.

---

<div class="post-metadata">

**Author:** ![htgoebel](https://discuss-cdn.tryton.org/user_avatar/discuss.tryton.org/htgoebel/32/1256_2.png) [@htgoebel](https://discuss.tryton.org/u/htgoebel)\
**Post date:** [September 8, 2021, 8:10pm UTC](https://discuss.tryton.org/t/sql-constraint-on-char-allow-empty-values-or-make-char-default-to-null/4601/3 "2021-09-08T20:10:07Z")

</div>

Thanks for you quick resonce, lat in the evening 🙂

`Member.code` might not be backed by a sequence: In the Menbership configuration, one can easily remove the “member sequence”, which will make the `code` field read/write. (This seems to modeled be like `Article.code` .

---

<div class="post-metadata">

**Author:** ![udono](https://discuss-cdn.tryton.org/user_avatar/discuss.tryton.org/udono/32/2114_2.png) [@udono](https://discuss.tryton.org/u/udono)\
**Post date:** [September 8, 2021, 8:23pm UTC](https://discuss.tryton.org/t/sql-constraint-on-char-allow-empty-values-or-make-char-default-to-null/4601/4 "2021-09-08T20:23:15Z")

</div>

> [@htgoebel](#):
>
> `Member.code` might not be backed by a sequence: In the Menbership configuration, one can easily remove the “member sequence”, which will make the `code` field read/write. (This seems to modeled be like `Article.code` .

Yes, but the unique code constraint on products in Tryton was especially introduced, to be able to use the code as identifier for [SKU](https://en.wikipedia.org/wiki/Stock_keeping_unit). Membership numbers IMHO should not have such a hard requirement on uniqueness. What speaks against having two members with the same code, when a warning was shown that it already exists? In the past in many places in Tryton we remove SQL-constraints when there is not technical reason, because they break the modularity.

---

<div class="post-metadata">

**Author:** ![ced](https://discuss-cdn.tryton.org/user_avatar/discuss.tryton.org/ced/32/1237_2.png) [@ced](https://discuss.tryton.org/u/ced)\
**Post date:** [September 8, 2021, 10:33pm UTC](https://discuss.tryton.org/t/sql-constraint-on-char-allow-empty-values-or-make-char-default-to-null/4601/5 "2021-09-08T22:33:44Z")

</div>

Just use an [`Exclude`](https://docs.tryton.org/projects/server/en/latest/ref/models/models.html#exclude) constraint with the proper clause to not check draft state.

---

<div class="post-metadata">

**Author:** ![wifasoi](https://discuss-cdn.tryton.org/letter_avatar_proxy/v4/letter/w/ed655f/32.png) [@wifasoi](https://discuss.tryton.org/u/wifasoi)\
**Post date:** [September 9, 2021, 9:17am UTC](https://discuss.tryton.org/t/sql-constraint-on-char-allow-empty-values-or-make-char-default-to-null/4601/6 "2021-09-09T09:17:39Z")

</div>

Ok, seams reasonable.  
I can it and push it in codereview (or @htgoebel ou can email a patchset if you want).

Anyway, I’m not dead.. just a bit too busy 😃

---

<div class="post-metadata">

**Author:** ![htgoebel](https://discuss-cdn.tryton.org/user_avatar/discuss.tryton.org/htgoebel/32/1256_2.png) [@htgoebel](https://discuss.tryton.org/u/htgoebel)\
**Post date:** [September 9, 2021, 9:37am UTC](https://discuss.tryton.org/t/sql-constraint-on-char-allow-empty-values-or-make-char-default-to-null/4601/7 "2021-09-09T09:37:04Z")

</div>

@ced Thanks pointing to Exclude. So you recommend keeping the SQL-constraints?

---

<div class="post-metadata">

**Author:** ![htgoebel](https://discuss-cdn.tryton.org/user_avatar/discuss.tryton.org/htgoebel/32/1256_2.png) [@htgoebel](https://discuss.tryton.org/u/htgoebel)\
**Post date:** [September 20, 2021, 6:26pm UTC](https://discuss.tryton.org/t/sql-constraint-on-char-allow-empty-values-or-make-char-default-to-null/4601/8 "2021-09-20T18:26:08Z")

</div>

@wifasoi Patchset is available at [htgoebel/trytond-association: Fork and extension of of https://git.mittelab.org/infra/tryton/mittelab\_association - Codeberg.org](https://codeberg.org/htgoebel/trytond-association/)
