May 19, 2017 at 3:56 pm
Our database's employee table has the “Username” column as its primary key – this matches their Active Directory username, and never changes; except when it does. This table was well entrenched before I arrived, and the username column is referenced by around 50 foreign keys.
I’ve received an email from our dev. team leader who needs to change the username of a user who recently married, asking why those foreign keys have cascading update and delete turned off.
My response was that this is a design decision to avoid unexpected data modifications caused by update or deletes. I noted I’d rather have us manage dependencies in code than have surprise deletes because someone set cascading delete on, or accidental updates to tables that need to be point-in-time snapshots. We’ve been stung by these in the past, and I believe it’s too easy to take cascading update and delete too casually – I’d rather have us write a stored procedure that needs executed to do the relevant changes.
I volunteered to write a stored procedure to rename an employee, which can be ran to perform the cascading updates. This was accepted, and is now ready for use.
I could do with some advice on this one - is the above reinventing the wheel based on paranoia, or is the above a respectable position to take? I’m concerned my position may be too simple; perhaps I'm being too careful, and my default should be to enable cascading updates and deletes on dependent tables, and turn off cascading update and delete only where it's identified it could cause problems.
Andrew P.
May 23, 2017 at 9:45 am
I'd say that, if you ALWAYS want to cascade an update/delete, then enable it. If there will regularly be exceptions, as it sounds is the case in your situation, then it's reasonable to handle these updates/deletes via stored procedures.
May 23, 2017 at 11:24 am
If the tables are not too large, you could even consider using CASCADE only when deliberately making changes, i.e., drop the FK constraints, recreate them as CASCADE, change the name(s), drop the constraints, recreate them as originally specified. That would prevent you from having to write and maintain fairly complex code.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
May 26, 2017 at 10:38 pm
Thanks guys, I appreciate the advice. I went with adding a new record to the parent table (tblEmployee) with the new primary key value, updating all referencing column values to the new value, then deleting the old record.
Scott - I expect this will work well in this specific use-case, but I'll use the technique you've described if I see this again; that looks to be a more robust approach, and it's much appreciated.
Andrew
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply