Re: Restrictions for a specific situation in my DB

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 independent indexes.

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