Anfängerfrage zu Datenkonsistenz: Constraint, Trigger oder ganz anders?

[fraow]

Liebe Leute,

ich habe, vereinfacht gesprochen, drei Tabellen:

• nutzer (PK: id_nutzer)
• einkaeufer (PK: id_einkaeufer, FK: id_nutzer)
• lose (PK: id_los, FK: id_nutzer, id_einkaeufer)

Ein Nutzer kann mehrere Einkäufer anlegen, die haben dann seine Nutzer-ID. Ein Einkaeufer kann auch id_nutzer = NULL haben und ist dann für alle Nutzer verwendbar.

Wenn ein Los angelegt wird, soll sichergestellt werden, dass id_nutzer und id_einkaeufer auch als Wertepaar zusammen in der Einkäufer-Tabelle vorkommen. Ich weiß, dass der id_nutzer-Fremdschlüssel in der Lose-Tabelle hinderlich ist, aber da die Nutzer-ID in der Einkäufer-Tabelle NULL werden kann, könnte man den Besitzer des Los-Datensatzes gar nicht mehr feststellen. In der Lose-Tabelle kann id_nutzer logischerweise auch = NULL sein.

Wie stelle ich am einfachsten sicher, dass in Lose nur ein gültiges Wertepaar aus id_einkaeufer und id_nutzer steht? Oder ist das so dumm, dass ich die Nutzer-ID auf jeden Fall aus lose rausnehmen sollte?

 

[akretschmer]

Liebe Leute,

ich habe, vereinfacht gesprochen, drei Tabellen:

• nutzer (PK: id_nutzer)
• einkaeufer (PK: id_einkaeufer, FK: id_nutzer)
• lose (PK: id_los, FK: id_nutzer, id_einkaeufer)

Ein Nutzer kann mehrere Einkäufer anlegen, die haben dann seine Nutzer-ID. Ein Einkaeufer kann auch id_nutzer = NULL haben und ist dann für alle Nutzer verwendbar.

Wenn ein Los angelegt wird, soll sichergestellt werden, dass id_nutzer und id_einkaeufer auch als Wertepaar zusammen in der Einkäufer-Tabelle vorkommen. Ich weiß, dass der id_nutzer-Fremdschlüssel in der Lose-Tabelle hinderlich ist, aber da die Nutzer-ID in der Einkäufer-Tabelle NULL werden kann, könnte man den Besitzer des Los-Datensatzes gar nicht mehr feststellen. In der Lose-Tabelle kann id_nutzer logischerweise auch = NULL sein.

Wie stelle ich am einfachsten sicher, dass in Lose nur ein gültiges Wertepaar aus id_einkaeufer und id_nutzer steht? Oder ist das so dumm, dass ich die Nutzer-ID auf jeden Fall aus lose rausnehmen sollte?

So?

test=# create table nutzer (id_nutzer int primary key);NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "nutzer_pkey" for table "nutzer"
CREATE TABLE
Time: 7,425 ms
test=*# create table einkaeufer (id_einkaeufer int, id_nutzer int references nutzer, primary key(id_einkaeufer, id_nutzer));NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "einkaeufer_pkey" for table "einkaeufer"
CREATE TABLE
Time: 4,983 ms
test=*# create table lose (id_los int primary key, id_nutzer int, id_einkaeufer int, foreign key(id_nutzer, id_einkaeufer) references einkaeufer);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "lose_pkey" for table "lose"
CREATE TABLE
Time: 3,244 ms

Andreas

 

[fraow]

Hallo Andreas,

vielen Dank für deine Antwort. Da aber id_nutzer NULL sein kann, bekomme ich als Antwort, wenn ich den Fremdschlüssel erstellen will "Foreign Key is incorrectly formed".
Mir ist noch was aufgefallen, was die Sache irgendwie schwieriger macht. Die Nutzer-ID bestimmt sozusagen immer den Ersteller und Nutzer des Datensatzes (bei Los wie bei Einkäufer). Ein Admin kann in beiden Tabellen einen Eintrag mit Nutzer-ID = NULL erstellen. Dann können andere Nutzer diesen Eintrag sozusagen als "Standardeintrag" nutzen (-> Standardlos oder Standardeinkäufer). Wenn jetzt z.B. ein normaler Nutzer ein Los erstellt, dass auf einen Standardeinkäufer verweist, muss als Nutzer-ID trotzdem seine ID im Los-Datensatz eingetragen sein und nicht NULL. Es muss dabei nur sichergestellt werden, dass die Einkäufer-ID auf einen seiner eigenen oder einen Standardeinkäufer verweist. Wenn der Admin ein Standardlos mit Nutzer-ID = NULL erstellt, so dürfen nur Standardeinkäufer genutzt werden. Das sind eigentlich die Einschränkungen. Ich versuch's noch mal zusammenzufassen..

Tabelle Los
- Nutzer-ID = NULL -> Einkäufer-ID darf nur von einem Standardeinkäufer mit Nutzer-ID ebenfalls = NULL kommen
- Nutzer-ID != NULL -> Einkäufer-ID darf von einem Standardeinkäufer mit Nutzer-ID = NULL oder von einem Einkäufer des Nutzers mit der selben Nutzer-ID kommen

Geht das immer noch mit Fremdschlüsseln? Kann ich da so eine Check-Constraint einbauen? Hab so viel gegoogelt gestern, aber werde einfach nicht schlauer. Die Syntax macht mir auch zu schaffen, ständig so kleine, kryptische Fehlermeldungen, die ich nicht deuten kann nutze übrigens MariaDB.

 

[akretschmer]

Hallo Andreas,

vielen Dank für deine Antwort. Da aber id_nutzer NULL sein kann, bekomme ich als Antwort, wenn ich den Fremdschlüssel erstellen will "Foreign Key is incorrectly formed".

Wieso kann die NULL sein?

Mir ist noch was aufgefallen, was die Sache irgendwie schwieriger macht. Die Nutzer-ID bestimmt sozusagen immer den Ersteller und Nutzer des Datensatzes (bei Los wie bei Einkäufer). Ein Admin kann in beiden Tabellen einen Eintrag mit Nutzer-ID = NULL erstellen.

Nein. Definiere halt die FK mit NOT NULL.

Andreas

 

[fraow]

Wenn ich dann meinetwegen, um Standardeinkäufer und -lose zu erstellen, die Nutzer-ID auf "0" und nicht NULL setze (und dazu eben so eine Art "Standardnutzer" mit 0 als ID anlege), bliebe das Problem:

Admin legt Standardeinkäufer an -> Nutzer-ID: 0, Einkäufer-ID: XX
Normaler Nutzer mit ID YY legt Los mit Standardeinkäufer an -> Nutzer-ID: YY, Einkäufer-ID: XX

Dann stimmt ja das Fremdschlüsselpaar nicht mehr. Ist aber so trotzdem richtig. Der Nutzer darf ja den Standardeinkäufer nutzen für seine Einträge.

 

[akretschmer]

Wenn ich dann meinetwegen, um Standardeinkäufer und -lose zu erstellen, die Nutzer-ID auf "0" und nicht NULL setze (und dazu eben so eine Art "Standardnutzer" mit 0 als ID anlege), bliebe das Problem:

Admin legt Standardeinkäufer an -> Nutzer-ID: 0, Einkäufer-ID: XX
Normaler Nutzer mit ID YY legt Los mit Standardeinkäufer an -> Nutzer-ID: YY, Einkäufer-ID: XX

Dann stimmt ja das Fremdschlüsselpaar nicht mehr. Ist aber so trotzdem richtig. Der Nutzer darf ja den Standardeinkäufer nutzen für seine Einträge.

Ja, und? Du kannst auch 99 oder 4711 als 'Standardnutzer' anlegen, die Interpretation ist nicht Aufgabe der DB.

 

[fraow]

Meinst du damit also, dass es, wenn ich eine Datenkonsistenz dieser Art gewährleisten möchte, Aufgabe des Clients sein muss?

 

[akretschmer]

Meinst du damit also, dass es, wenn ich eine Datenkonsistenz dieser Art gewährleisten möchte, Aufgabe des Clients sein muss?

Die Daten sind ja erst einmal konsistent. Du kannst natürlich mit der DB noch mmehr machen, z.B. daß jeder User nur seine eigene nutzer_id verwenden darf etc. Da geht vieles.