Unique constrain zieht nicht

Hi,

Ich verwende Django mit Postgresql und irgendwie funktioniert ein Unique-constrain nicht… Das Merkwürdige an der Sache ist bloß das die generierte Tabelle auf mich richtig wirkt. Wenn ich mich nicht irre sollte ja diese Tabelle nicht möglich sein:

Id permission_type source_group_id source_profile_id target_group_id target_profile_id
1 2 2 null null 14
2 2 2 null null 14

Order habe ich ein knick in der Logik? Hier die Tabelle:

-- auto-generated definition
create table permission
(
    id serial not null
        constraint permission_pkey
            primary key,
    permission_type   smallint not null,
    source_group_id   integer
        constraint permission_source_group_id_ad885e0f_fk_usc
            references group
            deferrable initially deferred,
    source_profile_id integer
        constraint permission_source_profile_id_101e9831_fk_usc
            references profile
            deferrable initially deferred,
    target_group_id   integer
        constraint permission_target_group_id_22a58b31_fk_usc
            references group
            deferrable initially deferred,
    target_profile_id integer
        constraint permission_target_profile_id_f3fc37f3_fk_usc
            references profile
            deferrable initially deferred,
    constraint "constrainError.permissionExists"
        unique (source_profile_id, source_group_id, target_profile_id, target_group_id),
    constraint "constrainError.onlyOneSource"
        check (((source_group_id IS NOT NULL) AND (source_profile_id IS NULL)) OR
               ((source_group_id IS NULL) AND (source_profile_id IS NOT NULL))),
    constraint "constrainError.onlyOneTarget"
        check (((target_group_id IS NOT NULL) AND (target_profile_id IS NULL)) OR
               ((target_group_id IS NULL) AND (target_profile_id IS NOT NULL))),
    constraint "constrainError.groupsCantBeSupervisor"
        check ((source_group_id IS NULL) OR ((permission_type > 1) AND (source_group_id IS NOT NULL)))
);

alter table permission
    owner to alamanda;

create unique index "constrainError.onlyOneSupervisorPerGroup"
    on permission (target_group_id)
    where (permission_type = 1);

create unique index "constrainError.onlyOneSupervisorPerProfile"
    on permission (target_profile_id)
    where (permission_type = 1);

create index permission_source_group_id_ad885e0f
    on permission (source_group_id);

create index permission_source_profile_id_101e9831
    on permission (source_profile_id);

create index permission_target_group_id_22a58b31
    on permission (target_group_id);

create index permission_target_profile_id_f3fc37f3
    on permission (target_profile_id);

Grüße, Alamanda

Du hast NULL Werte in den Spalten.

Sobald eine der Spalten eines unique constraints (or index) NULL ist, dürfen beliebig viele andere Datensätze (auch mit NULL Werten) eingefügt werden.

Unabhängig von Deinem Problem: die “quoted identifier” mit " solltest Du Dir ganz schnell wieder abgewöhnen. Langfristig machen die einfach viel mehr Probleme als sie Wert sind.

mich irritiert der Tabellenname group. Das ist ein reserviertes Wort.

Ansonsten: siehe @castorp

Andreas

Nachdem Du ja immer nur eine der Spalten als NULL zulässt, könnte man das von Dir gewünschte Verhalten über einen unique index erreichen:

create unique index permission_unique_group_target 
   on permission ( (coalesce(source_group_id, source_profile_id)), 
                   (coalesce(target_profile_id, target_group_id)) );

Ein unique constraint darf keine Expressions enhalten, deswegen der Index.

1 „Gefällt mir“

Nachtrag:

Check constraints der Art “Nur eine von mehreren Spalten darf NULL sein”, mache ich gerne mit der Funktion num_nonnulls - ich finde das leserlicher:

constraint "constrainError.onlyOneSource"
    check (num_nonnulls(source_group_id, source_profile_id) = 1),
constraint "constrainError.onlyOneTarget"
    check (num_nonnulls(target_group_id, target_profile_id) = 1),
1 „Gefällt mir“

Erstmal danke für die Antworten.

@akretschmer Ja ich habe den Tabellennamen vor dem Einfügen geändert, davor steht noch ein wenig firlefanz den das Django ORM da hin geklatscht hat.

@castorp ich habe mir die coalesce() Funktion mal angeguckt und wenn ich das so wie du geschrieben hast implementiere löst das nicht wirklich mein Problem. Die funktion gibt den ersten Parameter wieder der nicht NULL ist zurück. Das problem was dadurch entsteht ist, dass folgende Konstellation den Constrain auslößt:

Id permission_type source_group_id source_profile_id target_group_id target_profile_id
1 2 2 null null 14
2 2 2 null 14 null

Die einzige Lösung die ich jetzt gerade sehe ist das ich 4 Constrains baue die jeden möglichen Fall abbilden, oder hat jemand eine bessere Idee?

Ah, stimmt. An die Kombination hatte ich nicht gedacht.

Aber zwei müssten reichen:

create unique index unique_source 
  on permission ( (coalesce(source_group_id, source_profile_id)) );
create unique index unique_target 
  on permission ( (coalesce(source_group_id, source_profile_id)) );

@castorp hab gerade selbst noch ein wenig rum getüfftelt und folgende lösung gebaut:

create unique index permission_unique_group_target
    on permission (coalesce(source_group_id, -1), coalesce(source_profile_id, -1),
                   coalesce(target_group_id, -1), coalesce(target_profile_id, -1));

-1 geht komplett klar weil die Felder IDs sind und eh nie Negativ werden… Aber sag mir wenn meine Lösung dähmlich ist dann versuche ich deine nochmal.

Nein, gar nicht dämlich - hätte ich auch drauf kommen können :wink:

Alride dann lass ich das so. Danke für die schnelle Antwort!