Key violation and lock violation stop delete query!!

[DowningDevelopments]

hello moto
I have a table called receipts that hold values for receipts for money
transactions and is connected to different tables that hold records for what
is being paid (rent, deposit, parking etc) and how it is being paid (cash,
cheque, credit card etc). All of these tables data are tied in by the
receiptNo and are in a one to many relationship with the receipt.

Ive been asked to make it possible to delete a payment, which means
deleteing the receipt record and all dependent records so ive set up the
delete cascading updates to true and tried to run the following query:

DELETE DISTINCTROW Receipt.*
FROM Receipt
where [receipt].[receiptNo] = [receipt];

It asks for the [receipt] parameter, appears to run and then comes up with
the following error:

<-----
student database cant delete 1 record(s) in the delete query due to key
violation and 0 records due to lock violations.

Do you want to continue anyway?
---->

i press yes and it does nothing!

What am i doing wrong, this is the first delete query ive done and i cant
find much documentation on delete queries so im not sure what pitfalls to
look for, any help?
Thanks for looking at this in advance,

Amit

 

[Nikos Yannacopoulos]

Amit,

Not sure this is the source of your problem, but you could start by
losing the DISTINCTROW keyword:

DELETE Receipt.*
FROM Receipt
WHERE [receipt].[receiptNo] = [receipt];

HTH,
Nikos

 

[DowningDevelopments]

Thanks for the suggestion Nikos,

Ive got it working this morning, but im not sure what i did.

unhinged all the joined tables which had a receiptNo foreign key and thus
would have been deleted by cascading deletes and then checked added them back
one by one to see which table was causing the problem.

As you said, I think it was more to do with referential integreity than the
actual delete query code itself. But ive joined all the tables back on and
its working just fine. Ill keep you posted if i learn why this was.

with regards

Amit Patel