| Parent child table update (EMP and DEPT) (merged) [message #462643] |
Fri, 25 June 2010 07:48  |
heyit
Messages: 14
Registered: May 2010
Location: US
|
Junior Member |
|
|
EMP is master table and DEPT is child table. One of the Oracle form is based on DEPT (child) table. My requirement was to delete some of the department permanently which means departments, their all existing employee and other data needs to be deleted permanently from database and also while entering data, we should not see those departments in future also.
We went ahead and ran delete statement against the DEPT table (which is a child table) for specific departments and confirmed that all departments are gone from child table and master table as well.
Now even after confirmation of record deletion, when we are running query on the form, we are seeing associated value for those departments. I'm not able to figure out why?
My questions are:
- Did I make a mistake by deleting records from child?
- Whatever I did was correct and there is some other loop hole.
- Any other suggestion by which I can fix the issue.
Any help or suggestion would be really appreciated.
Thanks,
|
|
|
|
| Re: Parent child table update (EMP and DEPT) (merged) [message #462648 is a reply to message #462643] |
Fri, 25 June 2010 08:06  |
cookiemonster
Messages: 13938
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Generally dept is master not emp so you probably need to explain the relationship between those tables as you have it set up.
Also be an idea to tell us what query you are running in the form how exactly you are seeing data from those depts - what table/column.
|
|
|
|
| Re: Parent child table update (EMP and DEPT) (merged) [message #462652 is a reply to message #462648] |
Fri, 25 June 2010 08:19 |
heyit
Messages: 14
Registered: May 2010
Location: US
|
Junior Member |
|
|
Ok I was giving an example and here are the actual details:
Tables 'A' columns are:
Category
Amount
Trans_date
Tables 'B' columns are:
Customer
Category
Amount
Sys_Date
Form is based on table B which is a child table, while A is master table. On form we have:
Customer
Category
Amount
Sys_date
We have deleted categories from table b but their associated amounts are still being add up.
Please let me know if any additional information is required.
Thanks for help!
|
|
|
|
|
|
| Re: Parent child table update (EMP and DEPT) (merged) [message #462668 is a reply to message #462661] |
Fri, 25 June 2010 09:01 |
heyit
Messages: 14
Registered: May 2010
Location: US
|
Junior Member |
|
|
|
Basically we have deleted Category from B table ... yes I ran number of queries on both the tables and looks like categories are gone but their amounts are still being added on Form. Is it ok to delete records from child? or do we need to delete from parent only?
|
|
|
|
| Re: Parent child table update (EMP and DEPT) (merged) [message #462669 is a reply to message #462668] |
Fri, 25 June 2010 09:05 |
cookiemonster
Messages: 13938
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
heyit wrote on Fri, 25 June 2010 15:01Basically we have deleted Category from B table ... yes I ran number of queries on both the tables and looks like categories are gone but their amounts are still being added on Form.
Then presumably the category still exists somewhere - in tableA maybe? But really you need to tell us how this data is being added - it's your code that's doing it.
heyit wrote on Fri, 25 June 2010 15:01
Is it ok to delete records from child? or do we need to delete from parent only?
That's up to you - it's your data. I can't possibly know better than you what you can and can't delete.
|
|
|
|
|
|
| Re: Parent child table update (EMP and DEPT) (merged) [message #462680 is a reply to message #462673] |
Fri, 25 June 2010 10:44 |
cookiemonster
Messages: 13938
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
That's not really a scenario.
It depends on the data.
I've got lots of master-detail tables in my DB.
For some you would delete the parent and let the foreign keys get rid of the children.
For some you would delete the children manually and then the parent.
For some you would only ever delete the children.
It depends on what the data is used for.
Since my DB is different to your DB and I know nothing about your DB I can not tell you which option you should use.
If want to get rid of both and you're asking which way to do it - then have you got a foreign key between the 2 tables set to on delete cascade?
If you have you can delete from the master only, otherwise you need to do both.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
