Discussion :

[Rifton007]

Bonjour à tous et merci d'avance pour votre aide.

En plus du probléme ci-aprés, j'avoue que même le titre m'a posé problème.
Je le modifirais si celui-ci ne savére pas être claire.

J'ai déjà imposé une contrainte d'unicité sur les colonnes A et B.
Mais j'aimerais étendre cette contrainte même leurs valeurs sont inversé.
De sorte que si, (A = 8 et B = 7), ( A = 7 et B = 8) ne pourrait être ajouté par la suite.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
A	B	C
---------------------
8	7	17
7	8	18

Ici, la ligne 4 ne pourrait être ajouté dans ma table.
Pour info je suis sur PostgreSQL 9.6.1.

Merci à vous,

[vttman]

Bonjour Rifton007,

Je verrais bien dans ce cas la mise en place d'un trigger pour détecter une doublon "inversé " et empêcher l'insertion ?

[Arkhena]

Bonjour,

D'après la norme SQL, il n'existe que 4 types de contraintes : "unique constraint, a primary key constraint, a referential constraint, or a check constraint."
Dans votre cas, je regarderai la check constraint. (https://www.postgresql.org/docs/9.6/...eatetable.html)

and table_constraint is:

[ CONSTRAINT constraint_name ]
{ CHECK ( expression ) [ NO INHERIT ] |
UNIQUE ( column_name [, ... ] ) index_parameters |
PRIMARY KEY ( column_name [, ... ] ) index_parameters |
EXCLUDE [ USING index_method ] ( exclude_element WITH operator [, ... ] ) index_parameters [ WHERE ( predicate ) ] |
FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn [, ... ] ) ]
[ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE action ] [ ON UPDATE action ] }
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]

Bonne soirée,

Arkhena

[escartefigue]

Aucune contrainte ne permet de faire de requêtage, or là, il s'agit de vérifier que la combinaison de valeurs de deux colonnes existe de façon inversée, l'utilisation d'une contrainte n'est donc pas possible.

[Rifton007]

J'ai tout de même essayer avec un CHECK, mais je me rendais compte que c'était pas possible.
EXCLUDE peut être, mais j'ai jamais trop compris comment cela fonctionne.
Si non il reste le TRIGGER.

Merci à vous tous,

[Arkhena]

Aucune contrainte ne permet de faire de requêtage, or là, il s'agit de vérifier que la combinaison de valeurs de deux colonnes existe de façon inversée, l'utilisation d'une contrainte n'est donc pas possible.

Effectivement je n'avais pas lu la doc jusqu'au bout...:

A check constraint specified as a column constraint should reference that column's value only, while an expression appearing in a table constraint can reference multiple columns.Currently, CHECK expressions cannot contain subqueries nor refer to variables other than columns of the current row. The system column tableoid may be referenced, but not any other system column

(source: https://www.postgresql.org/docs/9.6/...eatetable.html)

Cependant, la norme SQL ne prévoit pas une telle limitation:

A table check constraint specifies a search condition. The constraint is violated if the result of the search condition is false for any row of the table (but not if it is unknown).

Et search condition est définit ainsi:

<search condition>
Function
Specify a condition that is True, False, or Unknown, depending on the value of a <boolean value expression>.
Format
<search condition> ::=
<boolean value expression>
Syntax Rules
None.
Access Rules
None.
General Rules
1) The result of the <search condition> is the result of the <boolean value expression>.
Conformance Rules
None.

Cordialement,

Arkhena

[SQLpro]

Il suffit de rajouter la contrainte CHECK(A<=B)

A +

[Arkhena]

Il suffit de rajouter la contrainte CHECK(A<=B)

Effectivement, c'était tout simple.

[vttman]

Effectivement, c'était tout simple.

Dans ce message de départ

De sorte que si, (A = 8 et B = 7), ( A = 7 et B = 8) ne pourrait être ajouté par la suite

Je vois juste et comprends ? que Rifton007 ne veut pas d'insertion du "couple" (7,8) si (8,7) a déjà été entré
maintenant si (7,8) qui est déjà présent alors ça serait (8,7) qu'il faudrait rejeter ... d'après ma compréhension évidemment

[SQLpro]

Et si tu transpose A en B et vice versa ???

A +

[François DORIN]

Bonjour,

Je ne sais pas si sous Postgres la solution que je propose est valide, mais sous SQLServer, ça marche :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
CREATE TABLE test_unicite(
	id INT IDENTITY,
	A INT NOT NULL,
	B INT NOT NULL,
	C AS CASE WHEN A <= B THEN A ELSE B END,
	D AS CASE WHEN A <= B THEN B ELSE A END
)
 
ALTER TABLE test_unicite ADD CONSTRAINT UK_test_unicite UNIQUE(C, D);
 
INSERT INTO test_unicite(A, B) VALUES (1, 2);
INSERT INTO test_unicite(A, B) VALUES (2, 1);

Le principe est simple : j'ai rajouté 2 colonnes calculées, C et D, qui contienne pour l'une le MIN(A, B), pour l'autre MAX(A, B).
Ainsi, quelque soit l'ordre des valeurs dans A et B, C et D contiennent toujours les mêmes valeurs. Ajoute une contrainte d'unicité dessus et cela fait le boulot. A ma deuxième insertion : violation de clé unique.

[StringBuilder]

La solution de François, même si elle est plus lourde que celle de Frédéric, me séduit plus : en effet, comme ça on ne bride pas l'ordre des ID.

Par exemple, un cas concret, ce serait qu'un FILS ne peut avoir qu'un et un seul PERE. Mais surtout, un PERE ne peut pas avoir pour PERE son propre FILS.
=> Dans ce cas, imposer A < B n'est pas possible, ou alors A et B ne sont pas de réels identifiant, car cela les oblige à porter une information de chronologie (ordre de naissance par exemple).

Sinon, attention aux solutions à base de trigger : il faut s'assurer qu'en cas d'insertion par lot la règle reste vraie (si 1,2 et 2,1 sont présents dans l'ordre d'insertion par exemple).

[SQLpro]

Il n'y a pas de colonne calculées sous PostGreSQL.

A +

[alassanediakite]

Salut
Deux solutions
Avec index unique...
Table

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

 create  table tcoupleunique (idc serial primary key, a  smallint  , b smallint)

index

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

CREATE  UNIQUE  INDEX  ukcouple ON tcoupleunique  ((a+b), (a*b))

Avec exclude

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

create table tcoupleunique2 (idc serial primary key, a  smallint  , b smallint, constraint ukcouple EXCLUDE  ((a+b) with =, (a*b)  with =) )

@+

[skuatamad]

A priori on peut créer des index sur expressions :
11.6. Indexes on Expressions

On doit donc pouvoir créer ce genre d'index unique (pas de postegresql pour tester) :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

CREATE  UNIQUE  INDEX  ukcouple ON la_table (CASE WHEN A <= B THEN A ELSE B END, CASE WHEN A <= B THEN B ELSE A END)

ou

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

CREATE  UNIQUE  INDEX  ukcouple ON la_table (least(A, B), greatest(A, B))

[alassanediakite]

Salut
skuatamad
J'ai testé, la première a besoin de parenthèses pour chaque expression...

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

((CASE WHEN a <= b THEN a ELSE b END), (CASE WHEN a <= b THEN a ELSE b END))

la deuxième passe sans problème.
@+

[alassanediakite]

Salut
Après test d'insertion, je remarque qu'il y a un problème de position de a et b dans les expressions.
On doit plutôt faire...

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

((CASE WHEN a <= b THEN a ELSE b END), (CASE WHEN a <= b THEN b ELSE a END))

@+

[skuatamad]

Exact, mauvais copier coller, merci pour la correction.
Et merci pour les tests.

[Rei Ichido]

EDIT bis : ah bein en fait j'ai mis tellement de temps à poster que quelqu'un avait posté une solution concurrente, tant pis pour moi