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?
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.
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!!