Date: Tue, 12 May 2020 00:49:38 +0200
From: Bzzzz <lazyvirus@gmx.com>
To: pgsql-novice@lists.postgresql.org
Subject: Re: Restrictions for a specific situation in my DB
Message-ID: <20200512004938.3634d392@msi.defcon1.lan>
In-Reply-To: 
 <CAAY=A79am3Cndk6DNPKA6Z0286YYxF9eYKWR=fUic-CEPjQntw@mail.gmail.com>
References: 
 <CAAY=A79am3Cndk6DNPKA6Z0286YYxF9eYKWR=fUic-CEPjQntw@mail.gmail.com>
Organization: Anyone, anywhere BUT in banana demokratik republik of france
 and UERSS.
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: quoted-printable
Precedence: bulk

On Mon, 11 May 2020 17:33:25 -0500
JORGE MALDONADO <jorgemal1960@gmail.com> wrote:

> Hi,
>
> I have a table with the following structure:
>
> ------------------------------------------------------------------------=
------
>
> FIELD       TYPE                         COMMENTS
> ------------------------------------------------------------------------=
------
>     id            serial                 primary key
>     m1          integer              id of record in another table
>     m2          integer              id of record in another table
>
> So, "m1" and "m2" are foreign keys.
> I have read that it is a good practice to define an index for each
> foreign key so "m1" and "m2" are also regular and independent indexes.
>
> Now, the combination of "m1" and "m2" together cannot be duplicated.
> So for example, the following entries
>
> ---------------------------------------------------------------------
> id             m1            m2           COMMENTS
> ---------------------------------------------------------------------
>  1              2               10
>  2              8                3
>  3             18               1
>  4              2               10           This is invalid.
>  5             13               8            This is invalid.
>  6             18              18           This is invalid.
>
> Maybe the case for records 4 and 5 can be achieved by setting 2
> restrictions:
> Restriction 1: "m1 + m2" fields
> Restriction 2: "m2 + m1" fields
>
> But, is this a good approach?

I forgot to say that your m1 and m2 existing indexes must also be of the
unique kind.

Jean-Yves