locked
SQL 2008 R2 - table design - add identity to table that has 85 records and dependency RRS feed

  • Question

  • Hi

    I have a table in a database that has 85 records and has no identity.  It has tables that is dependency.  I need to add identity to it and start seed at 85 and increment =1.  I need to know how to do this.  Any suggestion?  I cannot drop the table cause it has a butch of files that is depend on this table.

    thx, Ted.

    Tuesday, September 28, 2010 5:12 PM

Answers

  • If you need to alter existing column, then you don't have a choice.  As you can't change column property to add identity to it after the fact.  But you can add a new column with identity, without issues.

    • Marked as answer by eseeweb Tuesday, September 28, 2010 6:12 PM
    Tuesday, September 28, 2010 5:26 PM
  • Check this out. It is from 

    http://msdn.microsoft.com/en-us/library/ms190273.aspx

     

     

    G. Adding several columns with constraints
    The following example adds several columns with constraints defined with the new column. The first new column has an IDENTITY property. Each row in the table has new incremental values in the identity column.
    
    SQL Copy Code 
    CREATE TABLE dbo.doc_exe ( column_a INT CONSTRAINT column_a_un UNIQUE) ;
    GO
    ALTER TABLE dbo.doc_exe ADD 
    
    -- Add a PRIMARY KEY identity column.
    column_b INT IDENTITY
    CONSTRAINT column_b_pk PRIMARY KEY, 
    
    -- Add a column that references another column in the same table.
    column_c INT NULL 
    CONSTRAINT column_c_fk 
    REFERENCES doc_exe(column_a),
    
    -- Add a column with a constraint to enforce that 
    -- nonnull data is in a valid telephone number format.
    column_d VARCHAR(16) NULL 
    CONSTRAINT column_d_chk
    CHECK 
    (column_d LIKE '[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]' OR
    column_d LIKE
    '([0-9][0-9][0-9]) [0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]'),
    
    -- Add a nonnull column with a default.
    column_e DECIMAL(3,3)
    CONSTRAINT column_e_default
    DEFAULT .081 ;
    GO
    EXEC sp_help doc_exe ;
    GO
    DROP TABLE dbo.doc_exe ;
    GO
    
    

     


    Best Wishes, Arbi --- Please vote if you find this posting was helpful or Mark it as answered.
    • Marked as answer by eseeweb Tuesday, September 28, 2010 6:12 PM
    Tuesday, September 28, 2010 5:55 PM

All replies

  • If you need to alter existing column, then you don't have a choice.  As you can't change column property to add identity to it after the fact.  But you can add a new column with identity, without issues.

    • Marked as answer by eseeweb Tuesday, September 28, 2010 6:12 PM
    Tuesday, September 28, 2010 5:26 PM
  • Check this out. It is from 

    http://msdn.microsoft.com/en-us/library/ms190273.aspx

     

     

    G. Adding several columns with constraints
    The following example adds several columns with constraints defined with the new column. The first new column has an IDENTITY property. Each row in the table has new incremental values in the identity column.
    
    SQL Copy Code 
    CREATE TABLE dbo.doc_exe ( column_a INT CONSTRAINT column_a_un UNIQUE) ;
    GO
    ALTER TABLE dbo.doc_exe ADD 
    
    -- Add a PRIMARY KEY identity column.
    column_b INT IDENTITY
    CONSTRAINT column_b_pk PRIMARY KEY, 
    
    -- Add a column that references another column in the same table.
    column_c INT NULL 
    CONSTRAINT column_c_fk 
    REFERENCES doc_exe(column_a),
    
    -- Add a column with a constraint to enforce that 
    -- nonnull data is in a valid telephone number format.
    column_d VARCHAR(16) NULL 
    CONSTRAINT column_d_chk
    CHECK 
    (column_d LIKE '[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]' OR
    column_d LIKE
    '([0-9][0-9][0-9]) [0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]'),
    
    -- Add a nonnull column with a default.
    column_e DECIMAL(3,3)
    CONSTRAINT column_e_default
    DEFAULT .081 ;
    GO
    EXEC sp_help doc_exe ;
    GO
    DROP TABLE dbo.doc_exe ;
    GO
    
    

     


    Best Wishes, Arbi --- Please vote if you find this posting was helpful or Mark it as answered.
    • Marked as answer by eseeweb Tuesday, September 28, 2010 6:12 PM
    Tuesday, September 28, 2010 5:55 PM
  • thanks for the update.  Its a column that we need to change the property to make it an identity column.  This is a database that was export from sql 2005 to sql 2008r2 but the identity property as not export at the same time.  We are trying to figure out what is the best way to handle this with minimal effort, i guess.

    anyway, if we can't change the column property after the fact, we will backup from sql 2005 and restore to sql 2008 r2 again. 

    Arbi and Mohit, thx for your help.

    Ted.

    Tuesday, September 28, 2010 6:10 PM
  • Why not just backup and restore the database into SQL 2008 R2?
    Tuesday, September 28, 2010 6:30 PM
  • yes,Mohit, we have just backup a copy of database from 2005 and restore to 2008 r2 and everying look ok including the identity is there too.  What we need to do now is to temporarily suspend the constraint and replace one table with another one.  Not sure the t-sql need to use to suspend the constraint in a database. anyone have suggestion?
    Tuesday, September 28, 2010 6:44 PM
    1. Script out all FK Constraints for Source Table from Referencing Tables.
    2. Script out all Triggers from Source Table.
    3. Create new Table as you need it with different name.
    4. Insert all rows from Source Table to NewTable.
    5. Drop Source Table.
    6. Rename NewTable to Source Table.
    7. Run Trigger Script.
    8. Run FK Constraints Script.
    Tuesday, September 28, 2010 7:04 PM