Thread
-
Restrictions for a specific situation in my DB
JORGE MALDONADO <jorgemal1960@gmail.com> — 2020-05-11T22:33:25Z
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 -
Re: Restrictions for a specific situation in my DB
Bzzzz <lazyvirus@gmx.com> — 2020-05-11T22:46:24Z
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
-
Re: Restrictions for a specific situation in my DB
Bzzzz <lazyvirus@gmx.com> — 2020-05-11T22:49:38Z
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
-
Re: Restrictions for a specific situation in my DB
David G. Johnston <david.g.johnston@gmail.com> — 2020-05-11T23:08:57Z
On Mon, May 11, 2020 at 3:33 PM 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. > 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.
-
Re: Restrictions for a specific situation in my DB
Laurenz Albe <laurenz.albe@cybertec.at> — 2020-05-12T15:25:26Z
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
-
Re: Restrictions for a specific situation in my DB
JORGE MALDONADO <jorgemal1960@gmail.com> — 2020-05-13T14:10:03Z
*"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 <laurenz.albe@cybertec.at> 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 > >
-
Re: Restrictions for a specific situation in my DB
Laurenz Albe <laurenz.albe@cybertec.at> — 2020-05-13T20:26:53Z
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