Ein unique constraint auf Tabelle mit Null-Werten

Hallo zusammen,

ich suche eine Möglichkeit für eine Tabelle, in deren Spalten NULL-Werte enthalten sein dürfen, so etwas wie einen UNIQUE constraint bezogen auf alle Spalten zu definieren.

Zum Beispiel :

create table t_uni (
    a integer,
    b integer,
    c integer
);
wtest=# select * from t_uni;
 a | b | c 
---+---+---
 1 |   |  
   | 2 |  
 1 | 2 |  
 1 | 2 | 3

Der Constraint sollte zu einer Exception führen, wenn versucht wird
(1,null,null), (1,2,3) oder (null,2, null) einzufügen. NULL values sollen also
als normale Werte, die man vergleichen kann, behandelt werden.

Die Logik ist die von ‘is not distinct from’

wtest=# select case when (null,2,null) is not distinct from (null,2,null) then '-> raise exception'  else '-> go on' end;
        case        
--------------------
 -> raise exception
(1 row)

wtest=# select case when (null,2,null) is not distinct from (null,2,3) then '-> raise exception'  else '-> go on' end;
   case   
----------
 -> go on
(1 row)

Ein ‘PRIMARY KEY (a,b,c)’ funktioniert nicht, weil NULL-Werte nicht erlaubt sind.
Ein ‘UNIQUE (a,b,c)’ funktioniert interessanterweise nicht weil, z.Bsp.
(null,2,null) problemlos mehrfach eingefügt werden kann.

Ich würde diesen Constraint jetzt als Trigger implementieren.

Geht’s nicht irgendwie einfacher ???



test=# create unique index idx_t_uni on t_uni(coalesce(a,-1),coalesce(b,-1),coalesce(c,-1));
CREATE INDEX
test=*# commit;
COMMIT
test=# insert into t_uni values (1,null,null);
ERROR:  duplicate key value violates unique constraint "idx_t_uni"
test=!# rollback;
ROLLBACK
test=# insert into t_uni values (1,2,3);
ERROR:  duplicate key value violates unique constraint "idx_t_uni"
test=!# rollback;
ROLLBACK
test=#

In der Annahme, daß -1 ansonsten kein erlaubter Wert ist.

Andreas

Hallo Andreas,

wenn man hier eine Frage stellt, kann man einfach nicht in Ruhe Kaffee trinken gehen ;-). Danke für die Antwort. Supie -in die Richtung hatte ich überhaupt nicht gedacht.

Die Lösung lebt davon, dass man den NULL-Wert durch einen Wert, der ansonsten nicht vorkommt, ersetzt.

Sollte das nicht möglich sein (ist bei uns wahrscheinlich so), komme ich um eine Triggerfunktion nicht rum.

Wenn man die Wahl zwischen Indextabelle und Trigger hat, was würde
ein erfahrener DB-Mensch denn bevorzugen ?

Ciao

Wolfgang

der constraint ist ja ein trigger + einem index …

Na, dann greife ich mal tiefer in die Trick-Kiste:

test=# drop INDEX idx_t_uni ;
DROP INDEX
test=*# create unique index idx_t_uni on t_uni(coalesce(a::text,'verboten'),coalesce(b::text,'verboten'),coalesce(c::text,'verboten'));
CREATE INDEX
test=*# insert into t_uni values (1,null,null);
ERROR:  duplicate key value violates unique constraint "idx_t_uni"
test=!#

Andreas

Ok, dann spricht ja nix dagegen :-).

Wenn Du dafür keinen gescheiten Index hast, wird der Trigger sehr teuer. Zumindest bei Tabellen mit dem Attribut ‘GROß’. Ist die Frage damit beantwortet?

Andreas

nunja - wie ak schon sagt - ohne index wirds teuer - und das constraint objekt spart dir die DDL orgie - wie es dahinter dann technisch realisiert wird ist schoen zu wissen.

ich wuerde aber nochmal drueber nachdenken, ob ihr die null-werte wirklich braucht - denn mit dem constraint bringt das (nach meiner auffassung) ja keine vorteile mehr - da kannst auch gleich not null felder nehmen und 0 reinschreiben …

der vorteil waere dann ein wesentlich einfacherer constraint, dessen impliziter index (der dabei erzeugt wird) auch fuer andere, sehr wahrscheinlich vorkommende abfragen verwendet werden kann.

denn der coalesce index kann von PG nur angewendet werden, wenn deine abfragen den coalesce ausdruck beinhalten - was ich mal so fuer sehr unwahrscheinlich halte

