Datenbank-Check bei Insert

[AtTheDriveIn]

Hi

folgendes Problem: Ich bilde gerade eine Art Baum in einer Datenbanktabelle ab.

Die Tabelle sieht vereinfacht folgendermaßen aus:

table object_tree
-------------------
id int (PK)
o_id int (FK object(ID))
parent int (FK(object_tree(ID)), allow null
c_id int (FK(Collection(ID))
-------------------

parent referenziert einen Eintrag in derselben Tabelle, eben den Vater des Objects. Die Baumwurzel hat keinen Vater, parent ist beim Vaterobject also null.

c_id ist quasi die Baum_ID.

Ich möchte nun sicherstellen, das ein Baum nur eine Wurzel hat. Mein erster Gedanke war Unique(c_id,parent_id). Damit würde ich meine Forderung zwar erreichen, allerdings könnten dann Objecte in einem Baum auch nicht denselben Vater haben.
Gibt es eine Möglichkeit das zu erreichen, ohne die Verwendung von Triggern oder zusätzlichen Spalten/Tabellen? Check constraint vielleicht?

danke

[wry]

Mit dem Check constraint kann man das glaube ich nicht lösen, damit kann man soweit ich weiß nur den Wertebereich der Felder einschränken und das gilt dann für alle Einträge in einer Tabelle (nicht nur für die Wurzel).

[AtTheDriveIn]

denke inzwischen auch das ein check-constraint nicht gehen wird.

Da das Attribut parent eigentlich zwei Informationen trägt (hasParent? und isRoot?) ist wohl auch keine Unique Constraint denkbar.

Ich denke ein Insert-Trigger ist die richtige Wahl.

[daflow]

Hab da noch ein kleines Verständnissproblem.
In "Parent" willst du jeweils das übergeordnete Blatt zum aktuellen Blatt also der ID schreiben? Wenn dem so sein sollte, dann kannst du in "parent" eh kein Nullvalue eintragen weil dein FK dies verhindern wird immer wenn du etwas in Parent eintragen willst, wird ja nachgesehen ob dieser Wert in ID existiert, was ja nicht sein kann, weil ID der Primary key ist...Wo wir bei meiner nächsten Frage sind. Warum ist ID der Primary key? So das immer ein Blatt beschreiben soll, dann ist diese ja nur innerhalb eines baumes eindeutig und wenn ich dich richtig verstenden habe, willst du für mehrere Bäume hier die Beziehungen der Blätter festhalten? Dann wäre hier ja erst ein PK(ID,C_ID) (Ein Blatt ist pro Baum eindeutig) zulässig. Ein Unique constraint (id,c_id,parent) sollte dann den gewünschten Effekt haben (schränkt ein: Ein Blatt in einem Baum hat immer nur einen Parent)

Nachtrag: Aus dem Nullvalue-Schlamassel kommt man hier klassischerweise raus in dem man die Parent-Son Relation in eine eigene Tabelle ausgliedert, dann kannste auch wieder sauber 'n FK anlegen und du hättest ein sauberes DB-Design.

[AtTheDriveIn]

also mein DBS (MSSQL) akzeptiert NULL in einer Spalte die Foreign Key ist ohne Murren, außer natürlich ist setze [not NULL] ;)

Und nein, eine extra Tabelle will ich ja gerade vermeiden. Selbstreferenzierende Tabelle ist das Stichwort.


PK(ID,C_ID) ginge nicht, da object_tree ja Object (siehe o_ID) referenziert, ich also nicht verhindern würde das ein Object mehrere Male im Baum auftauchen kann.

Unique (id,c_id,parent) unnütz, da id PK und sowieso eindeutig ist. ID übrigens IDENTITY.


Ich habe Unique(o_id,c_id) drin, das verhindert, das ein Object öfters im Baum auftaucht und könnte auch als PK herhalten, bin aber kein Fan von zusammengesetzen PK.

[daflow]

also mein DBS (MSSQL) akzeptiert NULL in einer Spalte die Foreign Key ist ohne Murren, außer natürlich ist setze [not NULL] ;)

Und nein, eine extra Tabelle will ich ja gerade vermeiden. Selbstreferenzierende Tabelle ist das Stichwort.


PK(ID,C_ID) ginge nicht, da object_tree ja Object (siehe o_ID) referenziert, ich also nicht verhindern würde das ein Object mehrere Male im Baum auftauchen kann.

Unique (id,c_id,parent) unnütz, da id PK und sowieso eindeutig ist. ID übrigens IDENTITY.


Ich habe Unique(o_id,c_id) drin, das verhindert, das ein Object öfters im Baum auftaucht und könnte auch als PK herhalten, bin aber kein Fan von zusammengesetzen PK.


Äh , also auch wenn dein Fk auf einen PK referenziert, der definitiv keine NULL values enthalten darf, kannst du in das Fk-Feld NULL eintragen? Ist dann irgendwie sinnfrei der FK, wenn er nich prüft, was in der Referenz drin steht, wa?

Wenn du das so willst, dann sei dem so, wollte es nur erwähnt haben ;)

Äh ja dein uniqueconstraint dazu ist genau dafür da, war ja bisher nicht die Rede davon, dass du auch ein Object pro baum maximal einmal da sein darf.
Aber was hindert dich daran zusätzlich einen PK wie oben stehend zu verwenden, der PK schliesst das Unique constraint ja nicht aus?

Ein Pk ist immer identity. Daher ja eine erste Frage ob der PK so Sinn macht, oder was ID denn genau sien soll. Ist denn unn ID das jeweilig Blatt eines Baumes das du in dieser Tabelle beschreibst, dann versteh ich nicht ganz warum dieses Blatt innerhalb aller Bäume eindeutig sein sollte?

[AtTheDriveIn]

Äh , also auch wenn dein Fk auf einen PK referenziert, der definitiv keine NULL values enthalten darf, kannst du in das Fk-Feld NULL eintragen? Ist dann irgendwie sinnfrei der FK, wenn er nich prüft, was in der Referenz drin steht, wa?


Es geht nur kein Verweis auf nicht vorhandene Schlüssel. set parent=20 wenn es ID 20 nicht gibt, ist also nicht möglich. NULL ist bei Fremdschlüsseln definitiv möglich. Bei PK nicht, das ist klar.


Ein Pk ist immer identity. Daher ja eine erste Frage ob der PK so Sinn macht, oder was ID denn genau sien soll. Ist denn unn ID das jeweilig Blatt eines Baumes das du in dieser Tabelle beschreibst, dann versteh ich nicht ganz warum dieses Blatt innerhalb aller Bäume eindeutig sein sollte?

ID ist ein synthetischer Primary Key, d.h eine extra Spalte die als PK dient. Identity sorgt bei T-SQL automatisch dafür, das passende ID generiert werden.
Zweck der Tabelle object_tree ist es, Objecte aus einer anderen Tabelle(siehe o_id) in einer Baumstruktur anzuordnen. Objecte können in mehreren Bäumen vorhanden sein, allerdings pro Baum nur einmal [daher UNIQUE(o_id,c_id)]. Pro Baum darf es auch nur eine Wurzel geben [Lösung gesucht]. Eine Wurzel hat keinen parent, das FK Feld verweist also nicht auf eine andere ID in object_tree, sondern ist NULL.


EDIT: Habe einen ziemlich bemerkenswert klingenden Ansatz gefunden Bäume in SQL darzustellen: Nested Sets (http://www.klempert.de/nested_sets/artikel/). Das nehme ich mal in Angriff. :)