August 31, 2007 at 9:29 am
We have a situation where trying to delete rows from a table where
another table has a foreign key into, we get this error message:
{sysmessages error=547}
Message thrown:
Master record missing.
DELETE statement conflicted with COLUMN REFERENCE constraint . The conflict occurred in database
General SQL error.
Now, we also have some custom error messages that are being raised in
delete triggers and this is the one that I want the above to throw.
{sysmessages error=60005}
Message thrown:
General SQL error.
TSWERR60005:Cannot delete row in {SYCFVENDOR} with matching rows in dependent table {MDCFPRODUCT_VENDOR}.
However, this particular table has 25 referenced constraints and I
need to write a delete trigger to handle this.
Any help here would be greatly appreciated. If I can get help showing
how to handle one of these constraints, I think I can do the rest.
************************************************************************
{ Please be aware that I did not design this system 😉 }
Here are the constraints:
Storeworks.dbo.AICFDEFAULT: FK_AICFDEFAULT_AICFACCOUNT
Field: ACCOUNT_KEY->AICFACCOUNT.ACCOUNT_KEY
Storeworks.dbo.DBGBBANK: FK_DBGBBANK_AICFACCOUNT11
Field: DRAFT_ACCOUNT_KEY->AICFACCOUNT.ACCOUNT_KEY
Storeworks.dbo.DBGBBANK: FK_DBGBBANK_AICFACCOUNT5
Field: DEP_ACCOUNT_KEY->AICFACCOUNT.ACCOUNT_KEY
Storeworks.dbo.DBGBBANK: FK_DBGBBANK_AICFACCOUNT7
Field: CHK_ACCOUNT_KEY->AICFACCOUNT.ACCOUNT_KEY
Storeworks.dbo.DBGBBANK: FK_DBGBBANK_AICFACCOUNT9
Field: EFT_ACCOUNT_KEY->AICFACCOUNT.ACCOUNT_KEY
Storeworks.dbo.DBGBCARD: FK_DBGBCARD_AICFACCOUNT
Field: ACCOUNT_KEY->AICFACCOUNT.ACCOUNT_KEY
Storeworks.dbo.DBGBCASH_ACCT_FOR: FK_DBGBCASH_ACCT_FOR_AICFACCOUNT
Field: ACCOUNT_KEY->AICFACCOUNT.ACCOUNT_KEY
Storeworks.dbo.DBGBCASH_ACCTED_FOR: FK_DBGBCASH_ACCTED_FOR_AICFACCOUNT
Field: ACCOUNT_KEY->AICFACCOUNT.ACCOUNT_KEY
Storeworks.dbo.DBGBDEPARTMENT: FK_DBGBDEPARTMENT_AICFACCOUNT
Field: ACCOUNT_KEY->AICFACCOUNT.ACCOUNT_KEY
Storeworks.dbo.DBGBOTHER_REVENUE: FK_DBGBOTHER_REVENUE_AICFACCOUNT
Field: ACCOUNT_KEY->AICFACCOUNT.ACCOUNT_KEY
Storeworks.dbo.DBGBPAID_IN: FK_DBGBPAID_IN_AICFACCOUNT
Field: ACCOUNT_KEY->AICFACCOUNT.ACCOUNT_KEY
Storeworks.dbo.DBGBPAID_OUT: FK_DBGBPAID_OUT_AICFACCOUNT
Field: ACCOUNT_KEY->AICFACCOUNT.ACCOUNT_KEY
Storeworks.dbo.DBGBTAX: FK_DBGBTAX_AICFACCOUNT
Field: ACCOUNT_KEY->AICFACCOUNT.ACCOUNT_KEY
Storeworks.dbo.FLGBGRADE: FK_FLGBGRADE_AICFACCOUNT4
Field: COGS_ACCOUNT_KEY->AICFACCOUNT.ACCOUNT_KEY
Storeworks.dbo.FLGBGRADE: FK_FLGBGRADE_AICFACCOUNT6
Field: INV_ACCOUNT_KEY->AICFACCOUNT.ACCOUNT_KEY
Storeworks.dbo.FLGBGRADE: FK_FLGBGRADE_AICFACCOUNT8
Field: ADJ_ACCOUNT_KEY->AICFACCOUNTACCOUNT_KEY
Storeworks.dbo.FLGBPRODUCT: FK_FLGBPRODUCT_AICFACCOUNT
Field: ACCOUNT_KEY->AICFACCOUNT.ACCOUNT_KEY
Storeworks.dbo.FLGBTAX: FK_FLGBTAX_AICFACCOUNT
Field: ACCOUNT_KEY->AICFACCOUNT.ACCOUNT_KEY
Storeworks.dbo.MDGBADJ_CATG_XREF: FK_MDGBADJ_CATG_XREF_AICFACCOUNT
Field: ACCOUNT_KEY->AICFACCOUNT.ACCOUNT_KEY
Storeworks.dbo.MDGBADJUSTMENT: FK_MDGBADJUSTMENT_AICFACCOUNT
Field: ACCOUNT_KEY->AICFACCOUNT.ACCOUNT_KEY
Storeworks.dbo.MDGBCATEGORY: FK_MDGBCATEGORY_AICFACCOUNT13
Field: STORE_USE_ACCOUNT_KEY->AICFACCOUNT.ACCOUNT_KEY
Storeworks.dbo.MDGBCATEGORY: FK_MDGBCATEGORY_AICFACCOUNT4
Field: INV_ACCOUNT_KEY->AICFACCOUNT.ACCOUNT_KEY
Storeworks.dbo.MDGBCATEGORY: FK_MDGBCATEGORY_AICFACCOUNT6
Field: COGS_ACCOUNT_KEY->AICFACCOUNT.ACCOUNT_KEY
Storeworks.dbo.MDGBCATEGORY: FK_MDGBCATEGORY_AICFACCOUNT8
Field: SALES_ACCOUNT_KEY->AICFACCOUNT.ACCOUNT_KEY
Storeworks.dbo.SYGBVENDOR: FK_SYGBVENDOR_AICFACCOUNT
Field: ACCOUNT_KEY->AICFACCOUNT.ACCOUNT_KEY
August 31, 2007 at 10:29 am
you need to delete all the records from the foreign key tables before you delete the rows from the primary key tables.
so if you have an order table with primary key of OrderID, and a OrderDetails table with a foreign key that references OrderID on the orders table, you need to delete all the orderdetail records first, then delete the order record.
Hope this helps
August 31, 2007 at 10:55 am
I do not intend to allow the deletion to occur because of the child records.
I just want to show the custom error message.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply