Results 1 to 9 of 9

Thread: Alter table

  1. #1
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665

    Unanswered: Alter table

    I have been looking to alter a column in a table and add a default value. Im trying to find a good script example to do this. my question now is why cant I do it in the GUI tool? When I select the table select alter and then select the column and press alter everything is grayed out. Im logging in as the admin in the sysadmin group. any thoughts as to why the qui will not let me alter the table?
    Jim
    ------------
    And back to SQL Server....I always find my way home
    View my Linkedin profile

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    You can add a column, but you cannot alter a column (except to change the length of a varchar column). This may change in version 8.2 (Stinger) due out later this year.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665
    Quote Originally Posted by Marcus_A
    You can add a column, but you cannot alter a column (except to change the length of a varchar column). This may change in version 8.2 (Stinger) due out later this year.
    OK I accept that. Can you help me with an alter table script. I cant seem to find a good example to base mine off of.
    Jim
    ------------
    And back to SQL Server....I always find my way home
    View my Linkedin profile

  4. #4
    Join Date
    Apr 2004
    Location
    Inside Intel
    Posts
    165

    Talking

    You HAVE to drop and recreate them.

    Newbie

  5. #5
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665
    Quote Originally Posted by dsusendran
    You HAVE to drop and recreate them.

    Newbie
    You have to be kidding me. I cant accept that I cant add a default after the table is created. I mean for gods sake i have the table populated.
    Jim
    ------------
    And back to SQL Server....I always find my way home
    View my Linkedin profile

  6. #6
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    God has nothing to do with it, and YOU WILL ACCEPT IT.

    However, it is quite easy to do the following (assume your table is called table_name)

    create new table called table_name2 the way you want all the columns defined.

    insert into table_name2 select * from table_name
    (if the table is very large and you don't want to log the inserts, then use not logged initially and execute the insert in the same unit of work, and then commit)

    rename table table_name to table_name3

    rename table table_name2 to table_name

    create indexes, views, rebind, etc
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  7. #7
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665
    Quote Originally Posted by Marcus_A
    God has nothing to do with it, and YOU WILL ACCEPT IT.

    However, it is quite easy to do the following (assume your table is called table_name)

    create new table called table_name2 the way you want all the columns defined.

    insert into table_name2 select * from table_name
    (if the table is very large and you don't want to log the inserts, then use not logged initially and execute the insert in the same unit of work, and then commit)

    rename table table_name to table_name3

    rename table table_name2 to table_name

    create indexes, views, rebind, etc

    I am still in complete shock that I have to do all off this just to add a default. Ill remember to have this worked out before i creat the next batch of tables.
    Jim
    ------------
    And back to SQL Server....I always find my way home
    View my Linkedin profile

  8. #8
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    You need to think carefully about any data integrity problems that might occur with changes to the existing values of the table if you switch to new default value. I think this may be why changing it on the fly is not allowed.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  9. #9
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665
    Quote Originally Posted by Marcus_A
    You need to think carefully about any data integrity problems that might occur with changes to the existing values of the table if you switch to new default value. I think this may be why changing it on the fly is not allowed.

    Unfortunatly the issue was not discused before the table was created and now there is a need for a default value. I didnt change an exhisting one, i needed to create one.
    Thanks for all the help
    Jim
    ------------
    And back to SQL Server....I always find my way home
    View my Linkedin profile

Posting Permissions

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