Post reply

Alter statement to change width of col

  • October 29, 2009 at 11:01 am

    #212733

    Hi

    I have to change the width of a PK col in a table ..it has 10 columns.

    In the alter should I reference all col's or only the one I am changing.

    example

    Table1

    colpk,col2,col3,col4,col5

    should it be

    alter table Table1

    (

    colpk datatype

    )

    or

    alter table Table1

    (

    colpk datatype,

    col1 datatype,

    col2 datatype,

    col3 datatype,

    col4 datatype,

    col5 datatype

    )

    Thanks

  • October 29, 2009 at 11:19 am

    #1072670

    Best practice is for each column you need to change should have a single ALTER TABLE statement for it.

    your syntax was way off, probably just peudocode, but the example below might help.

    to alter your PK, you'll have to drop constraints, or you'll get an error like this:

    CREATE TABLE EXAMPLE (EXAMPLEID CHAR(10) NOT NULL PRIMARY KEY,EXAMPLETEXT VARCHAR(30) )

    ALTER TABLE EXAMPLE ALTER COLUMN EXAMPLEID CHAR(50) NOT NULL

    Msg 5074, Level 16, State 1, Line 2

    The object 'PK__EXAMPLE__570123A0' is dependent on column 'EXAMPLEID'.

    Msg 4922, Level 16, State 9, Line 2

    ALTER TABLE ALTER COLUMN EXAMPLEID failed because one or more objects access this column.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Viewing 2 posts - 1 through 1 (of 1 total)

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