[PostgreSQL] Contrainte d'unicite d'une paire de colonnes avec ordre indifferent

Le Mon, 12 Mar 2007 20:28:32 +0100, Pascal Hambourg a écrit :

Je débute en SQL en général et PostgreSQL en particulier. Dans une table
contenant des "paires", c'est-à-dire deux colonnes de même type dont
l'ordre est indifférent pour l'application ({a, b} est équivalent à {b,
a}), comment créer une contrainte d'unicité sur ces deux colonnes
empêchant l'insertion de {a, b} si {b, a} existe déjà ?

Ca ne me paraît pas trivial cette histoire :-)
Je pencherais plutôt pour un déclencheur.
Sinon pas très propre, ni très performant probablement :

create table paire (a text, b text);
create unique index idx_paire on paire ( ( CASE WHEN a < b THEN a || '*' || b ELSE b || '*' || a END ) );

(en prenant bien soin de remplacer les deux * par un caractère qui ne
peut apparaître ni dans a ni dans b, sinon grosse katastrophe)

Puis:
insert into paire values ('toto','titi');
INSERT 3591342 1
insert into paire values ('toto','titi');
ERROR: duplicate key violates unique constraint "idx_paire"
insert into paire values ('titi','toto');
ERROR: duplicate key violates unique constraint "idx_paire"


Il ne (me) semble pas qu'on puisse avoir la même clause (CASE WHEN ...)
en tant que directement contrainte d'unicité de table.

--
Patrick Mevzek . . . . . . . . . . . . . . Dot and Co
<http://www.dotandco.net/> <http://www.dotandco.com/>
Dépêches sur le nommage <news://news.dotandco.net/dotandco.info.news>

Patrick Mevzek a écrit :

Je débute en SQL en général et PostgreSQL en particulier. Dans une table
contenant des "paires", c'est-à-dire deux colonnes de même type dont
l'ordre est indifférent pour l'application ({a, b} est équivalent à {b,
a}), comment créer une contrainte d'unicité sur ces deux colonnes
empêchant l'insertion de {a, b} si {b, a} existe déjà ?

Ca ne me paraît pas trivial cette histoire :-)
Je pencherais plutôt pour un déclencheur.

Hum, comme je l'ai dit je débute et je ne sais même pas ce que c'est,
d'autant plus que ma doc est en anglais. Un trigger ?

Sinon pas très propre, ni très performant probablement :

create table paire (a text, b text);
create unique index idx_paire on paire ( ( CASE WHEN a < b THEN a || '*' || b ELSE b || '*' || a END ) );

Pas bête, l'idée de créer une valeur unique indépendante de l'ordre à
partir des deux noms. Merci !

Il ne (me) semble pas qu'on puisse avoir la même clause (CASE WHEN ...)
en tant que directement contrainte d'unicité de table.

Est-ce que ce genre de vérification serait faisable avec une "subquery"
(sous-requête ?) dans une contrainte de type CHECK ?

Le Wed, 14 Mar 2007 12:20:59 +0100, Pascal Hambourg a écrit :

Ca ne me paraît pas trivial cette histoire :-)
Je pencherais plutôt pour un déclencheur.

Hum, comme je l'ai dit je débute et je ne sais même pas ce que c'est,
d'autant plus que ma doc est en anglais. Un trigger ?

Oui.
Qui pourrait faire un select dans les deux colonnes voir si les valeurs si
trouvent. A noter que ce ne sera pas très performant si les colonnes ne
sont pas indexées. Et même si elles le sont, cela ne sera pas
nécessairement très performant.

Il ne (me) semble pas qu'on puisse avoir la même clause (CASE WHEN
...) en tant que directement contrainte d'unicité de table.

Est-ce que ce genre de vérification serait faisable avec une "subquery"
(sous-requête ?) dans une contrainte de type CHECK ?

Tiré de
http://www.postgresql.org/docs/8.2/interactive/sql-createtable.html :

Currently, CHECK expressions cannot contain subqueries nor refer to
variables other than columns of the current row.

Donc, non.

--
Patrick Mevzek . . . . . . . . . . . . . . Dot and Co
<http://www.dotandco.net/> <http://www.dotandco.com/>
Dépêches sur le nommage <news://news.dotandco.net/dotandco.info.news>

Pascal Hambourg a écrit :

Salut,

