Обсуждение: Restrictions for a specific situation in my DB

On Mon, 11 May 2020 17:33:25 -0500
JORGE MALDONADO <jorgemal1960@gmail.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

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

On Mon, 2020-05-11 at 17:33 -0500, JORGE MALDONADO wrote:
> 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
independentindexes.
 

Yes, if you ever plan to update primary keys or (more likely)
delete rows in the referenced tables, such indexes are a good idea
for performance reasons.

Not that if you already have an index on "(m1, m2)", you don't need
an additional index on "m1" alone (but you still need an index on "m2").

> 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 would do it like that:

CREATE UNIQUE INDEX ON atable (LEAST(m1, m2), GREATEST(m1, m2));
ALTER TABLE atable ADD CHECK (m1 <> m2);

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

On Wed, 2020-05-13 at 09:10 -0500, JORGE MALDONADO wrote:
> What I meant was "CONSTRAINT". I understand that, in PostgreSQL, I can define "UNIQUE INDEX"
> and "UNIQUE CONSTRAINT" and that a "UNIQUE CONSTRAINT" creates a "UNIQUE INDEX" behind the scenes.
> 
> So my question is: 
> Can a "UNIQUE CONSTRAINT" be used instead of a "UNIQUE INDEX"?
> 
> I searched the web for an answer and found that expressions like LEAST and GREATEST are
> not allowed in a UNIQUE CONSTRAINT but they can be used in a UNIQUE INDEX. I also tested
> directly in a table and I could verify it. So, a UNIQUE INDEX is the choice to include
> LEAST and GREATEST.

You got it.
A unique constraint can only be defined on columns, not on expressions.
But unique indexes can have an expression as key.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com