NULL != 0

denn der coalesce index kann von PG nur angewendet werden, wenn deine abfragen den coalesce ausdruck beinhalten - was ich mal so fuer sehr unwahrscheinlich halte

Von einer Verwendung für Abfragen hat niemand was gesagt. Im übrigen kann man pro Tabelle sogar mehr als nur einen Index haben…

Andreas

Natürlich ist 0 <> Null … darum geht es ja … er war der -1 ja auch nicht abgeneigt - vielleicht ist 0 auch ein “freier Wert” der praktisch nicht erzeugt wird. Und nach seiner Anforderung würde ja der Vorteil der Wiederholbarkeit von NULL im Constraint verpuffen - daher die Überlegung

Und selbstverständlich kann man viele Indizes auf die Tabelle drücken - man muss aber nicht - v.a. wenn ein Constraint einen geeigneten Index bereits impliziert.

Solln ja nur Anregungen sein

Wenn es Dir nicht möglich ist, den Sinn eines Textes beim ersten Lesen vollständig zu erfassen, warum versuchst Du es dann nicht ein 2., 3. oder 947. mal?

test=# create table symbiont (a int, b int);
CREATE TABLE
test=*# insert into symbiont values (100,0);
INSERT 0 1
test=*# insert into symbiont values (100,NULL);
INSERT 0 1
test=*# select avg(a), avg(b) from symbiont ;
         avg          |          avg
----------------------+------------------------
 100.0000000000000000 | 0.00000000000000000000
(1 row)

Andreas

Wir haben hier die Antworten soweit verstanden (immerhin ;-)).

Vielleicht zur Erläuterung :
Die Integers waren ein Beispiel.
Wir dürfen hier auf der grünen Wiese mit einem DB-Design starten und ein Teil der Aufgabe ist die Verwaltung von Adressen (in einem grösseren Kontext). Und Adressen sind eklig, weil als Adresse vieles möglich ist.

Bei uns besteht die Adresse momentan aus Postleitzahl, Stadt, Straße, Hausnummer.
In dem Kontext sind bis auf Postleitzahl immer auch NULL-Values möglich.
Trotzdem wollen wir eindeutige Adressen.

Die Idee ist momentan (dank eurer Hilfe) für alle Spalten denTyp Text zu nehmen und als default den Leerstring und dann die Index-Geschichte. Das ganze noch etwas versüßt mit Regular-Expression, dann sollte etwas halbwegs brauchbares dabei rauskommen.

Nochmals Danke.

Unter der Beschreibung “CREATE TABLE” findet man auch den “CHECK CONSTRAINT” - CHECK (expression) - , der je nach Ergebnis eine Error exception auslöst.



mfg

fristo

Das Beispiel war etwas, ähm, schlecht.

test=# create table symbiont (a int, b int);
CREATE TABLE
test=*# insert into symbiont values (100,0);
INSERT 0 1
test=*# insert into symbiont values (100,0);
INSERT 0 1
test=*# insert into symbiont values (100,100);
INSERT 0 1
test=*# select avg(a), avg(b) from symbiont ;
         avg          |         avg
----------------------+---------------------
 100.0000000000000000 | 33.3333333333333333
(1 row)

test=*# rollback;
ROLLBACK
test=# create table symbiont (a int, b int);
CREATE TABLE
test=*# insert into symbiont values (100,NULL);
INSERT 0 1
test=*# insert into symbiont values (100,NULL);
INSERT 0 1
test=*# insert into symbiont values (100,100);
INSERT 0 1
test=*# select avg(a), avg(b) from symbiont ;
         avg          |         avg
----------------------+----------------------
 100.0000000000000000 | 100.0000000000000000
(1 row)

Ist aber auch egal, immerhin hat der Fragesteller ja verstanden. Wowereit.

Andreas

und ist auch so kein problem … aber das ist sicher wieder so eine sache die nur du und Co verstehst lach

vor allem weil dein beispiel ja so ultra relevant ist, wenn das NULL sowieso nicht mehrfach vorkommt - dank dem netten constraint …

also - erst denken, dann meckern

siesch …

wenn das problem zu problematisch wird, dann lohnt es sich immer, sich die zielsetzung vors auge zu beppen …

so nebenbei - unique constraint auf address-daten geht sowieso oft in die hose :slight_smile: da wuerde ich die energie lieber in einen volltext-index für die adresse stecken - und eine entsprechende eingabehilfe die dem operator vor dem schirm sagt, dass er da bereits “ähnliche” adress-daten hat.