From: | "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> |
---|---|
To: | JORGE MALDONADO <jorgemal1960(at)gmail(dot)com> |
Cc: | pgsql-novice <pgsql-novice(at)postgresql(dot)org> |
Subject: | Re: Restrictions for a specific situation in my DB |
Date: | 2020-05-11 23:08:57 |
Message-ID: | CAKFQuwaw=yFb7FauOH=6cw4USmb-o7Z6vGQwr10qUrDgRU6NVA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
On Mon, May 11, 2020 at 3:33 PM JORGE MALDONADO <jorgemal1960(at)gmail(dot)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.
From | Date | Subject | |
---|---|---|---|
Next Message | Laurenz Albe | 2020-05-12 15:25:26 | Re: Restrictions for a specific situation in my DB |
Previous Message | Bzzzz | 2020-05-11 22:49:38 | Re: Restrictions for a specific situation in my DB |