Home » SQL & PL/SQL » SQL & PL/SQL » Error encountered while deleting a record with foreign key relationship. (Oracle 10G)
Error encountered while deleting a record with foreign key relationship. [message #627766] Sun, 16 November 2014 00:31 Go to next message
Asfakul
Messages: 43
Registered: July 2014
Member
Hi All,
I was doing some random things when I came accross this. I am trying to delete the record in EMPLOYEES(yeah the same employees table that comes by default Smile ) table , but couldn't apparently because of some circular dependency.

delete employees where employee_id=200 ;
delete  departments where manager_id=200;


can somebody let men know how to do the same?

Report message to a moderator

Re: Error encountered while deleting a record with foreign key relationship. [message #627767 is a reply to message #627766] Sun, 16 November 2014 00:40 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
Check for the constraints using *_CONSTRAINTS

Report message to a moderator

Re: Error encountered while deleting a record with foreign key relationship. [message #627772 is a reply to message #627766] Sun, 16 November 2014 03:01 Go to previous messageGo to next message
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

This is a good question.
How to delete a department and its manager (or all its employees)?
What does this mean for the business? How the business rules handle this case?
The problem is more a business question which will lead to the technical solution.

For instance, it could be, if you want to remove only the manager then there must be another manager for this department before (so your UPDATE the department row, first), and the manager must be assigned to another department or remove if you want to fire him (so you UPDATE or DELETE the manager row in EMPLOYEES table, after).

If you want to remove the whole department the rule could be: before deleting the department it has no more any manager; so you UPDATE department row to set its manager to null. And all the employees of this department must be assigned to another department before the department deletion (which seems quite logical for a real world point of view). The technical solution is then to UPDATE each employee row to set its department to another value or you delete them you want to fire the employees.
...
There you see the technical solution depends on the business rules.

Note that it might be possible (depending on the business rules) to define the action in case of deletion with the ON DELETE option of the foreign keys.

Report message to a moderator

Re: Error encountered while deleting a record with foreign key relationship. [message #627773 is a reply to message #627766] Sun, 16 November 2014 04:03 Go to previous messageGo to next message
John Watson
Messages: 8931
Registered: January 2010
Location: Global Village
Senior Member
Your example is perfect case for my general principle: "unless you have a good reason, create all constraints as DEFERRABLE INITIALLY IMMEDIATE."
Deferrable constraints (and of course non-unique indexes) give you much more flexibility in situations such as this where the order of operations is important.

[Updated on: Sun, 16 November 2014 04:04]

Report message to a moderator

Re: Error encountered while deleting a record with foreign key relationship. [message #627774 is a reply to message #627773] Sun, 16 November 2014 04:36 Go to previous messageGo to next message
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I agree with you as a quickfix to workaround for a block situation due a lack in design and specifications of business rules but not as a general method of designing an application.
The systematic usage of deferrable constraints tend to prevent designers to ask themselves "do we cover all the cases? is there any case missing?". Even if in the end all the constraints are satisfied, this does not mean that the data will be correct for a business or client point of view. Remember there are real people behind the EMPLOYEE table. Smile
In this case, for these statements, questions like "what does mean removing a manager for the department (and its employees)?", "what does mean removing a department for its employees?" should first raise and, I bet, it will appear that writing a procedure for these actions which implements the business rules would be the best approach of the issues and remove the need of deferrable constraint.

As you said, deferrable constraints give much more flexibility in such situations but flexibility should not be the first point to address. There are some cases where it can't be done without deferrable constraints but they're not in majority.

Report message to a moderator

Re: Error encountered while deleting a record with foreign key relationship. [message #627777 is a reply to message #627774] Sun, 16 November 2014 05:24 Go to previous messageGo to next message
John Watson
Messages: 8931
Registered: January 2010
Location: Global Village
Senior Member
Well, your comments have made me think a bit. I suspect that my preference for deferrable constraints (which I now question) comes from my years working on short-term contracts: a few days to fix a problem, convert to RAC, tune some SQLs, and so on. This means that I rarely have the opportunity to see the long term results of a solution, and perhaps look for too many quick fixes. Thank you for making me think.

Report message to a moderator

Re: Error encountered while deleting a record with foreign key relationship. [message #627779 is a reply to message #627766] Sun, 16 November 2014 06:33 Go to previous message
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
Senior Member
Asfakul wrote on Sun, 16 November 2014 01:31
can somebody let men know how to do the same?


Rule (implemented via self-referencing FK on nullable MANAGER_ID column) on HR.EMPLOYEES table states you can't delete employee who is a manager and has other employees working for him. So you have two options:

1. All employees working for to be deleted manager will be working for a different manager
2. All employees working for to be deleted manager will be working without manager.

Case 1 splits into two:

a) New manager is existing employee. Then we update manager id in departments table, update manager id for all employees who work for to be deleted manager and delete to be deleted manager from employee table.

b) New manager is new employee. Then we insert new manager into employees table, update manager id in departments table, update manager id for all employees who work for to be deleted manager and delete to be deleted manager from employee table.

Case 2. We update manager id with NULL for all employees who work for to be deleted manager, update manager id with NULL in departments table and delete to be deleted manager from employee table.

SY.

Report message to a moderator

Previous Topic: Calling Web Service using UTL_DBWS
Next Topic: Date comparison
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Wed Apr 24 17:23:21 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.