Change primary key column in SQL Server

Question

UPDATE

Here are the constraints as a result of the query

SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME = 'history'

CONSTRAINT_NAME   COLUMN_NAME  ORDINAL_POSITION
PK_history        userKey       1
PK_history        name          2

Here is the result of the query

SELECT * 
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS 
WHERE TABLE_NAME = 'history'

CONSTRAINT_NAME   CONSTRAINT_TYPE  IS_DEFERRABLE  INITIALLY_DEFERRED
PK_history        PRIMARY KEY      NO             NO

END UPDATE

My host provides an interface to my SQL Server DB via ASP.NET Enterprise Manager.

I have 3 columns in my history table:

• userId (key, int, NULL not allowed)
• name (key, string, NULL not allowed)
• id (not key, int, NULL allowed)

I want to make the id column the only key.

To do that, I believe I need to:

1. Make sure there are no NULLs in that column for any row
2. Set the column to not allow NULLs
3. Add the column as a primary key
4. Remove the other 2 columns as keys

However, when I use the UI provided, it never works. Sometimes it'll just look like it tries to do something but it never changes when I refresh the view of the columns. It occasionally creates a temp table that looks like it tried to do some of the operation, but that never gets copied/overwrites the original table that I'm trying to change.

When I try using a query, the changes don't show up either. Here are the queries I think I need:

    SELECT * from history WHERE id is NULL     <---- This shows 0 results

    ALTER TABLE history
    ALTER COLUMN id int NOT NULL

    ALTER TABLE history ADD PRIMARY KEY (id)

    ALTER TABLE history
    DROP CONSTRAINT userId
    DROP CONSTRAINT name
    GO

I've only gotten to the attempt to disallow NULLs and to add the primary key for the id column. It doesn't seem to work. Can someone point me in the right direction? Thanks!

Answer 1

Assuming that your current primary key constraint is called pk_history, you can replace the following lines:

ALTER TABLE history ADD PRIMARY KEY (id)

ALTER TABLE history
DROP CONSTRAINT userId
DROP CONSTRAINT name

with these:

ALTER TABLE history DROP CONSTRAINT pk_history

ALTER TABLE history ADD CONSTRAINT pk_history PRIMARY KEY (id)

If you don't know what the name of the PK is, you can find it with the following query:

SELECT * 
  FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS 
 WHERE TABLE_NAME = 'history'