You can have the need to change the type of the key column of a SQL Server table, due to a previous bad design, or simply to a change of requirements.
If you have to change the type of a key column and the new column type has to be integer, and even identity, you can do it by using some criteria in order to get the rows ordered (PK= index clustered= order by in physical disk by this column).
We will change the type of the PK column of a table in a sample with 2 typical invoice tables master-detail where the key columns are nchar. 'invheader.invoiceid' is the primary key of the master table and 'invlines.lineid' is the primary key of the detail table.
[[ad]]
Steps to change the type of primary keys in SQL Server
Add a new column to the master table to create new id's
We start choosing the column type and creating it in the master table.
Next, un UPDATE sentence will inform the values of the new column with an incremental id calculated with a join with a select over the same table that uses the function ROW_NUMBER() to create an incremental line counter.
USE DBTEST;
ALTER TABLE INVHEADER ADD PK_INVHEADER int NULL;
UPDATE INVHEADER SET PK_INVHEADER = A.ROWNUMBER
FROM INVHEADER
INNER JOIN (SELECT ROW_NUMBER() OVER (ORDER BY DATE ASC) AS ROWNUMBER
FROM INVHEADER ) A ON INVHEADER.invoiceid = A.invoiceid;
Add a new column to the detail table to create new id's
With the table of invoice lines, INVLINES, we will add also a new column for the new row identifiers. The SQL sentences will use the same method with ROW_NUMBER(), but also joining the master table to inform the new foreig key created in previous step.
USE DBTEST;
ALTER TABLE INVLINES ADD PK_LINE int NULL;
ALTER TABLE INVLINES ADD FK_HEADER int NULL;
UPDATE INVLINES SET PK_LINE = A.ROWNUMBER,
FK_HEADER = PK_HEADER
FROM INVLINES INNER JOIN
(SELECT ROW_NUMBER() OVER (ORDER BY DATE ASC) AS ROWNUMBER, lineid, invoiceid
FROM INVLINES INNER JOIN INVHEADER ON INVHEADER.invoiceid=INVLINES.invoiceid) A
ON INVLINES.lineid = A.lineid;Alter tables to use new primary keys
The new PK's and FK are yet informed with int id's. Finally, we have to define the fields as NOT NULL and Primary Key, or even as Identity, and alter the foreign key in detail table, or add a new one.
An easy way to make this changes in tables design is using the visual environtment of SQL Server Management Studio, selecting Database diagram in the tree of the Objects explorer.
If we want that new keys are also of type identity remember that we have first to select the max value of this field on both tables to asign to identity the starting values. With SQL Server 2012 and above we can use Sequences to better control the values of identity columns.
As an extra information, useful if you plan to migrate between Oracle, MySQL or SQL Server databases, Oracle uses also sequencies but don't have a similar feature to 'Identity' in fields properties, and MySQL have the same feature od 'Identity' with the equivalent 'AUTO_INCREMENT' clause.
[[ad]]
