--
Martijn Tonies
Upscene Productions

InterBase Workbench - The Developer Tool for InterBase
http://www.interbaseworkbench.com

"Experience is what you get when you didn't get what you wanted"

"Helen Borrie" <helebor@...> schreef in bericht
news:5.1.0.14.0.20020104023117.02a45c60@......

> At 04:20 PM 03-01-02 +0100, you wrote:
> >Hi all,
> >
> >Perhaps we all know the error:
> >
> >My metadata was this:
> >
> >Table "RELATIE"
> >
> >RelatieID PK
> >... more columns ...
> >
> >Table "LEVERANCIER"
> >
> >RelatieID PK, FK to RELATIE
> >... more columns ...
> >
> >Table "ORDERREGEL"
> >
> >LeverancierID FK to LEVERANCIER
> >
> >Creating this constraint gave me the error that some index wasn't defined

in

> >the tables and a trigger raised an exception bla bla...
> >
> >A common cause for this error is have an index defined on a primary key
> >column. In this case, the index was created by the system - namely

because

> >of the FK from LEVERANCIER to RELATIE.
> >
> >Dropping this FK results in removing the index and the second FK (from
> >ORDERREGEL to LEVERANCIER) could be created without problems. Next up:
> >creating the FK from LEVERANCIER to RELATIE. All went well...
> >
> >It is a workaround for a strange problem -- comments, anyone?
>
> Well, you won't like it, probably..but my rule (incidentally NOT

determined by the need to work around your problem, but by aesthetics) is to
NEVER make a primary key do double action as a foreign key. If I want a
foreign key, I create a column for it, i.e. design the database before you
create it. It doesn't cost much and it keeps keys "functionally pure".

>
> :)
>
> Helen

I think it is functionally pure.

RELATIE holds companies that are somehow related to the application owner.
These companies can have several different functions - LEVERANCIER is
someone who sells goods to application owner. And every LEVERANCIER is a
RELATIE - therefore, every record in LEVERANCIER can be identified by it's
unique RelatieID - hence, both the PK and FK on the column... Is this wrong?

Martijn