Tuesday, March 27, 2012

Change Identify/Primary Key Column Data Type?

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 ...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.

No comments:

Post a Comment