Results 1 to 11 of 11
  1. #1
    Join Date
    Nov 2003
    Posts
    9

    Unanswered: can we update the primary key..

    Hi,

    we have hotel reservation system...where hotelphone is primary key...
    now we want to update hotelphone ...we couldnt do that...

    may be we r using this as foreign key in many tables...that is y?..

    pls reply us..

    thanx....

  2. #2
    Join Date
    Nov 2003
    Location
    Bangalore, INDIA
    Posts
    333

    Thumbs up

    Hi,

    Yes, the problem is due to parent child relationship only.
    SATHISH .

  3. #3
    Join Date
    Mar 2008
    Posts
    40
    Can anyone one reply ?

    I have a same problem. Need a solution for newbies like me !!!

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Yes, the problem is due to parent child relationship only.

    After deleting all child rows, you'll be able to change the PK.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Mar 2007
    Posts
    623
    > Need a solution for newbies like me !!!
    Change your design so your primary keys are immutable (eg. using surrogate keys) !!!

    You may find more complex solution in Updating child table automatically thread on AskTom (make the foreign keys DEFERRABLE and update it in the parent table and all child tables in one transaction).

  6. #6
    Join Date
    Nov 2010
    Posts
    3
    Hi all, i have a problem that i can't solve. I create a table with a primary key, no foreign key and it's a stand-alone table. I can insert values but I can't update the value of primary key. Oracle shows a messeage that: "not available to edit" at primary key textbox. In HR database, every primary key can be edited. How can i solve it?

  7. #7
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by TearKoshan View Post
    Hi all, i have a problem that i can't solve. I create a table with a primary key, no foreign key and it's a stand-alone table. I can insert values but I can't update the value of primary key. Oracle shows a messeage that: "not available to edit" at primary key textbox. In HR database, every primary key can be edited. How can i solve it?
    It is possible to update a row by using simple UPDATE statement. Database will raise "ORA-00001: unique constraint violated" exception if the primary key value matches any existing row.

    As "textbox" is not part of the database, it would be useful to state which concrete tool you are using. The error message guides to some "textbox" property not allowing to edit/modify the value. Does not that tool (whatever it is) have any kind of online help (F1 button)?

  8. #8
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >How can i solve it?
    it works for me.
    Code:
    SQL> CREATE TABLE t1 (id NUMBER PRIMARY KEY, val1 VARCHAR2(30));
    
    Table created.
    
    SQL> insert into t1 values(1,'A');
    
    1 row created.
    
    SQL> commit;
    
    Commit complete.
    
    SQL> update t1 set id= 2 where id = 1;
    
    1 row updated.
    
    SQL> commit;
    
    Commit complete.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  9. #9
    Join Date
    Nov 2010
    Posts
    3
    Thank guys. But I do it with interface, not with code. Everythings is OK when i do with code. But when I edit the values of a table in interface, Oracle doesn't allow to me to edit value of primary key. I will post some photos late.

  10. #10
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by TearKoshan View Post
    But when I edit the values of a table in interface, Oracle doesn't allow to me to edit value of primary key
    Then it's apparently not an Oracle problem, but a problem with whatever frontend you are using.

  11. #11
    Join Date
    Nov 2010
    Posts
    3
    I found out it. When i set data type is CHAR or NVARCHAR2, i can't update the values, the orthers is OK

Posting Permissions

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