Hello,
it was my misunderstanding of the documentation (I've read the german
version). Since I'm not so firm at SQL and I'm new to the whole database
world, it would have been helpful if the docs were changed that it's clear
that there is a 1:n-relationship between reservation and customer or
something like that.
I find the explanation of Thomas Anhaus very useful. Maybe something like
this could be added to the docs to make it clear(er) (even for a newbie like
me). Though it's related to my tables it can be easily changed to the
"reservation-customer"-example:
Quote from T. Anhaus:
"This means that you have one (master) row in "TBL_RUECKMELDUNG" and
several (detail) rows in "TBL_STAFFELRUECKM" referencing the master row.
You can also say that the detail rows are linked to the master row.
The referential constraint ensures that
1. you can insert into TBL_STAFFELRUECKM only if there exists the
corresponding master
row in "TBL_RUECKMELDUNG".
2. All corresponding (detail) rows of TBL_STAFFELRUECKM are deleted when the
corresponding (master) row is deleted."
I have to add that he changed my definitions of the tables in his answer, so
they fit my intention.
Thanks to all
Peter
> -----Urspr�ngliche Nachricht-----
> Von: Zabach, Elke [SMTP:[EMAIL PROTECTED]
> Gesendet am: Mittwoch, 20. August 2003 16:38
> An: H�bschen, Peter; Mailingliste SAPDB (E-Mail)
> Betreff: RE: Problem with referential constraint
>
> "H�bschen, Peter" wrote:
>
> > Hello,
> >
> > I was running into this problem earlier, but now I need an
> > explanation,
> > cause it's important for project. Earlier I only deleted the
> > referential
> > constraint.
> > My tables are defined:
> > CREATE TABLE "TBL_RUECKMELDUNG"
> > (
> > "LFD_RUECKFRAGENUMMER" Integer NOT
> > NULL DEFAULT
> > SERIAL (1),
> > "RUECKMELDEDATUM" Timestamp DEFAULT TIMESTAMP,
> > "LFD_ANFRAGENUMMER" Integer,
> > .....
> > PRIMARY KEY ("LFD_RUECKFRAGENUMMER"),
> > FOREIGN KEY "REF_RUECK_STAFFEL" ("LFD_ANFRAGENUMMER")
> > REFERENCES
> > "TBL_STAFFELRUECKM" ("LFD_STAFFELNR") ON DELETE CASCADE
> > )
> >
> > CREATE TABLE "TBL_STAFFELRUECKM"
> > (
> > "LFD_RUECKFRAGENUMMER" Integer NOT NULL,
> > .....
> > "LFD_STAFFELNR" Fixed (10,0) NOT NULL
> > DEFAULT
> > SERIAL (1),
> > PRIMARY KEY ("LFD_STAFFELNR")
> > )
> >
> >
> > If I try to insert a new line into TBL_RUECKMELDUNG (through
> > SQL-Studio
> > (latest Version)) I get an integrity constraint violation. If
> > I insert a new
> > line into TBL_STAFFELRUECKM it works.
> > My intention is:
> > TBL_RUECKMELDUNG contains one line and TBL_STAFFELRUECKM
> > contains one or
> > more lines which are "linked" to the one line in
> > TBL_RUECKMELDUNG. If I
> > delete this one line in TBL_RUECKMELDUNG I want to delete the
> > "linked" lines
> > in TBL_STAFFELRUECKM
> > So I made the constraint TBL_RUECKMELDUNG ->
> > TBL_STAFFELRUECKM. But after my
> > experience from below it seems that I have to make a constraint
> > TBL_STAFFELRUECKM -> TBL_RUECKMELDUNG. Is this right?
>
> Yes
>
> > I have to fill my first table first, since it contains the
> > main data and the
> > second one contains more 'detailed' data or maybe no data for
> > that record in
> > TBL_RUECKMELDUNG.
>
> That's right
>
> >
> > I nearly forgot: I'm using a Redhat 9 box with SAPDB 7.4.3.25
>
> The definition of the referential constraint has to be in the table which
> references,
> i.e. which has to be checked if an insert is done in that table.
>
> The referenced table (which has to be checked when update/delete are done
> in)
> does not have the referential constraint definition in.
>
> --> TBL_STAFFELRUECKM has to have the referential constraint definition
> if for one of its numbers it has to be assured that this number is in
> TBL_RUECKMELDUNG
> as well.
>
> Please pay attention to the fact about keycolumns/unique index columns
> which are needed for the referential constraint.
>
> Elke
> SAP Labs Berlin
>
>
>
>
_______________________________________________
sapdb.general mailing list
[EMAIL PROTECTED]
http://listserv.sap.com/mailman/listinfo/sapdb.general
