Updating a key column [message #387757] |
Fri, 20 February 2009 08:34 |
scorpio4frenz
Messages: 48 Registered: October 2008
|
Member |
|
|
I have three tables: "people", "section_code_dtls", and "people_sections". "People" table shows details of contacts, "section_code_dtls" has details of cities (also called sections), and "people_sections" is a relationship table between "people" and "section_code_dtls". (many to many) In the "people_sections" table, the PK is a composite key between PK's of "people" table and "section_code_dtls" tables. I'm trying to figure out a way to be able to update only the "section_code" column in the "people_sections" table without changing the corresponding "people_id" value. I'm trying to achieve this through my Oracle Form. (User would like to be able to update a contact's city ). It cannot be done at the moment because it is not possible to update a key column.
Any suggestions on how to handle this situation?
Basic table structures look something like this:
CREATE TABLE SECTION_CODE_DTLS
(
SECTION_CODE VARCHAR2(5 BYTE) NOT NULL,
SECTION_DESCRIPTION VARCHAR2(25 BYTE) NOT NULL,
CONSTRAINT sectcodedtls_pk PRIMARY KEY (section_code)
);
CREATE TABLE PEOPLE
(
PEOPLE_ID VARCHAR2(5 BYTE) NOT NULL,
PEOPLE_DESCRIPTION VARCHAR2(25 BYTE) NOT NULL,
CONSTRAINT people_pk PRIMARY KEY (people_id)
);
CREATE TABLE PEOPLE_SECTIONS
(
SECTION_CODE VARCHAR2(5 BYTE) NOT NULL,
PEOPLE_ID VARCHAR2(25 BYTE) NOT NULL,
CONSTRAINT peoplesects_pk PRIMARY KEY (section_code, people_id),
CONSTRAINT peoplesects_fk1 FOREIGN KEY (section_code)
REFERENCES section_code_dtls(section_code),
CONSTRAINT peoplesects_fk2 FOREIGN KEY (people_id)
REFERENCES people(people_id),
); [EDITED by DJM: split long lines]
[Updated on: Tue, 24 February 2009 00:43] by Moderator Report message to a moderator
|
|
|
Re: Updating a key column [message #388251 is a reply to message #387757] |
Tue, 24 February 2009 00:48 |
|
djmartin
Messages: 10181 Registered: March 2005 Location: Surges Bay TAS Australia
|
Senior Member Account Moderator |
|
|
Why would you 'update' an entry in 'PEOPLE_SECTIONS'. If the person moves wouldn't you create a new record for the new city and delete the old record?
David
|
|
|