At 04:17 PM 14/09/2004 +0000, you wrote:

>Howdy,
> I realize this is more than likely not a FB question but I am just
>having a hard time getting this to work right. I am sure it is in
>the way I am looking at the problem. Here is what I have in some
>generic terms.
>TableA has a Primary Key of Col1, Col2.
>TableB has a Primary Key of Col1, Col2, Col3.
>TableB has a Foreign Key defined as FOREIGN KEY(Col1, Col2)
>REFERENCES TableA(Col1,Col2).
>Col1 and Col2 are the same type and size in both tables.
>
>Here is where I am running into problems. They tables both CREATE
>just fine. I am loading a bunch of data in from a text file and I
>load TableA first and it works just fine. Then part way through
>loading TableB I get a Foreign Key error. The interesting thing
>about the row that generates the error is that it is the first row
>that the Col2 data is duplicated, but the Col1 values are
>different. I don't know if this is making sense. What I am
>expecting from the foreign key may be incorrect. I am expecting
>that it is requireing uniqueness on the combination of the two
>columns. The combination of Col1 and Col2 are unique in this case
>that is generating the error. I have searched the text files I am
>loading the data from to make sure this is the case.
>
>Can anyone help shed a bit of light on this? I would really
>appreciate it.

If you get duplication in the *foreign* key, that is "as expected,
intended". If a foreign key had to be unique, you could not implement a
1:many relationship. So the FK violation isn't coming from duplicated
keys. Although the primary key constraint on table B requires that the
three key columns of your primary key must be a unique combination, the two
(shared) key columns of the foreign key are allowed to be duplicates. So
duplication isn't causing a FK violation.

The FK violation will have arisen from there being no "parent" primary key
that matches it. Likely causes should be sought in the actual
data: possibly you have a case mismatch, or you are using "risky" elements
in the keys, such as floating point numbers or timestamps; or null dates
being passed as zero; et al.

Advice you didn't ask for: Although it's not structurally or theoretically
incorrect, you're making a practical problem for yourself by combining the
primary key with the foreign key. This legacy from hierarchical data
storage systems like Paradox---which don't have any other way to implement
referential integrity--- makes key maintenance more complex and risk-prone
than it needs to be.

I'm of the school that believes foreign key relationships should be
implemented THIN and STRONG; that is, by surrogating the primary key of
the "parent" entity with an atomic, human-proof key (which we do in
Firebird with generators). Then you hang the dependents off a simple key
that won't be broken by bad spelling, upper/lowercasing or precision
problems in data.

./heLen