Post reply

changing datatype of primary key field

  • November 28, 2004 at 4:16 pm

    #163251

    Hi Friends

    I have table which has primary key of datatype varchar(5).

    we initially thought it may contain some chars so we defined it as varchar.

    but so far it has only numbers to it and we decided that we change it to int.

    This table is being referenced by 5 tables.

     
    could you tell me whats best way of handling this issue.
     
    My initial idea is to create a new field(of int) and re-create foreign key relation ship with child table pointing to new pkey.ofcourse i'll have to change all views ,sps which r using varchar pkey.
     
    am wondering u guys have better ideas.

    Thank u very much.

  • November 28, 2004 at 7:58 pm

    #531661

    You could start with making the changes using EM.  If you make the changes to the parent table, it will make prompt you that the related tables require changes and will then make them for you - all based on the foreign key relationships.  Behind the scenes, it will create new tables, insert the current data into the new tables drop the current tables and then rename the new tables.

    This should work quite nicely if all of the data is numeric. 

    This then leaves you with changing the view etc

  • November 28, 2004 at 8:06 pm

    #531662

    That's easy
    didn't know EM will change child tables automatically
    Thank you very much Happycat59

  • November 28, 2004 at 8:20 pm

    #531663

    I just tried and it worked beautifully 🙂
    Thank you very much Happycat

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply