Constraints für child-parent Beziehungen mit Index

Hallo,

ich habe folgende Tabelle und Daten

CREATE TABLE component
(
  id integer NOT NULL, 
  "name" character varying(255),
  "index" integer, 
  id_parent integer 
)

INSERT into component (id, name, index, id_parent) values (0, 'Vater', null, null);
INSERT into component (id, name, index, id_parent) values (1, 'Kind1', 0, 0);
INSERT into component (id, name, index, id_parent) values (2, 'Kind2', 1, 0);

Nun habe ich ein Problem, dass es manchmal in meiner Datenbank vorkommt, dass es z.B. drei Kinder gibt, aber nicht alle Indizes vorhanden sind. Z.B. 0,1,3.

Kann man in Postgres einen Constraint erstellen, das prüft, ob die Indizes innerhalb einer Generation kontinuierlich (=ohne Lücken) sind?

Vielen Dank,
Tobias

Wozu dient die Index-Spalte? Deine id_parent zeigt ja offensichtlich auf die Spalte id, selbe Tabelle. Warum ist das nicht als Primary Key / Foreign Key gemacht? Und warum müssen solch künstlichen Keys denn ohne Lücken sein?

Andreas

Hallo,

hast Recht, die Unique und FK-Constraints habe ich nicht in die Demonstration hier im Forum mit aufgenommen. In der richtigen Tabelle sind diese, und noch viel mehr Sachen mit drin.

Die index Spalte dient dazu die Reihenfolge der Kindelemente zu speichern, damit NHibernate diese Reihenfolge auch korrekt einlesen kann. Wir verwenden Postgres-NPGSQL-NHibernate-C#. In C# gibt es dann eine Liste der Kinder. Fehlt hier ein Index, dann gibt es null Einträge in der Liste, was nur zu Problemen führt. Deshalb möchte ich das gerne per Constraints verhindern.

Gruß,
Tobias

Dann berechne doch die Spalte zur Laufzeit:

test=# select * from component;
 id | name  | id_parent
----+-------+-----------
  1 | vater |
  2 | sohn1 |         1
  3 | sohn2 |         1
  4 | sohn3 |         1
(4 rows)

test=# create view view_component as select id, name, row_number() over (partition by id_parent order by id) as index from component;
CREATE VIEW
test=# select * from view_component order by id, index;
 id | name  | index
----+-------+-------
  1 | vater |     1
  2 | sohn1 |     1
  3 | sohn2 |     2
  4 | sohn3 |     3
(4 rows)

Ich sollte allerdings erwähnen, daß das 8.4 ist.

Bis dahin könnte man das mit einer SRF nachbilden. Ein Constraint zu definieren halte ich für nicht möglich. Aber vielleicht irre ich mich ja auch.

Andreas

Hallo Andreas,

vielen Dank für deine, wie immer schnelle Antwort. Ich habe zunächst mal nachgelesen, was eine Window Call function ist. Ich wusste bisher nicht, dass es so etwsa gibt. Ich hätte es aber schon oft benötigt:-)

Leider kann ich PG 8.4 noch nicht einsetzen, und die Berechnung wäre auch nicht möglich, da die Reihenfolge der Kindelemente durch den Benutzer änderbar sein muss. Auf Views könnte ich mit Triggern zwar die Operationen abfangen, doch das wird sehr umständlich. Deshalb werde ich wohl im C# Code die entsprechenden Ursachen der Probleme suchen müssen, um sie zu vermeiden.

Was ich an deiner Antwort nicht verstehe ist die Abkürzung SRF ist das eine StoRedFunction, also mit Update,Insert Trigger auf die Component-Tabelle? Ich habe leider in der PG-Doku zu SRF nichts gefunden.

Set Returning Function.

PostgreSQL: Documentation: Manuals: PostgreSQL 8.3: Query Language (SQL) Functions

Andreas

Vielen Dank für deine Hilfe!

du koenntest einen

PostgreSQL: Documentation: Manuals: PostgreSQL 8.3: CREATE CONSTRAINT TRIGGER

definieren mit folgender bedingung:

sum(n) = n * (n + 1) /2

in einer selbst gebauten funktion - die dann eben bei nicht-erfuellung einen fehler ausgibt …

du koenntest natuerlich auch auf die aggregat-funktion verzichten und bei den inserts und/oder updates dafuer sorgen, dass keine “luecken” auftreten … das ist wahrscheinlich schneller …

oder du koenntest deiner anwendung beibringen, mit luecken umzugehen - lücken sind fuer ne reihenfolge ja nicht wirklich relevant … da wäre ohnehin zu empfehlen - da es die anwendung insgesamt “robuster” macht

ACK.

test=# create or replace function check_index() returns trigger as $$begin if new.id_parent is null then return new; end if; if (select sum(index) from component where id_parent=new.id_parent) = new.index*(new.index+1)/2 then raise notice 'okay'; return NEW; else raise exception 'INDEX nicht fortlaufend'; return NULL; end if;end;$$language plpgsql;
CREATE FUNCTION
test=*# drop trigger trg_check on component;
DROP TRIGGER
test=*# create constraint trigger trg_check after insert on component for each row execute procedure check_index();
CREATE TRIGGER
test=*# insert into component values (1,'vater',1,null);
INSERT 0 1
test=*# insert into component values (2,'sohn1',1,1);
NOTICE:  okay
INSERT 0 1
test=*# insert into component values (3,'sohn2',2,1);
NOTICE:  okay
INSERT 0 1
test=*# insert into component values (4,'sohn3',4,1);
ERROR:  INDEX nicht fortlaufend
test=!#

Müßte noch auf Update erweitert werden, scheint aber so zu funktionieren.

Andreas

Andreas

diese summe ist so ziemlich die einfachste reihe an die ich mich aus dem alten mathe-unterricht erinnere feix

1 + 2 + 3 + …

war das also doch zu irgendwas nützlich :slight_smile:

korrekter weise sollte man fuer n = max(feld) nehmen … damit man auch das löschen zwischendrin verhindert … (kommt halt drauf an was er denn alles erlauben will und was nicht)

oder noch besser - man triggert das delete und updated alle die groesser sind mit dem jeweiligen decrement …

das ganze ding hat ein paar eier - da stolpert er aber wohl selbst noch drueber … ich hab sowas auch schon gebaut … am ende kommt ein gesamtkunstwerk raus - das laesst sich nicht durch nur eine funktion beschreiben …

aber nur wegen der anzeige wuerde ich das nicht machen - da lieber die anwendung mal ueberdenken

Ja, dachte ich mir BEI DIR auch gerade.

Andreas

hach ja … gib nicht mir die schuld an deinen komplexen

Na sicher doch.

Falschfahrer – Wikipedia

Andreas

Hallo,

ich bin nochmal auf eure Vorschläge gekommen. Die Lösung mit dem trigger finde ich Klasse. Vielen Dank.!