At 08:02 PM 28-12-00 +0000, you wrote:

> > > When I delete entry from master table want all detail records
>also to
> > > be deleted. As far as I can tell, masterlinks demo should do this
> > > but when I try to delete a record from the master table I get an
> > > error about Violation of Foreign Key Constraints.

The foreign key constraint **constrains** you from deleting a master whilst
referenced details exist. If you want cascading deletes you must add them
to the constraint; or write a Before Delete trigger for the master that
deletes the details first.

> > >
> > > Can someone point me in the right direction to learn how to
> > > accomplish this.
> >

Any good book on database design will explain how cascading referential
integrity works. There is quite a bit about it in the IB 6 beta manuals,
albeit spread out in several different places.

> > Change the referential integrity to cascade the delete instead of
> > restricting it. Also, you could use a trigger that would delete the
>child
> > records when the master record is being deleted.
>
>Being new to Interbase and IBO, I may simply not understand but I can
>find no way to accomplish what I need.

There is a vast difference between Paradox and InterBase. You really need
to understand how foreign keys work - it is structurally quite different
from the hierarchical primary key structure with which you form referential
relationships in Paradox.

>Initially my application ran under the BDE using Paradox tables. I
>had a master table, Employee. I also had a PayControl table. The
>PayControl table had a secondary (non unique) index by EmployeeNumber
>since each employee could have any number of pay control records.

Don't confuse keys and indexes. Keys form relationships, indexes
don't. The typical way to form a master-detail relationship is to have a
column in the detail table that holds the primary key of the master. To
get uniqueness in the detail records, use a surrogate primary key for that
table.

>When I deleted the master (Employee) record, since the tables were
>linked master/detail, all I had to do was
>With PayControlTable Do
>Begin
> First;
> While Not Eof Do
> Delete;
>End;
>and all pay control records for the employee were deleted.

This is what the cascading delete (or Before Delete trigger) does for you.

>I then converted the application to Interbase using IBX. With no
>change to the logic, all worked fine.

That's a puzzle - if you had a foreign key defined at that time, it should
not have worked.

>I have now converted to IBO but since I can find no where to have IBO
>use a secondary index, the logic no longer works. I get a multiple
>records deleted error.
>
>I have tried to use Foreign keys to set up referential integrity but,
>If I userstand correctly, the keys must be unique. Since the
>PayControl table contains non unique keys, I can't see how to make
>this work.

The foreign key column does not have to be unique; but the primary key
column of the master must be. Stop thinking about "secondary indexes" -
that concept is peculiar to Paradox and has no meaning in a RDBMS. Indexes
are indexes, keys are keys. It happens that, when you apply a PK
constraint to a column or composite, IB automatically creates a unique
index for it; and when you apply a FK constraint to a column or composite,
IB automatically creates a non-unique index for it, PROVIDED no rows
contain NULL. It may be the presence of NULL in the FK column that is
preventing you from applying the FK constraint.

To repeat - creating an index does not create a key. Key constraints must
be applied specifically. Remove any indexes on the keys before you do it,
though, as you will get duplicate indexes otherwise, and these cause the
optimizer to work incorrectly.

>Can you assist in a more detailed explanation as to how to handle a
>delete situation like mine.

It's not an IBO matter, it's a fundamental database design issue. I
recommend Graeme Simsion's book "Data Modeling Essentials: Analysis,
Design and Innovation".

Helen

All for Open and Open for All
InterBase Developer Initiative · http://www.interbase2000.org
_______________________________________________________