Change Identify/Primary Key Column Data Type? RRS feed

  • Question

  • Hi ...

    I've taken over a project where the tables were created with identity/primary key columns of type DECIMAL(12,0). The latest addition to the project is to replicate data down to Pocket PC applications. Replication requires that identify/primary key columns be of type INT/BIGINT.

    I've attempted to ALTER TABLE xxx ALTER COLUMN yyy BIGINT; and it fails.  Failed due to all the foreign key constraints that have been created.  While it didn't give any error messages associated with the the field being the PRIMARY KEY - I'm assuming I may get that error as well.

    I then did an ALTER TABLE xxx NOCHECK CONSTRAINT ALL; for every table in the system to disable checking of foreign keys and then attempted to alter the column to a bigint and it still failed.

    How can I change the column from Decimal to BIGINT - or do I have to create new tables, import all the data, get rid of the original tables? Please tell me I don't have to do the latter.

    Thanks ...
    Thursday, February 15, 2007 8:31 PM


  • Unfortunately the only way is to recreate the table or drop & recreate the column. This would require removing the existing constraints (PKs, FKs) and recreating them. Alternatively, you can create a view on the table(s) that does the conversion and replicate those. But this may or may not work depending on your replication scheme and you will have to ask in the Replication newsgroup.
    Thursday, February 15, 2007 8:36 PM