Re: Restrictions for a specific situation in my DB

On Mon, 11 May 2020 17:33:25 -0500
JORGE MALDONADO <jorgemal1960(at)gmail(dot)com> wrote:

> Hi,

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?

No, you don't want to do that.

Imagine #2 is 8/4, 8+4=12 == 10+2=12…

The only possibility is to use 2 unique indexes with your 2 FK :
m1, m2
m2, m1
this way, as you can't have a doublon either ways, you'll fulfill your
double condition (if I understand it correctly).

Jean-Yves