Re: [Gnumed-devel] ID issuers, types, and validation

[Busser, Jim]

On 2013-07-26, at 4:04 PM, Jim Busser <address@hidden> wrote:

> Along that same thread
> 
>       http://lists.gnu.org/archive/html/gnumed-devel/2011-09/msg00267.html
> 
> you offered on 2011-09-25, at 2:56 PM:

I can easily understand there to exist rules (of whatever
origin) applicable to manifestations of external ID types.

I would think it proper database design to store such
restrictions with each external ID type.

======

I continue to think that uniqueness, for those external IDs that are intended 
to be unique, is worth supporting.

If this is not supportible in the proposed regexen, then in a column is_unique ?

In this example at stack exchange, it is possible to make a column entry 
unique, depending on the value of another column

        http://stackoverflow.com/a/16236566

        CREATE UNIQUE INDEX stop_myc ON stop (col_a) WHERE (col_b is null);

        PostgreSQL doesn't define a partial (ie conditional) UNIQUE constraint 
- however,
        you can create a partial unique index. PostgreSQL uses unique indexes to
        implement unique constraints, so the effect is the same, you just won't 
see
        the constaint listed in information_schema.

        … while "constraint" doesn't show up as a constraint, it nonetheless 
gives the desired
        error of ERROR: duplicate key value violates unique constraint 
"stop_myc"

so where in GNUmed an id type has been defined as unique, say by setting the 
column value 'regexen' to 'unique', then would something along the lines of

        CREATE UNIQUE INDEX nodup_id
        ON Dem.lnk_identity2ext_id (external_id)
        WHERE (SELECT regex
                                FROM dem.enum_ext_id_types
                                WHERE fk_origin = dem.enum_ext_id_types.pk
                                AND regex = 'unique');

work?