Postgres Pro
Downloads
Home   >   mailing lists

Re: Restrictions for a specific situation in my DB - Mailing list pgsql-novice

From David G. Johnston
Subject Re: Restrictions for a specific situation in my DB
Date
Msg-id CAKFQuwaw=yFb7FauOH=6cw4USmb-o7Z6vGQwr10qUrDgRU6NVA@mail.gmail.com
Whole thread Raw
In response to Restrictions for a specific situation in my DB  (JORGE MALDONADO <jorgemal1960@gmail.com>)
List pgsql-novice
Tree view
On Mon, May 11, 2020 at 3:33 PM 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.

Its nice you provided examples but I'm pretty sure you have one (maybe two) typos - one in row 2 (m2) and one in row 3 (m2)...otherwise your stated rule and the data don't agree.
 

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?

Sure, two separate multi-column unique indexes and a row constraint that m1 != m2 would work.  Whether its "good" depends greatly on how the model/data is going to be used.

David J.

pgsql-novice by date:

Previous
From: Bzzzz
Date:
Subject: Re: Restrictions for a specific situation in my DB
Next
From: Laurenz Albe
Date:
Subject: Re: Restrictions for a specific situation in my DB