Hallo,

Check Constraints können keine Subselects enthalten.

Wie kann ich dann sicherstellen, dass es bei einer 1:N Beziehung, N
nicht Null sein darf.
Es muss also mindestens einen Datensatz geben.

Beispiel: Zu einer Rechnung muss es immer Rechnungspositionen geben.

Gruß,
Thomas

--
Thomas Guettler, http://www.thomas-guettler.de/
E-Mail: guettli (*) thomas-guettler + de


--
Sent via pgsql-de-allgemein mailing list (pgsql-de-allgemein [at] postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-de-allgemein

am Mon, dem 10.03.2008, um 11:46:06 +0100 mailte Thomas Guettler folgend=
es:
> Hallo,
>
> Check Constraints können keine Subselects enthalten.
>
> Wie kann ich dann sicherstellen, dass es bei einer 1:N Beziehung, N
> nicht Null sein darf.
> Es muss also mindestens einen Datensatz geben.
>
> Beispiel: Zu einer Rechnung muss es immer Rechnungspositionen geben.

Du könntest ja eine Function aufrufen, aber Du hast dann ein
Henne-Ei-Problem: In dem Moment, wo Du die Rechnung erzeugst, steht in
der Positionstabelle noch kein Record. Man kann aber Constraints
deferrable setzen und in der TX das nutzen.

Reicht das Dir weiter?


Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net

--
Sent via pgsql-de-allgemein mailing list (pgsql-de-allgemein [at] postgresql.o=
rg)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-de-allgemein

Am Montag 10 März 2008 11:46:06 schrieb Thomas Guettler:
> Hallo,
>
> Check Constraints können keine Subselects enthalten.
>
> Wie kann ich dann sicherstellen, dass es bei einer 1:N Beziehung, N
> nicht Null sein darf.
> Es muss also mindestens einen Datensatz geben.
>
> Beispiel: Zu einer Rechnung muss es immer Rechnungspositionen geben.

START TRANSACTION;

CREATE TABLE rechnung (
id SERIAL PRIMARY KEY,
empfaenger TEXT NOT NULL,
kontoverbindung TEXT NOT NULL,
abteilung TEXT NOT NULL,
date TIMESTAMP NOT NULL
);

CREATE TABLE leistung (
id SERIAL PRIMARY KEY,
titel TEXT NOT NULL,
ergaenzung TEXT NOT NULL,
betrag DECIMAL NOT NULL
);

-- Keine Rechnungsposten ohne (Leistungs-)Empfänger oder Leistung...

CREATE TABLE rechnungs_posten (
rechnung_id INTEGER REFERENCES rechnung (id) NOT NULL,
leistung_id INTEGER REFERENCES leistung (id) NOT NULL
);

-- Rechnungsstellung...

SELECT SUM(betrag) FROM leistung
WHERE
id IN
(
SELECT leistung_id FROM rechnungs_posten
WHERE
rechnung_id =3D
( SELECT id FROM rechnung WHERE empfaenger =3D 'Musterma, Peter')
);

-- Geht aber nur wenn eine Leistung pro Rechnung vorkommen darf.
-- Ansonsten wird die Abfrage etwas umgestellt...
COMMIT;

Fazit: Tabellen-Design überdenken.

Gruß

Olaf

--
Sent via pgsql-de-allgemein mailing list (pgsql-de-allgemein [at] postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-de-allgemein

am Mon, dem 10.03.2008, um 12:15:06 +0100 mailte A. Kretschmer folgendes=
:
> am Mon, dem 10.03.2008, um 11:46:06 +0100 mailte Thomas Guettler folge=
ndes:
> > Hallo,
> >
> > Check Constraints können keine Subselects enthalten.
> >
> > Wie kann ich dann sicherstellen, dass es bei einer 1:N Beziehung, N
> > nicht Null sein darf.
> > Es muss also mindestens einen Datensatz geben.
> >
> > Beispiel: Zu einer Rechnung muss es immer Rechnungspositionen geben.
>
> Du könntest ja eine Function aufrufen, aber Du hast dann ein
> Henne-Ei-Problem: In dem Moment, wo Du die Rechnung erzeugst, steht in
> der Positionstabelle noch kein Record. Man kann aber Constraints
> deferrable setzen und in der TX das nutzen.
>
> Reicht das Dir weiter?

Mal ein Beispiel, wie sowas geht:

--
-- ich erstelle 2 Tabellen, master und slave
--
test=3D# create table master (id serial primary key, d date);
NOTICE: CREATE TABLE will create implicit sequence "master_id_seq" for s=
erial column "master.id"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "master_pk=
ey" for table "master"
CREATE TABLE
test=3D*# create table slave (id serial primary key, m int references mas=
ter deferrable not null, i int);
NOTICE: CREATE TABLE will create implicit sequence "slave_id_seq" for se=
rial column "slave.id"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "slave_pke=
y" for table "slave"
CREATE TABLE
--
-- bis hier simpel, Master-Detail-Tabelle
-- nun noch, daß kein Eintrag in master sein kann, wenn es keinen Eintr=
ag in slave gibt
-- dazu eine weitere Spalte via alter table, die eine Referenz auf slave =
darstellt
--
test=3D*# alter table master add column s int references slave deferrable=
not null;
ALTER TABLE
--
-- nun haben wir ein Henne-Ei-Problem, das zu lösen müssen wir constr=
aints deferred setzen
--
test=3D*# set constraints all deferred;
SET CONSTRAINTS
--
-- nun können wir, innerhalb einer TX, auch mal Constraints verletzen, =
was wir ja tun müssen
--
test=3D*# insert into master (d,s) values (current_date,0);
INSERT 0 1
test=3D*# insert into slave(m, i) values (currval('master_id_seq'), 10);
INSERT 0 1
test=3D*# update master set s=3Dcurrval('slave_id_seq') where id=3Dcurrva=
l('master_id_seq');
UPDATE 1
test=3D*# select * from master;
id | d | s
----+------------+---
1 | 2008-03-11 | 1
(1 row)

test=3D*# select * from slave;
id | m | i
----+---+----
1 | 1 | 10
(1 row)

test=3D*# commit;
COMMIT
--
-- noch einmal, ohne daß Constraints deferred sind
--
test=3D# insert into master (d,s) values (current_date,0);
ERROR: insert or update on table "master" violates foreign key constrain=
t "master_s_fkey"
DETAIL: Key (s)=3D(0) is not present in table "slave".
test=3D!#
--
-- sehr schön, oder?
--



--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net

--
Sent via pgsql-de-allgemein mailing list (pgsql-de-allgemein [at] postgresql.o=
rg)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-de-allgemein

Moin,

wäre es in diesem Fall nicht geschickt, den Constraint auf "DEFERRABLE
INITIALLY DEFERRED" zu setzen?
Wenn ich die Doku richtig verstehe, wäre der Constraint im Rahmen der T=
X
zunächst nicht scharf, sondern erst beim Commit.
Vorteil: man müsste die Constraints nicht handish bei jeder TX auf
deferrable setzen.

??

Gruß
Rainer

A. Kretschmer schrieb:
> am Mon, dem 10.03.2008, um 12:15:06 +0100 mailte A. Kretschmer folgend=
es:
>
>> am Mon, dem 10.03.2008, um 11:46:06 +0100 mailte Thomas Guettler folg=
endes:
>>
>>> Hallo,
>>>
>>> Check Constraints können keine Subselects enthalten.
>>>
>>> Wie kann ich dann sicherstellen, dass es bei einer 1:N Beziehung, N
>>> nicht Null sein darf.
>>> Es muss also mindestens einen Datensatz geben.
>>>
>>> Beispiel: Zu einer Rechnung muss es immer Rechnungspositionen geben.
>>>
>> Du könntest ja eine Function aufrufen, aber Du hast dann ein
>> Henne-Ei-Problem: In dem Moment, wo Du die Rechnung erzeugst, steht in
>> der Positionstabelle noch kein Record. Man kann aber Constraints
>> deferrable setzen und in der TX das nutzen.
>>
>> Reicht das Dir weiter?
>>
>
> Mal ein Beispiel, wie sowas geht:
>
> --
> -- ich erstelle 2 Tabellen, master und slave
> --
> test=3D# create table master (id serial primary key, d date);
> NOTICE: CREATE TABLE will create implicit sequence "master_id_seq" for=
serial column "master.id"
> NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "master_=
pkey" for table "master"
> CREATE TABLE
> test=3D*# create table slave (id serial primary key, m int references m=
aster deferrable not null, i int);
> NOTICE: CREATE TABLE will create implicit sequence "slave_id_seq" for =
serial column "slave.id"
> NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "slave_p=
key" for table "slave"
> CREATE TABLE
> --
> -- bis hier simpel, Master-Detail-Tabelle
> -- nun noch, daß kein Eintrag in master sein kann, wenn es keinen Ein=
trag in slave gibt
> -- dazu eine weitere Spalte via alter table, die eine Referenz auf slav=
e darstellt
> --
> test=3D*# alter table master add column s int references slave deferrab=
le not null;
> ALTER TABLE
> --
> -- nun haben wir ein Henne-Ei-Problem, das zu lösen müssen wir cons=
traints deferred setzen
> --
> test=3D*# set constraints all deferred;
> SET CONSTRAINTS
> --
> -- nun können wir, innerhalb einer TX, auch mal Constraints verletzen=
, was wir ja tun müssen
> --
> test=3D*# insert into master (d,s) values (current_date,0);
> INSERT 0 1
> test=3D*# insert into slave(m, i) values (currval('master_id_seq'), 10)=
;
> INSERT 0 1
> test=3D*# update master set s=3Dcurrval('slave_id_seq') where id=3Dcurr=
val('master_id_seq');
> UPDATE 1
> test=3D*# select * from master;
> id | d | s
> ----+------------+---
> 1 | 2008-03-11 | 1
> (1 row)
>
> test=3D*# select * from slave;
> id | m | i
> ----+---+----
> 1 | 1 | 10
> (1 row)
>
> test=3D*# commit;
> COMMIT
> --
> -- noch einmal, ohne daß Constraints deferred sind
> --
> test=3D# insert into master (d,s) values (current_date,0);
> ERROR: insert or update on table "master" violates foreign key constra=
int "master_s_fkey"
> DETAIL: Key (s)=3D(0) is not present in table "slave".
> test=3D!#
> --
> -- sehr schön, oder?
> --
>
>
>
>

