>>>Hi,
>>>
>>>I need to change the type of a PK column of a table from type NUMERIC (6,0) to INT (When I created this table I never thought that the number of records would grow to be more than 999,999 but now I need more).
>>>
>>>When I change the type in the SSMS it works, but it takes quite a bit of time on a table of about 600,000 rows. I thought that changing the type using the script would be faster. And I tried the following script:
>>>
>>>ALTER TABLE dbo.MyTable
>>>ALTER COLUMN wo_number INT
>>>
>>>
>>>But I get the following errors:
>>>
>>>he object 'PK_MyTable' is dependent on column 'wo_number'.
>>>Msg 5074, Level 16, State 1, Line 1
>>>The index 'open_wo' is dependent on column 'wo_number'.
>>>
>>>That is, the PK index and another index do not allow the script to change the type. Does it mean that the only way to change the type of this PK is via SSMS?
>>
>>Just FYI, rather than having different sizes of primary keys, just use Int for every primary key. Don't forget to start the numbering at -2 billion. The primary key is not supposed to have any meaning to anyone. Candidate keys might have meaning, but are not used for joining. Further, you'd never have run into this problem.
>
>I agree that Int is the best choice for every primary key (I am refactoring this application that started in Clipper that didn't have Int type). But I am not 100% in agreement that the PK could not have a meaning. Thank you.
Just as having int be the PK type everywhere as a standard is a good thing, so is having meaningless keys as a standard. The point to them is to prevent any kind of cascade updates. You have an invoice number on the invoice header. You have a meaningless key on the invoice header. You use that key to join to line items. The invoice number can be changed all you want with no effect on the line items.