I have a global schema include the relations customer, order, orderline, salesperson, and product. I fragment the salesperson into salesperson_MEL and salesperson_SYD , the salesperson in different branches may have shift. so what I should do with the salesperson relation in branches, if delete, how about the related order records. what should I deal with this?
-
-
What is the problem that you are facing here?Originally posted by lucoin
R u not able to fragment the salesperson table? -
Yes, I did. The problem is when I change the local salesperson to another branch. what should I do. for example, I want to shift one sales person from Sydney to Melbourne. yes, delete the salesperson from Sydney salesperson table, and insert into Melbourne salesperson table and change the location. but when I delete the salesperson from Sydney, how about the other tables has associate tuples to that person. If there any better solution to solve this problem?Originally posted by amitpatel66Comment
-
You will not be able to delete the parent data in case if it has any dependencies in another table. This will violate the constraint. In case if you want the data to be deleted from the dependent tables also, then you need to use ON DELETE CASCADE clause when you created a foreign key relation ship.Originally posted by lucoin
If you have not used on delete cascade clause, then do the following:
1. create table tn_sydney1 as seleect * from sydney_table
2. drop table sydney_table CASCADE CONSTRAINTS ( this will drop the table even there are any dependencies)
3. RENAME tn_sydney1 to sydney_table
4. Create primary key,foreign key relation between all tables and say ON DELETE CASCADE for foreign key relationship.
5. Delete from sydney_table (This will delete all dependent records from child table)
6. Now you can add this entry to melbourne table
I hope this helps!!Comment
Comment