#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2004
    Posts
    7
    Rep Power
    0

    Question how to alter primary key column?


    Hi!

    Iíve got problems to alter a column witch is a primary key.
    I want to change a column from VARCHAR(50) to VARCHAR(100)

    This works only with non primary key columns:
    ALTER TABLE table ALTER COLUMN column TYPE VARCHAR(100)

    Can someone give me a example to do that?

    Thanks!
    Torsten
  2. #2
  3. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2004
    Location
    Romania
    Posts
    173
    Rep Power
    10

    Thumbs up


    well ... first of all ... i think you should consider using an integer primary key.
    Code:
    CREATE TABLE ..... (ID INTEGER NOT NULL PRIMARY KEY ...........)
    anyway ... to modify the primary key you must drop the constraints related to the primary key (not null, primary key) drop the index (RDB$PRIMARY...) and then you are left with a normal column which you can alter the way you want it.
    If i've been helpful, please add to my reputation.
    My unfinished site: http://www.dever.ro
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2004
    Posts
    7
    Rep Power
    0

    Unhappy


    Thanks for your answer

    But Iím not able to drop column constraints!

    Iíve tried a lot of things Ė for example this:
    ALTER TABLE table ALTER COLUMN column DROP CONSTRAINT PRIMARY KEY

    But nothing worked!
  6. #4
  7. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2004
    Location
    Romania
    Posts
    173
    Rep Power
    10
    The correct sql would be something like:
    (this is a real example)
    (the primary key constraint)
    Code:
    ALTER TABLE TEST DROP CONSTRAINT INTEG_23
    (the not null constraint)
    Code:
    ALTER TABLE TEST DROP CONSTRAINT INTEG_18
    after that you can alter your column:
    Code:
    ALTER TABLE table ALTER column ....
    If i've been helpful, please add to my reputation.
    My unfinished site: http://www.dever.ro
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2004
    Posts
    7
    Rep Power
    0

    Lightbulb


    Thanks again!

    But the problem is Ė the constraint changes always.
    In my case, itís INTEG_47!

    Now Iíve found a way to delete a PRIMARY KEY.
    But it takes two steps:
    select rdb$constraint_name from rdb$relation_constraints where rdb$constraint_type='PRIMARY KEY' and rdb$relation_name='tablename';
    ALTER TABLE tablename DROP CONSTRAINT <result of selection>;

    Why isnít it possible, just to say DROP PRIMARY KEY ?

    Sincerely Torsten
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,784
    Rep Power
    348
    Why don't you simply give your primary key a name?
    Code:
    CREATE TABLE foo 
    ( 
      id integer
    ...
    );
    ALTER TABLE foo
      ADD CONSTRAINT foo_pk PRIMARY KEY (id);
  12. #7
  13. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2004
    Location
    Romania
    Posts
    173
    Rep Power
    10
    Originally Posted by torstenzey
    ...
    Why isnít it possible, just to say DROP PRIMARY KEY ?
    ...
    In my opinion you should have no reasons to delete a PRIMARY KEY especially when that is a FOREIGN KEY in another table and it is set by a GENERATOR !
    What you have now is a perfect example of a bad database design in the first place
    If i've been helpful, please add to my reputation.
    My unfinished site: http://www.dever.ro
  14. #8
  15. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2004
    Posts
    7
    Rep Power
    0
    Originally Posted by SilverDB
    In my opinion you should have no reasons to delete a PRIMARY KEY especially when that is a FOREIGN KEY in another table and it is set by a GENERATOR !
    What you have now is a perfect example of a bad database design in the first place
    @SilverDB:
    Haha!
    Please look at the top of the thread.
    I don't want to delete the primary key at all!

    @shammat:
    Thanks! I will try that!
  16. #9
  17. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2008
    Location
    BAGUIO CITY PHILIPPINES
    Posts
    1
    Rep Power
    0

    Erase Rdb$relation Constraints Failed Action Cancenceld By Trigger (1)


    Originally Posted by SilverDB
    The correct sql would be something like:
    (this is a real example)
    (the primary key constraint)
    Code:
    ALTER TABLE TEST DROP CONSTRAINT INTEG_23
    (the not null constraint)
    Code:
    ALTER TABLE TEST DROP CONSTRAINT INTEG_18
    after that you can alter your column:
    Code:
    ALTER TABLE table ALTER column ....
    Hi!

    Im trying to drop the constraints of my primarykey but the error ERASE RDB$RELATION CONSTRAINTS FAILED ACTION CANCENCELD BY TRIGGER (1) TO PRESERVE DATA INTEGRITY
    I tried to look for the trigger in the other tables and but i cant find any that uses my primary key (ID_NO).

    Where can i find the trigger? Please help...
  18. #10
  19. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2006
    Location
    Carlsbad, CA
    Posts
    2,057
    Rep Power
    383
    Where can i find the trigger? Please help...
    It is not thee trigger you should be looking for.
    It is the Foreign Key constraint linked to your PK.
  20. #11
  21. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2013
    Posts
    1
    Rep Power
    0

    how to set primary key in already field


    plz help me soon

IMN logo majestic logo threadwatch logo seochat tools logo