SQL Server Performance

Identity constraint

Discussion in 'SQL Server 2005 General Developer Questions' started by ashish.johri2007, Mar 6, 2007.

  1. ashish.johri2007 New Member

    Can I remove Identity constraint from one column in my table? Pls suggest me code.

    Regards,
    Ashish

    Ashish Johri
  2. ashish.johri2007 New Member

    SET IDENTITY_INSERT sales_item OFF

    can't be the answer of my question. I want to remove this constraint permanently.



    Ashish Johri
  3. ashish.johri2007 New Member

    alter table sales_item drop IDENTITY(1,1,1) is not working.



    Ashish Johri
  4. Adriaan New Member

    Identity is not a constraint, it's a property. There doesn't appear to be any simple syntax to remove the property.

    Add another INT column to the table, add a unique constraint if necessary.
    Copy the identity value into the new column.
    Drop the identity column (if you have FKs referring to this column, drop those FKs first).
    Rename the new column (and recreate any FKs you dropped in the previous step).
  5. ashish.johri2007 New Member

    Thanks Adriaan,

    Actually I copied data from one DB to another in the same table.
    Eg. From DB1.dbo.Sales to DB2.dbo.Sales.
    I used Micorsoft#%92s export data utility. I did the same you told and I could successfully transfer the data.
    Thanks for that.

    But this time one problem cropped up. Now I have table and column sales_id populated in this. I want to make this an identity column.
    I created one new column temp and made this IDENTITY.

    if not exists(
    select 1 from sysobjects o, syscolumns c
    where o.id = c.id
    and o.name = 'Sales'
    and c.name = 'temp')

    alter table Sales add temp int IDENTITY(1,1)

    Now I copied all the values from sales_id to that temp.

    Update Sales

    set temp = Sales_Id

    And I delete original sales_ID column.
    This is successful.

    Now when I rename this column---->as follows

    sp_rename 'tablename.columnname', 'newcolumnName','column'
    I get an error.

    "Msg 15248, Level 11, State 1, Procedure sp_rename, Line 213

    Either the parameter @objname is ambiguous or the claimed @objtype (column) is wrong."

    I could not make out what this error is. Please any pointer?


    Ashish Johri
  6. Adriaan New Member

    The easy way out is of course to do the renaming in Enterprise Manager, so you don't have to worry about the exact syntax.
  7. MohammedU New Member

    You are getting the error while checking column existence in the code...
    -- check for wrong param
    if ((@colid is not null AND @objtype <> 'column') OR
    (@colid is null AND @objtype = 'column'))
    begin
    COMMIT TRANSACTION
    raiserror(15248,-1,-1,@objtypeIN)
    return 1
    end

    Try qualifying schema name as an example in BOL...

    exec sp_rename 'schemaname.tablename.columnname', 'newcolumnName','column'



    MohammedU.
    Moderator
    SQL-Server-Performance.com
  8. ashish.johri2007 New Member

    Thanks Mohammed and Adriaan for support.

    Regards,
    Ashish

    Ashish Johri
  9. MohammedU New Member

    How did you rename your column?
    Using...Alter table command or SSMS?

    MohammedU.
    Moderator
    SQL-Server-Performance.com
  10. ashish.johri2007 New Member

    Sir I renamed column by using the code as below:

    sp_rename 'schemaname.tablename.columnname', 'newcolumnName','column'






    Ashish Johri

Share This Page