--
Sent via pgsql-de-allgemein mailing list (pgsql-de-allgemein [at] postgresql.o=
rg)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-de-allgemein

Hallo,

Ziel: Constraint, dass bei einer 1:N Beziehung N größer Null.

danke für eure Antworten,

Leider funktioniert das nicht wie ich mir dachte:

1. Check Constraints lassen sich nicht auf deferrable setzen.
http://www.postgresql.org/docs/8.3/interactive/sql-createtab le.html
"""
DEFERRABLE ...Only foreign key constraints currently accept this clause.

2. Check Constraints werden nur bei INSERT und UPDATE geprüft.
Es muss aber auch geprüft werden, ob durch DELETE in der Detail
Tabelle das N der 1:N Beziehung Null wird.

Wenn obige Einschränkungen nicht wären, würde es mit diesem SQL-Code
gehen:
----------
begin;
CREATE TABLE "master" (
"id" serial NOT NULL PRIMARY KEY,
"name" varchar(32) NOT NULL
);

CREATE TABLE "detail" (
"id" serial NOT NULL PRIMARY KEY,
"master_id" integer NOT NULL
);

ALTER TABLE "detail" ADD CONSTRAINT master_id_constraint
FOREIGN KEY ("master_id") REFERENCES "master" ("id") DEFERRABLE
INITIALLY DEFERRED;

create or replace function count_detail(in master_id int)
returns bigint as $$
select count(master_id) from detail where detail.master_id=3D$1
$$ language sql;

alter table master add constraint constraint_master
check(count_detail(id)>0);
alter table detail add constraint constraint_detail
check(count_detail(master_id)>0);
insert into master values (DEFAULT, 'abc');
insert into detail values (DEFAULT, 1);

commit;

begin;
insert into master values (DEFAULT, 'abc2');
commit;
---------
Da Check Constraints nicht deferrable sind, kommt diese Meldung:
FEHLER: neue Zeile für Relation =BBmaster=AB verletzt Check-Constraint
=BBconstraint_master=AB

Aber jetzt habe ich eine Lösung gefunden: CONSTRAINT TRIGGER
http://www.postgresql.org/docs/8.3/interactive/sql-createcon straint.html

--
Thomas Guettler, http://www.thomas-guettler.de/
E-Mail: guettli (*) thomas-guettler + de


--
Sent via pgsql-de-allgemein mailing list (pgsql-de-allgemein [at] postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-de-allgemein

Am Dienstag 11 März 2008 10:48:27 schrieb Thomas Guettler:
> Aber jetzt habe ich eine Lösung gefunden: CONSTRAINT TRIGGER
> http://www.postgresql.org/docs/8.3/interactive/sql-createcon straint.html

Okay, wenn du bereit bist so tief in den Eingeweiden von pgSQL zu wühlen,=

würde ich mir an deiner stelle noch mal CREATE TYPE ansehen. Da sind dann=

deiner Kreativität (fast) keine Grenzen mehr gesetzt.

Das könnte dann im laufenden Betrieb so aus sehen (Pseudocode)...


create table rechnungswesen (id serial primary key, rechnungstyp rechnung=
,
firmentyp firma );

SELECT JAHRESABSCHLUSS(rechnung) FROM rechnungswesen;

So wird dann aus einer PostgreSQL ein SAP. Nur mit dem UI wird deine
Belegschaft etwas Probleme haben. Aber es gibt bestimmt ein Praktikant der=

ein bisschen PHP kann. Das kriegen wir dann auch noch in den Griff. ;-)


Gruß

Olaf

--
Sent via pgsql-de-allgemein mailing list (pgsql-de-allgemein [at] postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-de-allgemein

am Tue, dem 11.03.2008, um 11:25:26 +0100 mailte Olaf Radicke folgendes:
> Am Dienstag 11 März 2008 10:48:27 schrieb Thomas Guettler:
> > Aber jetzt habe ich eine Lösung gefunden: CONSTRAINT TRIGGER
> > http://www.postgresql.org/docs/8.3/interactive/sql-createcon straint.h=
tml
>
> Okay, wenn du bereit bist so tief in den Eingeweiden von pgSQL zu wüh=
len,
> würde ich mir an deiner stelle noch mal CREATE TYPE ansehen. Da sind =
dann
> deiner Kreativität (fast) keine Grenzen mehr gesetzt.
>
> Das könnte dann im laufenden Betrieb so aus sehen (Pseudocode)...
>
>
> create table rechnungswesen (id serial primary key, rechnungstyp rech=
nung,
> firmentyp firma );
>
> SELECT JAHRESABSCHLUSS(rechnung) FROM rechnungswesen;
>
> So wird dann aus einer PostgreSQL ein SAP. Nur mit dem UI wird deine
> Belegschaft etwas Probleme haben. Aber es gibt bestimmt ein Praktikant =
der
> ein bisschen PHP kann. Das kriegen wir dann auch noch in den Griff. ;-)

=C4hm, nicht wirklich hilfreich. Wenn es die Chance gibt, mit Bordmitteln
der DB, also mit CONSTRAINT oder TRIGGER, bestimmte Eigenschaften der
Daten in der DB zu erzwingen, dann ist es legitim, dies zu tun...


Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net

--
Sent via pgsql-de-allgemein mailing list (pgsql-de-allgemein [at] postgresql.o=
rg)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-de-allgemein

Am Dienstag 11 März 2008 11:43:29 schrieb A. Kretschmer:
> =C4hm, nicht wirklich hilfreich.

Wie so nicht? Ich finde es transparenter. Man sieht sofort: "Ah, hier wurde=

was eigenes reingestrickt. Mal sehen was dahinterstecht..."

Bei Verbiegen von CONSTRAINT weis der benutzer erst mal nicht warum sich se=
in
SQL-Code nicht so verhält wie er erwartet hat.

> Wenn es die Chance gibt, mit Bordmitteln
> der DB, also mit CONSTRAINT oder TRIGGER,

CREATE TYPE ist kein "Bordmitteln"? Definiere bitte "Bordmitteln".

> bestimmte Eigenschaften der
> Daten in der DB zu erzwingen, dann ist es legitim, dies zu tun...

Ich sage nicht das es nicht ginge oder falsch sei. Die frage ist (für mic=
h):
was entspricht den Erwartungen des Users an das Verhalten der DB am ehesten?


Olaf

--
Sent via pgsql-de-allgemein mailing list (pgsql-de-allgemein [at] postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-de-allgemein

Olaf Radicke schrieb:
> Am Dienstag 11 März 2008 10:48:27 schrieb Thomas Guettler:
>
>> Aber jetzt habe ich eine Lösung gefunden: CONSTRAINT TRIGGER
>> http://www.postgresql.org/docs/8.3/interactive/sql-createcon straint.html
>>
>
> Okay, wenn du bereit bist so tief in den Eingeweiden von pgSQL zu wühle=
n,
> würde ich mir an deiner stelle noch mal CREATE TYPE ansehen. Da sind da=
nn
> deiner Kreativität (fast) keine Grenzen mehr gesetzt.
>
>
Ich weiß, das CREATE CONSTRAINT TRIGGER nicht im SQL Standard ist. Aber
damit
habe ich kein Problem.

> Das könnte dann im laufenden Betrieb so aus sehen (Pseudocode)...
>
>
> create table rechnungswesen (id serial primary key, rechnungstyp rechnu=
ng,
> firmentyp firma );
>
> SELECT JAHRESABSCHLUSS(rechnung) FROM rechnungswesen;
>
> So wird dann aus einer PostgreSQL ein SAP. Nur mit dem UI wird deine
> Belegschaft etwas Probleme haben. Aber es gibt bestimmt ein Praktikant de=
r
> ein bisschen PHP kann. Das kriegen wir dann auch noch in den Griff. ;-)
>

Mit PHP hat wohl noch niemand etwas wirklich in den Griff bekommen.

Thomas

--
Thomas Guettler, http://www.thomas-guettler.de/
E-Mail: guettli (*) thomas-guettler + de

--
Sent via pgsql-de-allgemein mailing list (pgsql-de-allgemein [at] postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-de-allgemein

Olaf Radicke schrieb:
> Am Dienstag 11 März 2008 10:48:27 schrieb Thomas Guettler:
>
>> Aber jetzt habe ich eine Lösung gefunden: CONSTRAINT TRIGGER
>> http://www.postgresql.org/docs/8.3/interactive/sql-createcon straint.html
>>
>
> Okay, wenn du bereit bist so tief in den Eingeweiden von pgSQL zu wühle=
n,
> würde ich mir an deiner stelle noch mal CREATE TYPE ansehen. Da sind da=
nn
> deiner Kreativität (fast) keine Grenzen mehr gesetzt.
>
>
Ich weiß, das CREATE CONSTRAINT TRIGGER nicht im SQL Standard ist. Aber
damit
habe ich kein Problem.

> Das könnte dann im laufenden Betrieb so aus sehen (Pseudocode)...
>
>
> create table rechnungswesen (id serial primary key, rechnungstyp rechnu=
ng,
> firmentyp firma );
>
> SELECT JAHRESABSCHLUSS(rechnung) FROM rechnungswesen;
>
> So wird dann aus einer PostgreSQL ein SAP. Nur mit dem UI wird deine
> Belegschaft etwas Probleme haben. Aber es gibt bestimmt ein Praktikant de=
r
> ein bisschen PHP kann. Das kriegen wir dann auch noch in den Griff. ;-)
>

Mit PHP hat wohl noch niemand etwas wirklich in den Griff bekommen.

Thomas

--
Thomas Güttler, http://www.tbz-pariv.de/
Bernsdorfer Str. 210-212, 09126 Chemnitz, Tel.: 0371/5347-917
TBZ-PARIV GmbH Geschäftsführer: Dr. Reiner Wohlgemuth
Sitz der Gesellschaft: Chemnitz Registergericht: Chemnitz HRB 8543


--
Sent via pgsql-de-allgemein mailing list (pgsql-de-allgemein [at] postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-de-allgemein

* Thomas Guettler <hv [at] tbz-pariv.de> schrieb:

Hi,

> Wie kann ich dann sicherstellen, dass es bei einer 1:N Beziehung, N
> nicht Null sein darf.
> Es muss also mindestens einen Datensatz geben.
>
> Beispiel: Zu einer Rechnung muss es immer Rechnungspositionen geben.

erstmal würde ich bezweifeln, daß dieses Definition sinnvoll ist.
Ich könnte mir viele Situationen denken, in denen eine Rechnung
vielleicht ohne Posten sein könnte (zb. bei Stornierungen). Ergo
müßtest Du erstmal sicherstellen, daß bei den dahinterliegenden
Prozessen solch eine Situation ausgeschlossen ist !

Im =DCbrigen würd ich Dir einen ganz anderen Ansatz vorschlagen:
Erst die offenen Posten erfassen und dann daraus (evtl. aus einer
Teilmenge) die Rechnung erzeugen. Das kapselst Du dann über Views
oder Functions - ein direkter Zugriff auf Rechnungsposten ist verboten!
Es gibt nur diese schreibenden Operationen:

* Rechnung anlegen (-> Posten =3D> RE-ID)
* Rechnung stornieren ( -> RE-ID =3D> void)
* Metadaten setzen (-> RE-ID, Attribut, Wert =3D> void)

Für mich ist eine RE auch keine akkumulierte Forderung, sondern
ledeglich die Benachrichtigung darüber bzw. Zustands-Element im
Payroll-Prozeß.


cu
--
------------------------------------------------------------ ---------
Enrico Weigelt =3D=3D metux IT service - http://www.metux.de/
------------------------------------------------------------ ---------
Please visit the OpenSource QM Taskforce:
http://wiki.metux.de/public/OpenSource_QM_Taskforce
Patches / Fixes for a lot dozens of packages in dozens of versions:
http://patches.metux.de/
------------------------------------------------------------ ---------

--
Sent via pgsql-de-allgemein mailing list (pgsql-de-allgemein [at] postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-de-allgemein