Restrictions for a specific situation in my DB

From: JORGE MALDONADO <jorgemal1960(at)gmail(dot)com>
To: pgsql-novice <pgsql-novice(at)postgresql(dot)org>
Subject: Restrictions for a specific situation in my DB
Date: 2020-05-11 22:33:25
Message-ID: CAAY=A79am3Cndk6DNPKA6Z0286YYxF9eYKWR=fUic-CEPjQntw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

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?

Respectfully,
Jorge Maldonado

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Bzzzz 2020-05-11 22:46:24 Re: Restrictions for a specific situation in my DB
Previous Message Alvaro Herrera 2020-05-05 20:32:29 Re: Unique Constraint vs Unique Index