Home » Developer & Programmer » Forms » Updating a key column
Updating a key column [message #387757] Fri, 20 February 2009 08:34 Go to next message
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 Go to previous message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT 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
Previous Topic: ora-01403
Next Topic: Scroll Bar Not Working
Goto Forum:
  


Current Time: Sat Dec 10 09:21:20 CST 2016

Total time taken to generate the page: 0.07666 seconds