Re: Unique constraint on part numbers

["Chris Travers" <..hidden..>]

On Sun, Jun 15, 2008 at 10:29 PM, Luke <..hidden..> wrote:
> I would guess that is so you can obsolete a part/service/what ever, and
> create a newer version which replaces the old one, without screwing up
> existing invoices posted with the old info.
>
> Not, I suspect, the best way of handling this (invoices should not be an
> entity with variable items after their posted), but I believe that's why
> you can't make the partnumber unique.

One thing you can do at the moment (before we redesign this part) is:

CREATE UNIQUE INDEX parts_partnumber_u ON parts (partnumber) WHERE
obsolete is not true;

There are a lot of parts of the application where constraints should
be added.  However, our focus is on redesigning these sections.  We
have committed to primary and foreign key constraints on the entire
application but beyond that, the Perl code we inherited makes it a
problem to add too many constraints until we rewrite.

Hope this helps;
Chris Travers