public inbox for [email protected]
help / color / mirror / Atom feedRestrictions for a specific situation in my DB
7+ messages / 4 participants
[nested] [flat]
* Restrictions for a specific situation in my DB
@ 2020-05-11 22:33 JORGE MALDONADO <[email protected]>
0 siblings, 4 replies; 7+ messages in thread
From: JORGE MALDONADO @ 2020-05-11 22:33 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Restrictions for a specific situation in my DB
@ 2020-05-11 22:46 Bzzzz <[email protected]>
parent: JORGE MALDONADO <[email protected]>
3 siblings, 0 replies; 7+ messages in thread
From: Bzzzz @ 2020-05-11 22:46 UTC (permalink / raw)
To: [email protected]
On Mon, 11 May 2020 17:33:25 -0500
JORGE MALDONADO <[email protected]> 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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Restrictions for a specific situation in my DB
@ 2020-05-11 22:49 Bzzzz <[email protected]>
parent: JORGE MALDONADO <[email protected]>
3 siblings, 0 replies; 7+ messages in thread
From: Bzzzz @ 2020-05-11 22:49 UTC (permalink / raw)
To: [email protected]
On Mon, 11 May 2020 17:33:25 -0500
JORGE MALDONADO <[email protected]> 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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Restrictions for a specific situation in my DB
@ 2020-05-11 23:08 David G. Johnston <[email protected]>
parent: JORGE MALDONADO <[email protected]>
3 siblings, 0 replies; 7+ messages in thread
From: David G. Johnston @ 2020-05-11 23:08 UTC (permalink / raw)
To: JORGE MALDONADO <[email protected]>; +Cc: pgsql-novice
On Mon, May 11, 2020 at 3:33 PM JORGE MALDONADO <[email protected]>
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.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Restrictions for a specific situation in my DB
@ 2020-05-12 15:25 Laurenz Albe <[email protected]>
parent: JORGE MALDONADO <[email protected]>
3 siblings, 1 reply; 7+ messages in thread
From: Laurenz Albe @ 2020-05-12 15:25 UTC (permalink / raw)
To: JORGE MALDONADO <[email protected]>; pgsql-novice
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Restrictions for a specific situation in my DB
@ 2020-05-13 14:10 JORGE MALDONADO <[email protected]>
parent: Laurenz Albe <[email protected]>
0 siblings, 1 reply; 7+ messages in thread
From: JORGE MALDONADO @ 2020-05-13 14:10 UTC (permalink / raw)
To: pgsql-novice
*"You should always CC the list."*
I am sorry for that. I will take it into consideration from now on.
*"I have never heard of a "restriction" as a technical concept in
databases,so I cannot answer that."*
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.
With respect,
Jorge Maldonado
On Tue, May 12, 2020 at 10:46 PM Laurenz Albe <[email protected]>
wrote:
> On Tue, 2020-05-12 at 18:27 -0500, JORGE MALDONADO wrote:
>
> You should always CC the list.
>
> > Does it has to be a UNIQUE INDEX?
>
> If you want to avoid duplicates, yes.
>
> > What about a RESTRICTION? I understand it also creates a UNIQUE INDEX
> behind the scenes.
> > Is a RESTRICTION not supported because of the LEAST and GREATEST
> expressions?
> > If it is, what is a reason to use a UNIQUE INDEX instead of a
> RESTRICTION?
> > I just want to fully understand the concept.
>
> I have never heard of a "restriction" as a technical concept in databases,
> so I cannot answer that.
>
> Yours,
> Laurenz Albe
> --
> +43-670-6056265
> Cybertec Schönig & Schönig GmbH
> Gröhrmühlgasse 26, A-2700 Wiener Neustadt
> Web: https://www.cybertec-postgresql.com
>
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Restrictions for a specific situation in my DB
@ 2020-05-13 20:26 Laurenz Albe <[email protected]>
parent: JORGE MALDONADO <[email protected]>
0 siblings, 0 replies; 7+ messages in thread
From: Laurenz Albe @ 2020-05-13 20:26 UTC (permalink / raw)
To: JORGE MALDONADO <[email protected]>; pgsql-novice
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
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2020-05-13 20:26 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-05-11 22:33 Restrictions for a specific situation in my DB JORGE MALDONADO <[email protected]>
2020-05-11 22:46 ` Bzzzz <[email protected]>
2020-05-11 22:49 ` Bzzzz <[email protected]>
2020-05-11 23:08 ` David G. Johnston <[email protected]>
2020-05-12 15:25 ` Laurenz Albe <[email protected]>
2020-05-13 14:10 ` JORGE MALDONADO <[email protected]>
2020-05-13 20:26 ` Laurenz Albe <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox