Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2003
    Posts
    50

    Unanswered: Updating tables with references

    Hi!

    Basically I have one table Store with the atribute storeName being its Primary Key and a table soldBy with storeName being a FOREIGN KEY from table Store. I want to update a name of a store to some other name so I have to do it in both tables.
    WIth the SQL:

    Code:
    UPDATE (SELECT storeName FROM soldBy NATURAL JOIN Store)
    SET storeName='Virgin2' where storeName='Virgin';
    I get an ORA-01779: cannot modify a column which maps to a non key-preserved table.

    Is it possible to do it?

    Thanks.

    Code:
    CREATE TABLE Store(
    	storeName VARCHAR(15),
    	logo VARCHAR(255),
    	storeLink VARCHAR(255),
    	PRIMARY KEY (storeName)
    	);
    
    CREATE TABLE soldBy(
    	albName VARCHAR(100),
    	albYear NUMBER(4),
    	storeName VARCHAR(15),
    	albLink VARCHAR(255) NOT NULL,
    	PRIMARY KEY (albName, albYear, storeName),
    	FOREIGN KEY (albName, albYear)
    		REFERENCES Album(albName, albYear),
    	FOREIGN KEY (storeName)
    		REFERENCES Store(storeName)
    	);
    Ah! Não ser eu toda a gente e toda a parte!

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    It is NOT possible.
    You can NOT change a primary key value.
    You can delete the row & then INSERT a new row.

  3. #3
    Join Date
    Dec 2003
    Posts
    50
    I can't delete a storeName in table Store if it has child registers in table soldBy. I have to delete them first, then delete the storeName from Store and create the new one.

    But all I wanted was to change the storeName in both tables. If I have to delete the registers in soldBy, how can I insert them with the same entries for the other atributes and the new storeName created?

    Is there a way to do this?
    Ah! Não ser eu toda a gente e toda a parte!

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    What is stopping you from inserting the new records with the desired values, BEFORE deleting the old records and the doing a final COMMIT?

  5. #5
    Join Date
    Dec 2003
    Posts
    50
    Nevermind! I found the way to do it!

    Code:
    INSERT INTO Store Values('NewName','samelogo','samestoreLink');
    UPDATE soldBy SET storeName='NewName' where storeName='oldName';
    DELETE FROM Store WHERE storeName='oldName';
    Ah! Não ser eu toda a gente e toda a parte!

  6. #6
    Join Date
    Dec 2003
    Posts
    50
    Thanks for your patience and your prompt help!
    Ah! Não ser eu toda a gente e toda a parte!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •