473,320 Members | 2,193 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

how to tread the primary key and foreign key in fragmentation

24
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?
Oct 24 '07 #1
3 2254
amitpatel66
2,367 Expert 2GB
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?
R u not able to fragment the salesperson table?
Oct 25 '07 #2
lucoin
24
What is the problem that you are facing here?
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?
Oct 25 '07 #3
amitpatel66
2,367 Expert 2GB
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!!
Oct 25 '07 #4

Sign in to post your reply or Sign up for a free account.

Similar topics

1
by: Lannsjo | last post by:
I need to change my primary key column type from smallint to int. I have tried: ALTER TABLE livegroup MODIFY id INT UNSIGNED NOT NULL AUTO_INCREMENT; But get an error message certainly since my...
3
by: William D. Bartholomew | last post by:
I'm working on a system that is very address-centric and detection of duplicate addresses is very important. As a result we have broken addresses down into many parts (DDL below, but I've left out...
6
by: Andreas | last post by:
Hello list, what about uniqueness of inherited primary keys ? eg you have : create table objects ( id int4, date_created timestamp(0), primary key (id)
4
by: Fred | last post by:
Hi. What is the usual and what are the possible fields to use for the primary key of an intersecting table of a many-to-many relationship? I would think the typical, most common fields would...
13
by: Tibor | last post by:
I am using PostgreSQL 7.4.1 (only through psql) I know, that the command ALTER TABLE OFFICES DROP PRIMARY KEY (CITY); and its foreign key equivalent: ALTER TABLE SALESREPS DROP CONSTRAINT
9
by: Geoff | last post by:
Hi I was wondering if anybody could advice me on the following scenario: Suppose I have two data tables A and B respectively. Table B contains a foreign key to a primary key in A. If I add...
9
by: sonal | last post by:
Hi all, I hv started with python just recently... and have been assigned to make an utility which would be used for data validations... In short we take up various comma separated data files for...
3
by: Rick Kay | last post by:
I have a table that has a Primary key and a foreign key. The primary key is NOT an Identity field, however, the foreign key is. I would like to know if there is a way to have the foreign key reset...
6
by: ravichoudhari | last post by:
i came accross requirement of multiple foreign keys in a table referencing the same primary key another table. i created the table relations using the relations editor in access. even though i...
4
by: Peter | last post by:
I am interested in informed feedback on the use of Constraints, Primary Keys and Unique. The following SQL statement creates a Bands tables for a database of bookings Bands into Venues, where the...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.