| Subject | Re: Which way to set up Foreign Key? |
|---|---|
| Author | Adam |
| Post date | 2006-09-14T23:59:01Z |
--- In firebird-support@yahoogroups.com, "Anthony Tanas" <anthony@...>
wrote:
any DOCTORINSURANCE record with the old DOCTORID will be automatically
changed to point to the new DOCTORID.
Just a tip at this point in time, using surrogate primary keys from
generators all but removes the need to ever update a master record.
do manage to get the constraint applied but the table data breaks the
rules, it makes restoring a backup more tricky.
when inserting, updating or deleting records. No worse than the
overhead of any other indexed fields. Almost always, foreign keys will
improve select performance.
The only time under Firebird 1.5 or earlier you may want to even
consider not defining the constraint is where the master table only
has a very small number of records and the detail table has lots of
records. This is called poor selectivity. Garbage collection can be
very expensive under such a condition. This will be resolved by
Firebird 2. For example, in an electoral system, you might have
thousands or millions of voter records. There may be information such
as country of birth, and the vast majority of voters may be from a
single country. Such an index may be expensive to maintain.
forget one? What if someone goes through IBExpert or DBWorkbench or
something and just deletes a record without going through your
application? What if you remember to delete a dependent record, but
forget that the particular dependent record has dependent records of
its own? (for example, you may have a record that depends on a
DOCTORINSURANCEID or something like that). What if someone extends
your database in the future to add yet another table with DOCTORID?
(you would need to modify your source to take that dependency into
account).
I would let the database take care of that sort of thing. I mean it is
not going to hurt if you have already deleted the records anyway, but
the client side cascade logic you describe involves a lot of round
trips to the database (expensive on performance) whereas a foreign key
constraint allows it to be achieved by a single trip to the database.
Adam
wrote:
>that if
> > --- In firebird-support@yahoogroups.com, "Anthony Tanas" <anthony@>
> > wrote:
> > >
> > > I need to set up Foreign Key constraints in my database.
> > >
> > > I'm not sure which table I should add the constraint to. Here is an
> > > example:
> > >
> > > I have a DOCTOR table.
> > > I have an INSURANCE table.
> > >
> > > They are joined via a DOCTORINSURANCE table that contains the PK for
> > each
> > > table and then adds certain credential information for a doctor for
> > > that insurance company.
> >
> > So you are saying that for a DOCTORINSURANCE record to be
> > 'valid', there must exist a record in DOCTOR (matched between
> > DOCTORINSURANCE.DOCTORID and DOCTOR.DOCTORID).
>
> Yes, and also a corresponding entry in INSURANCE.
>
> >
> > If a doctor can be a member of multiple doctorinsurance
> > relationships, then the foreign key must go the other way.
> > Your foreign key should then rather constrain the
> > DOCTORINSURANCE table.
>
> Gotcha, that explains my problem. I'll need two FK constraints on
> DOCTORINSURANCE, one for DOCTOR and one for INSURANCE.
>
> Now for ON DELETE CASCADE and ON UPDATE CASCADE...does that mean if
> the ID changed in DOCTOR it would cascade to DOCTORINSURANCE, or ifit is
> changed in DOCTORINSURANCE it would cascade the other way?It means if you for example update the DOCTOR.DOCTORID for a record,
any DOCTORINSURANCE record with the old DOCTORID will be automatically
changed to point to the new DOCTORID.
Just a tip at this point in time, using surrogate primary keys from
generators all but removes the need to ever update a master record.
>another
> The ID would never change in this case, but I do have an example in
> case...but we'll use this example since it is similar:DOCTORINSURANCE
>
> So if the DOCTORID changed in the DOCTOR table, with an FK constraint on
> DOCTORINSURANCE with ON UPDATE CASCASE...would the field in
> change?Correct, should have read this before bothering to reexplain it.
>Not sure, hopefully it refuses to apply the constraint. If somehow you
> Thanks Adam. :)
>
> Additional Questions:
>
> 1) What happens if I put an FK constraint in place and some data in the
> database allready violates it?
do manage to get the constraint applied but the table data breaks the
rules, it makes restoring a backup more tricky.
> 2) How does FK constraints affect performance?FKs are implemented using an index. Obviously there is an overhead
when inserting, updating or deleting records. No worse than the
overhead of any other indexed fields. Almost always, foreign keys will
improve select performance.
The only time under Firebird 1.5 or earlier you may want to even
consider not defining the constraint is where the master table only
has a very small number of records and the detail table has lots of
records. This is called poor selectivity. Garbage collection can be
very expensive under such a condition. This will be resolved by
Firebird 2. For example, in an electoral system, you might have
thousands or millions of voter records. There may be information such
as country of birth, and the vast majority of voters may be from a
single country. Such an index may be expensive to maintain.
> 3) Currently my application makes sure to delete related records.If I have
> an FK constraint set up with ON DELETE CASCADE, should I just relyon the
> database to take care of this sort of thing?That is great if your application does this. What if you accidentally
forget one? What if someone goes through IBExpert or DBWorkbench or
something and just deletes a record without going through your
application? What if you remember to delete a dependent record, but
forget that the particular dependent record has dependent records of
its own? (for example, you may have a record that depends on a
DOCTORINSURANCEID or something like that). What if someone extends
your database in the future to add yet another table with DOCTORID?
(you would need to modify your source to take that dependency into
account).
I would let the database take care of that sort of thing. I mean it is
not going to hurt if you have already deleted the records anyway, but
the client side cascade logic you describe involves a lot of round
trips to the database (expensive on performance) whereas a foreign key
constraint allows it to be achieved by a single trip to the database.
Adam