Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1360
  • Last Modified:

MASTER DETAIL RELATIONSHIP FORM 6i

Hi,
   if you delte records in a master-detail relationship how can we do that if one of the rule of deleting is that "you cannot delte records until the master table or parent still contains records'
0
therooster
Asked:
therooster
  • 3
  • 3
  • 2
  • +1
1 Solution
 
soraCommented:
Normally, it would be the other way round. i.e - you cannot delete master/parent records if there are still child (or foreign key table) records.

Actually the easiest way to do this would be to enforce the foreign key referential integrity constraint in the database itself.


Lets say you have a DEPT table

DEPT
----
DEPT_NO
DEPT_NAME


EMP
---
EMP_NO
EMP_NAME
DATE_OF_JOINING
etc


EMP_DEPT_INFO
--------------
EMP_NO
DEPT_NO


In the above case you can add a foreign key (without delete cascade) to EMP_DEPT_INFO to both EMP and DEPT. This way, you cannot delete the EMP record or the DEPT record until the EMP_DEPT_INFO record is first deleted.

ALTER TABLE EMP_DEPT_INFO ADD CONSTRAINT fk_emp_no FOREIGN KEY (emp_no) REFERENCES EMP (EMP_NO);

and

ALTER TABLE EMP_DEPT_INFO ADD CONSTRAINT fk_dept_no FOREIGN KEY (dept_no) REFERENCES DEPT (DEPT_NO);

If your rule is not the typical parent-child relationship, you can always write a trigger on the table (whose records you are deleting) and inside the trigger check if there are any records in the other parent table. If found, then raise an exception. This exception can be trapped in your code - from where the deletion is being done.


sora
0
 
jtriftsMI and AutomationCommented:
I agree that this is better implemented on the database side through the use of foreign key constraints.

If you insist on implementing the business rule from Forms 6i (say if you have different applications accessing the same data and only one is not allowed to delete "children", while the others might...)

You could put an on delete trigger trigger that checks to see if a record exists in the "parent" and then rollback (perhaps with  a form_trigger_failure) if you find one.

Alternatively, you make the application invoke the delete through a button press and then put the check in the when-button-pressed trigger...where if the record is found it issues a message, and if not founf then it deletes the child.

Note that if you have set this up as a true master-detail relationship, this will be such that the ONLY way you can delete a parent record is if you delete the child first or set an ON DELETE CASCADE constraint for the key.

Regards,
JT
0
 
Mark GeerlingsDatabase AdministratorCommented:
Please clarify your question.  Do you want to support deleting records from master-detail tables?  If so, deleting records from the detail table should be no problem.  Do you want the master table also deleted if all details are deleted?  

Or do you want the form (or database) to prevent master records from being deleted if detail records exist?

Or do you want the form (or database) to automatically delete the detail records if a master record is deleted?

All of these are possible, so please explain what you would like to do.
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
theroosterAuthor Commented:
i prefer this question markgeer.

Or do you want the form (or database) to automatically delete the detail records if a master record
is deleted?

0
 
Mark GeerlingsDatabase AdministratorCommented:
If you want the database to delete detail records when master records are deleted (the best way to do it my opinion) then drop the existing foreign key constraint between the master and detail tables (if any) and re-create it including "on delete cascade".

If for some reason you do not want to have the database do this, you can create a pre-delete trigger on the master block of the form that will delete any/all related detail records before the master record is deleted.  Of cource you will have to change the properties of the block relationship to allow the master delete while detail records exist.
0
 
soraCommented:
therooster

The answers to questions in your latest post and those suplied by markgeer are available in the previous posts also


sora
0
 
theroosterAuthor Commented:
Okey,
  let me just check which of your suggestion works
0
 
theroosterAuthor Commented:
i forgot...
   a have a button that is used for deleting..
   if we put the code in the when-button-press
   would it be okey if we will do it this way..
   go_block('employee_leave');
   delete_record;
   commit;
   go_block('employee_status');
   delete_record;
   commit;
   go_block('employee_master');
   commit
or what code should be written in pre-delete trigger?
0
 
soraCommented:
therooster-
I think you have too many questions open on the same thread

sora
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now