Je débute en SQL en général et PostgreSQL en particulier. Dans une table
contenant des "paires", c'est-à-dire deux colonnes de même type dont
l'ordre est indifférent pour l'application ({a, b} est équivalent à {b,
a}), comment créer une contrainte d'unicité sur ces deux colonnes
empêchant l'insertion de {a, b} si {b, a} existe déjà ?

Un exemple pratique si ce n'est pas assez parlant :
(ville1, ville2, distance)
Les colonnes ville1 et ville2 contiennent des noms de villes dont
l'ordre est indifférent. Je souhaite par exemple empêcher ('Toulouse',
'Lyon', xx) non seulement si ('Toulouse', 'Lyon', yy) existe déjà
(facile) mais aussi si ('Lyon', 'Toulouse', yy) existe déjà.

C'est votre modèle de données qui est faux.

En effet la ville doit être dans ce cas une entité à part et votre table
des distances doit faire référence à l'id de la ville :

Exemple :

CREATE TABLE T_VILLE_VIL
(VIL_ID INT NOT NULL PRIMARY KEY,
VIL_VILLE VARCHAR(64) NOT NULL UNIQUE)

INSERT INTO T_VILLE_VIL VALUES (1, 'Toulouse')
INSERT INTO T_VILLE_VIL VALUES (2, 'Lyon')
INSERT INTO T_VILLE_VIL VALUES (3, 'Paris')

CREATE TABLE T_DISTANCE_VILLE_DTV
(DTV_ID INT NOT NULL PRIMARY KEY,
VIL_ID1 INT NOT NULL
CONSTRAINT PK_VILID1 FOREIGN KEY
REFERENCES T_VILLE_VIL (VIL_ID),
VIL_ID2 INT NOT NULL
CONSTRAINT PK_VILID2 FOREIGN KEY
REFERENCES T_VILLE_VIL (VIL_ID),
DTV_DISTANCE FLOAT,
CONSTRAINT CK_UNIVIL CHECK (VIL_ID1 < VIL_ID2))

-- insertion toulouse lyon
INSERT INTO T_DISTANCE_VILLE_DTV VALUES (1, 1, 2, 450)
-- insertion lyon toulouse
INSERT INTO T_DISTANCE_VILLE_DTV VALUES (2, 2, 1, 450)
/* Conflit entre l'instruction INSERT et la contrainte TABLE CHECK
'CK_UNIVIL'.
Le conflit est survenu dans la base de données 'DB_DIST',
table 'T_DISTANCE_VILLE_DTV'. */

-- insertion toulouse paris
INSERT INTO T_DISTANCE_VILLE_DTV VALUES (3, 1, 3, 450)
-- insertion paris toulouse
INSERT INTO T_DISTANCE_VILLE_DTV VALUES (4, 3, 1, 450)
/* Conflit entre l'instruction INSERT et la contrainte TABLE CHECK
'CK_UNIVIL'.
Le conflit est survenu dans la base de données 'DB_DIST',
table 'T_DISTANCE_VILLE_DTV'. */

-- insertion lyon paris
INSERT INTO T_DISTANCE_VILLE_DTV VALUES (5, 2, 3, 450)
-- insertion paris lyon
INSERT INTO T_DISTANCE_VILLE_DTV VALUES (6, 3, 2, 450)
/* Conflit entre l'instruction INSERT et la contrainte TABLE CHECK
'CK_UNIVIL'.
Le conflit est survenu dans la base de données 'DB_DIST',
table 'T_DISTANCE_VILLE_DTV'. */

-- essais sur paris paris
INSERT INTO T_DISTANCE_VILLE_DTV VALUES (7, 3, 3, 450)
/* Conflit entre l'instruction INSERT et la contrainte TABLE CHECK
'CK_UNIVIL'.
Le conflit est survenu dans la base de données 'DB_DIST',
table 'T_DISTANCE_VILLE_DTV'. */

CQFD

A +


--
Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************

PS : pour vous faciliter la vie, rien ne vous empêche de rajouter une
vue avec les distances explices dans les deux sens :

CREATE VIEW
V_DISTANCE_VILLE_DTV
AS
(SELECT DTV_ID, V1.VIL_VILLE AS VILLE_DEPART,
V2.VIL_VILLE AS VILLE_ARRIVEE, DTV_DISTANCE
FROM T_DISTANCE_VILLE_DTV D
INNER JOIN T_VILLE_VIL V1
ON D.VIL_ID1 = V1.VIL_ID
INNER JOIN T_VILLE_VIL V2
ON D.VIL_ID2 = V2.VIL_ID
UNION ALL
SELECT DTV_ID, V2.VIL_VILLE AS VILLE_DEPART,
V1.VIL_VILLE AS VILLE_ARRIVEE, DTV_DISTANCE
FROM T_DISTANCE_VILLE_DTV D
INNER JOIN T_VILLE_VIL V1
ON D.VIL_ID1 = V1.VIL_ID
INNER JOIN T_VILLE_VIL V2
ON D.VIL_ID2 = V2.VIL_ID)

A +

Fred Brouard - SQLpro a écrit :

Pascal Hambourg a écrit :

Salut,

Je débute en SQL en général et PostgreSQL en particulier. Dans une
table contenant des "paires", c'est-à-dire deux colonnes de même type
dont l'ordre est indifférent pour l'application ({a, b} est équivalent
à {b, a}), comment créer une contrainte d'unicité sur ces deux
colonnes empêchant l'insertion de {a, b} si {b, a} existe déjà ?

Un exemple pratique si ce n'est pas assez parlant :
(ville1, ville2, distance)
Les colonnes ville1 et ville2 contiennent des noms de villes dont
l'ordre est indifférent. Je souhaite par exemple empêcher ('Toulouse',
'Lyon', xx) non seulement si ('Toulouse', 'Lyon', yy) existe déjà
(facile) mais aussi si ('Lyon', 'Toulouse', yy) existe déjà.

C'est votre modèle de données qui est faux.

En effet la ville doit être dans ce cas une entité à part et votre table
des distances doit faire référence à l'id de la ville :

Exemple :

CREATE TABLE T_VILLE_VIL
(VIL_ID INT NOT NULL PRIMARY KEY,
VIL_VILLE VARCHAR(64) NOT NULL UNIQUE)

INSERT INTO T_VILLE_VIL VALUES (1, 'Toulouse')
INSERT INTO T_VILLE_VIL VALUES (2, 'Lyon')
INSERT INTO T_VILLE_VIL VALUES (3, 'Paris')

CREATE TABLE T_DISTANCE_VILLE_DTV
(DTV_ID INT NOT NULL PRIMARY KEY,
VIL_ID1 INT NOT NULL
CONSTRAINT PK_VILID1 FOREIGN KEY
REFERENCES T_VILLE_VIL (VIL_ID),
VIL_ID2 INT NOT NULL
CONSTRAINT PK_VILID2 FOREIGN KEY
REFERENCES T_VILLE_VIL (VIL_ID),
DTV_DISTANCE FLOAT,
CONSTRAINT CK_UNIVIL CHECK (VIL_ID1 < VIL_ID2))

-- insertion toulouse lyon
INSERT INTO T_DISTANCE_VILLE_DTV VALUES (1, 1, 2, 450)
-- insertion lyon toulouse
INSERT INTO T_DISTANCE_VILLE_DTV VALUES (2, 2, 1, 450)
/* Conflit entre l'instruction INSERT et la contrainte TABLE CHECK
'CK_UNIVIL'.
Le conflit est survenu dans la base de données 'DB_DIST',
table 'T_DISTANCE_VILLE_DTV'. */

-- insertion toulouse paris
INSERT INTO T_DISTANCE_VILLE_DTV VALUES (3, 1, 3, 450)
-- insertion paris toulouse
INSERT INTO T_DISTANCE_VILLE_DTV VALUES (4, 3, 1, 450)
/* Conflit entre l'instruction INSERT et la contrainte TABLE CHECK
'CK_UNIVIL'.
Le conflit est survenu dans la base de données 'DB_DIST',
table 'T_DISTANCE_VILLE_DTV'. */

-- insertion lyon paris
INSERT INTO T_DISTANCE_VILLE_DTV VALUES (5, 2, 3, 450)
-- insertion paris lyon
INSERT INTO T_DISTANCE_VILLE_DTV VALUES (6, 3, 2, 450)
/* Conflit entre l'instruction INSERT et la contrainte TABLE CHECK
'CK_UNIVIL'.
Le conflit est survenu dans la base de données 'DB_DIST',
table 'T_DISTANCE_VILLE_DTV'. */

-- essais sur paris paris
INSERT INTO T_DISTANCE_VILLE_DTV VALUES (7, 3, 3, 450)
/* Conflit entre l'instruction INSERT et la contrainte TABLE CHECK
'CK_UNIVIL'.
Le conflit est survenu dans la base de données 'DB_DIST',
table 'T_DISTANCE_VILLE_DTV'. */

CQFD

A +

--
Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************