October 8, 2009 at 9:44 am
I have a T-SQL Procedure that Deletes a row from a table. There are times when this fails due to violating a foreign key contraint. This leads me to ask the following questions:
1. Is there something I could do that would check to see if the delete will violate the foreign key contraint BEFORE I issue the delete command?
2. If the answer to the first question is no, then I need to be able to trap for the violation of the foreign key constraint in my try/catch block so that I could send a nicer error message to the users. How do I trap for this error?
Thanks
ps. In case it matters my catch block looks like:
Begin Catch
Declare @ErrMsg nvarchar(2047)
Set @ErrMsg = Error_Message()
If @@TranCount > 0
Begin
RollBack Transaction
End
RaisError(@ErrMsg, 16, 1)
End Catch
October 8, 2009 at 9:51 am
One way of doing it (without issuing an error from the RDBMS), would be to do something like this:
IF NOT EXISTS(SELECT 'X' FROM dbo.childtable t1 WHERE t1.fk_reference = 'Value you are trying to delete')
BEGIN
--Run your DELETE statement
END
ELSE
BEGIN
--Return -1, soft error, friendly error message, delete this ELSE/BEGIN-END block of code and do nothing, etc.
END
MJM
October 8, 2009 at 10:32 am
delete from MyTable
where
--- All Other Selection Criteria ---
and MyTable.ForeignKeyColumn not in
( Select MyOtherTable.PrimaryKeyCol from MyOtherTable )
October 8, 2009 at 10:59 am
My SQL is kind of weak. So either I didn't really understand the previous posts (which I thank you for) or my question was confusing. Let me give you an example of what I mean.
I have a table called Customer. Customer has a Primary Key of CustomerNumber. In addition I have the following 3 tables SalesOrders, PurchaseOrders, CustomerContacts. Each of these tables has a CustomerNumber field that is a foreign key to the Customer Table.
If a customer becomes inactive my users want to delete that customer. If the customer number is being used by one of the 3 tables that I mentioned the users get the foreign key constraint message. I would like to be able to trap for that error in my catch block so I could issue a more user friendly message or skip the delete statement from executing.
I could issue a select statement against the SalesOrders, PurchaseOrders and CustomerContacts table to see if they reference the customer number in question but that would mean that everytime I add a new table that references the customer I would have to change my stored procedure. There must be a better way.
Thanks
October 8, 2009 at 11:30 am
Hi,
This is something I think would be very useful and wouldn't require huge rework from the SQL Server team: to be able to define user friendly messages, something along these lines for FK violation
create table CustomerOrders
(
value_column varchar(10),
fk_column int references Customer(pk_column) custom_error_message(547, 'Customer cannot be deleted because there are Orders for him'),
another_column char(1)
)
In your case, if deleting Customer with Orders and Contacts is OK, you may define foreign key constraints in these tables with ON DELETE CASCADE. This will allow for deleting Customer and all data related to it.
You can customize error message in the catch block, as in:
begin catch
rollback
if error_number() = 547
raiserror('Customer cannot be deleted because there are Orders or Contacts associated with him', 16, 1)
end catch
The other option is to check for existence of referencing records as Michael and Mark wrote.
Regards
Piotr
...and your only reply is slàinte mhath
October 8, 2009 at 2:54 pm
Well if you want a bad approach and want to delete the orders, shipments, invoices, etc...
Change the Foreign key to ON DELETE CASCADE
That will clean it up.
Otherwise testing the tables that depend on the foreign key reference is the best way to go.
That or simply instead of deleteing the customer do the following.
EXEC sp_rename 'Customers','Customersdata'
ALTER TABLE Customersdata ADD column customerdeleted int NOT NULL DEFAULT = 0
CREATE VIEW Customers
AS
SELECT
<all fields but customerdeleted>
FROM
Customersdata
WHERE
customerdeleted = 0
Then when they delete a customer, just set the customerdeleted field to 1 in the Customersdata table.
October 8, 2009 at 2:56 pm
Trust me when I say this.
Your users do not want to delete customers. Especially not the ones with sales information that depends on the customer info.
Later on somebody is going to want to run a historical sales report, and all the deleted info will be gone and numbers won't match up to prior reported numbers which will cause nothing but heartache.
October 8, 2009 at 3:07 pm
mtassin (10/8/2009)
Trust me when I say this.
Yes, trust him ^^. Usually if there's any kind of purge occuring in a system (an actual DELETE, especially for customer data) I will soon get a request for an IsActive flag or similar.
mjm
October 8, 2009 at 3:30 pm
mtassin (10/8/2009)
Trust me when I say this.Your users do not want to delete customers. Especially not the ones with sales information that depends on the customer info.
Later on somebody is going to want to run a historical sales report, and all the deleted info will be gone and numbers won't match up to prior reported numbers which will cause nothing but heartache.
While I completely agree with you, I have no choice. I do however write the cutomer row to a historical table within the same transaction as the delete.
October 9, 2009 at 6:39 am
From reading the posts, it looks like I have two choices.
1) Before issuing the delete command, check each of the tables that reference the CustomerNumber to see if the Customer Number that is being deleted is in use or
2) Catch the error and send a more user friendly message to the user.
Option #1 - Question
The problem with this option is that everytime a new table is created that depends on the Customer table, I would need to change this stored procedure. Is there a way to get around this problem?
Option #2 - Question
Is there a way that I can determine which Foreign Key constraint was violated? I would like to use that information in the error handler. For instance, if the constraint that was violated was the FK_SalesOrder_CustomerNumber, I would like to create an error message that says:
The change cannot be completed because the Customer Number is being used by FK_SalesOrder_CustomerNumber
Thanks
October 9, 2009 at 6:59 am
Write an INSTEAD OF DELETE trigger that passes the ID to a stored proc that iterates through the constraints on the table and checks them via some nasty cursor?
October 9, 2009 at 9:05 am
meichner (10/9/2009)
Option #1 - QuestionThe problem with this option is that everytime a new table is created that depends on the Customer table, I would need to change this stored procedure. Is there a way to get around this problem?
Option #2 - Question
Is there a way that I can determine which Foreign Key constraint was violated? I would like to use that information in the error handler. For instance, if the constraint that was violated was the FK_SalesOrder_CustomerNumber, I would like to create an error message that says:
The change cannot be completed because the Customer Number is being used by FK_SalesOrder_CustomerNumber
Thanks
1. A workaround would be to maybe create a VIEW (yes, it would need to be updated with each table addition) that is called by these types of stored procs which detects the presence of Foreign Keys in their respective tables. I am thinking along the lines of:
IF EXISTS(SELECT 'X' FROM dbo.someview WHERE CustomerID = @Foo)
If a value exists for any other columns, raise an error, else perform the delete.
2. Here's a quick (and NOT thoroughly tested) way of obtaining the particular FK violation (this assumes an "FK_:" naming convention and is working with strings so YMMV):
DECLARE
@CustomerID INT,
@ErrorMsg VARCHAR(512)
SET @CustomerID = 1
BEGIN TRY
DELETE FROM dbo.Customer WHERE CustomerID = @CustomerID
END TRY
BEGIN CATCH
SET @ErrorMsg = ERROR_MESSAGE()
SET @ErrorMsg = SUBSTRING(@ErrorMsg, CHARINDEX('FK_', @ErrorMsg), LEN(@ErrorMsg))
SELECT 'The change cannot be completed because the Customer Number is being used by ' + SUBSTRING(@ErrorMsg, 1, CHARINDEX('"', @ErrorMsg) - 1)
END CATCH
GO
MJM
October 9, 2009 at 9:57 am
Mark Marinovic (10/9/2009)
meichner (10/9/2009)
2. Here's a quick (and NOT thoroughly tested) way of obtaining the particular FK violation (this assumes an "FK_:" naming convention and is working with strings so YMMV):
DECLARE@CustomerID INT,
@ErrorMsg VARCHAR(512)
SET @CustomerID = 1
BEGIN TRY
DELETE FROM dbo.Customer WHERE CustomerID = @CustomerID
END TRY
BEGIN CATCH
SET @ErrorMsg = ERROR_MESSAGE()
SET @ErrorMsg = SUBSTRING(@ErrorMsg, CHARINDEX('FK_', @ErrorMsg), LEN(@ErrorMsg))
SELECT 'The change cannot be completed because the Customer Number is being used by ' + SUBSTRING(@ErrorMsg, 1, CHARINDEX('"', @ErrorMsg) - 1)
END CATCH
GO
MJM
Thanks so much for this. While I haven't tested it yet, I believe it will work well for me.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply