I am using SQL SERVER 2008 database. I have a table Customers in which the CustomerID column is had datatype INT intially but due to increase in Customers the number range is exceeding which may cause error arithmetic overflow of data. Hence I thought to
modify the datatype from INT to BIGINT and wrote an alter script but when I ran the script it failed. Hence please provide me help in creating alter script for the updating the datatype of the primarykey column for the Customers table.
--This will drop the primary key temporarily
ALTER TABLE MyTable
drop CONSTRAINT PK_MyTable
--change data type
ALTER TABLE MyTable
ALTER COLUMN PrimaryKeyID BigInt
--add primary key
ALTER TABLE MyTable
ADD CONSTRAINT PK_MyTable PRIMARY KEY (PrimaryKeyID)
You can use Alter table Alter column to make changes to your columns' data type, but there are some restrictions. In your case, you are running into issue with the Primary key constrain on your primary key column. "The modified column can’t have a PRIMARY
KEY or FOREIGN KEY constraint defined on it." (From SQL Server 2008 Internals, Chaper 5).
You can drop the constrain, change data type and reassign the primary key to the column. Try this to see whether it works for you.
santosh.patr...
Member
15 Points
62 Posts
Alter script to change the Primarykey Column datatype
Feb 22, 2010 10:03 AM|LINK
Hi WebGurus,
I am using SQL SERVER 2008 database. I have a table Customers in which the CustomerID column is had datatype INT intially but due to increase in Customers the number range is exceeding which may cause error arithmetic overflow of data. Hence I thought to modify the datatype from INT to BIGINT and wrote an alter script but when I ran the script it failed. Hence please provide me help in creating alter script for the updating the datatype of the primarykey column for the Customers table.
Thanks,
Santosh
SQL Server 2008
jgangwisch
Contributor
2449 Points
366 Posts
Re: Alter script to change the Primarykey Column datatype
Feb 22, 2010 12:55 PM|LINK
Here is a sql script:
limno
All-Star
117340 Points
8005 Posts
Moderator
MVP
Re: Alter script to change the Primarykey Column datatype
Feb 22, 2010 04:50 PM|LINK
You can use Alter table Alter column to make changes to your columns' data type, but there are some restrictions. In your case, you are running into issue with the Primary key constrain on your primary key column. "The modified column can’t have a PRIMARY KEY or FOREIGN KEY constraint defined on it." (From SQL Server 2008 Internals, Chaper 5).
You can drop the constrain, change data type and reassign the primary key to the column. Try this to see whether it works for you.
Format your SQL query with instant sql formatter:
http://www.dpriver.com/pp/sqlformat.htm