Append query - key violations??

[Angi]

How can append InvoiceDetail records and still enforce referential
integrity with the InvoiceMain table? I've tried every way I know how.
I have no need to keep the details if the invoice is deleted. Do I
have to do seperate delete queries or is there a way for this to be
done??

I get the following error when I run my query with integrity enforced,
works fine as soon as I remove it:

Database set 0 fields to null due to a type conversion error, and it
didn't add 2 records to the table due to key violations...blah blah
blah.


TIA!
Ang

 

[Van T. Dinh]

If you enforce R.I., JET will check that the ForeignKey values you are
trying to append do exist in the InvoiceMain Table, i.e. there are Records
with PK values that are the same with the ForeignKey values being appended.

If the ForeignKey values being appended don't correspond to valid PK values
in the InvoiceMain Table, you will get the error posted.

In effect, R.I. prevent child Records without a valid Parent Record.

Whether you want to enforce "Cascade Delete Related Records" is personal
choice. I tend not to use "Cascade Delete" since if the Parent Record is
deleted by accident, all the Child Records will also be deleted.

 

[Angi]

Van,
Thank you very much for that explanation. I didn't realize there
needed to be matching FK's from the many side. If the parent is
deleted by "accident" I'll have no use for the child records because
the new invoice number won't match the old, so the old details are
useless. So, I guess I have to do separate delete queries. That's
what I thought...just thought I could use the R.I. to make it easier.

Thanks for your help!!

Ang

 

[John Vinson]

Van,
Thank you very much for that explanation. I didn't realize there
needed to be matching FK's from the many side. If the parent is
deleted by "accident" I'll have no use for the child records because
the new invoice number won't match the old, so the old details are
useless. So, I guess I have to do separate delete queries. That's
what I thought...just thought I could use the R.I. to make it easier.

Thanks for your help!!

Ang

Just note that you can set the relationship's Cascade Delete checkbox;
deleting parent records (the invoice) will automatically delete all
the child records. Use with care! Deleted is GONE, without recourse or
recovery.

John W. Vinson[MVP]

 

[Van T. Dinh]

I am not sure whether I misunderstood your post or not but your post seems
to say the reverse from what I wrote.

Let me clarify the R. I. : when you create a relationship between 2 Tables
in a One- to-Many relationship, i.e. the ForeignKey Field in the Many
(Child) Table corresponds to the PK in the One (parent) Table, you have 2
options (mutually exclusive):

Option 1 - R.I. not used / enforced: In this case, you can enter anything
in the ForeignKey Field (valid data type, of course) and Access (JET
actually) will accept without checking. In this case, you can create
orphaned Child Records, i.e. Invoice Items without a corresponding Invoice
Header Record.

Option 2 - R.I. enforced: In this case, whenever you enter a value in the
ForeignKey Field of a Child Record and update the Child Record into the Many
/ Child Table, JET will check to ensure that there *exists* a One / Parent
Record that has the PK Field value exactly the same as the ForeignKey value
of the Child Record being saved. This will ensure that the user can't
create Child / Many Records without a valid Parent Record.

If you select the option 2 above (R.I. enforced), you can then select 2 more
*sub*options which are not mutually exclusive, i.e. you can select no
sub-option, 1 sub-option or both sub-options:

Sub-option 2.1 - "Cascade Update Related Records": This means that if you
change the PK value of the One / Parent Record, the ForeignKey values of the
related Many / Child Records will be changed automatically to the same value
as the PK value so that they are still linked to the same old One / Parent
Record even thought the PK value has been changed.

In Tables where the PK Field is an AutoNumber Field, the "Cascade Update
Related Field" in not nrcessary since you cannot change the value of the PK
/ AutoNumber Field.

Sub-option 2.2 - "Cascade Delete Related Records": This means that if you
delete the One / Parent Records, JET will automatically delete all Child
Records that are related to the deleted One / Parent Record.

Sub-option 2.2 is a bit of a danger as John & I pointed out previously.

Basically:

# (main) option 2 prevents the user from *creating* orphaned Child Records,
i.e. creating Child Records without (valid) Parent Records.

# Sub-option 2.1 tries to maintain the correct linking (in case the user
change the PK value and forget to change the corresponding FK values).

# Sub-option 2.2 prevent the user from *leaving* orphaned Child Records in
the database.

 

[Angi]

Van,
I'm not sure of what I said that prompted that explanation, but I
greatly appreciate it! I understood what R.I. meant/means. What I
don't understand is why my query was returning those errors with R.I.
on. Of course....I just turned R.I. on and it's working fine. I'm
thinking the order of my queries were wrong and I fixed them somewhere
along the way and didn't realize that fixed the problem. Thanks all
for your help!!!

Gratefully,
Angi

 

[Van T. Dinh]

That why I wrote that I might have misunderstood your post. I thought you
understand R.I. However, you wrote:

"I didn't realize there needed to be matching FK's from the many side."

To me logically, it should be:

"I didn't realize there needed to be matching PrimaryKey value from the One
side."

since the One / Parent Record must exist _first_ and the R.I. check is done
on the creation / editing of the Many / Child Record.

The main thing is that you R.I. well, though ...