Adding a primary key to a large table on a shared database server

Question

I have a large database of zip / postal codes used in proximity searches. There are about 1 million records. Somehow I didn't add the primary key to the table, and now I have to add it because searches take about 3 seconds because of the full table scan. When I go to generate a script to add it in SSMS, the operation times out because it is creating a new table and important the data. Any ideas?

Answer 1

Generate the script and paste it into a query and run it there. Make sure your SSMS is not set to have an execution time-out in the options.

Answer 2

ALTER TABLE ADD PRIMARY KEY (,etc...)

Should work in MySQL.

Answer 3

What database are you using? Oracle? Postgresql? SqlServer? MySQL?

Answer 4

Make sure that the execution timeout in your SSMS Options/Query Execution is set to 0 (no timeout). Then use a query similar to the following with table/column/key names replaced with yours. Adjust the parameters as desired.

ALTER TABLE tableName WITH NOCHECK 
ADD CONSTRAINT PK_tableName PRIMARY KEY CLUSTERED (columnName)
WITH (FILLFACTOR = 75, ONLINE = ON, PAD_INDEX = ON)

Reference

Answer 5

why not just create a unique index?