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    Make sure there are no NULLs in that column for any row Set the column to not allow NULLs Add the column as a primary key 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      lt ---- 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

User · Answer

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'

User · Answer

Necromancing  It looks you have just as good a schema to work with as me    Here is how to do it correctly   In this example  the table name is dbo T SYS Language Forms  and the column name is LANG UID  -- First  chech if the table exists    IF 0  lt        SELECT COUNT    FROM INFORMATION SCHEMA TABLES      WHERE TABLE TYPE    BASE TABLE      AND TABLE SCHEMA    dbo      AND TABLE NAME    T SYS Language Forms    BEGIN     -- Check for NULL values in the primary-key column     IF 0    SELECT COUNT    FROM T SYS Language Forms WHERE LANG UID IS NULL      BEGIN         ALTER TABLE T SYS Language Forms ALTER COLUMN LANG UID uniqueidentifier NOT NULL           -- No  don t drop  FK references might already exist            -- Drop PK if exists          -- ALTER TABLE T SYS Language Forms DROP CONSTRAINT pk constraint name          --DECLARE  pkDropCommand nvarchar 1000           --SET  pkDropCommand   N ALTER TABLE T SYS Language Forms DROP CONSTRAINT     QUOTENAME  SELECT CONSTRAINT NAME FROM INFORMATION SCHEMA TABLE CONSTRAINTS          --WHERE CONSTRAINT TYPE    PRIMARY KEY           --AND TABLE SCHEMA    dbo           --AND TABLE NAME    T SYS Language Forms           ----AND CONSTRAINT NAME    PK T SYS Language Forms           --           ---- PRINT  pkDropCommand          --EXECUTE  pkDropCommand            -- Instead do         -- EXEC sp rename  dbo T SYS Language Forms PK T SYS Language Forms1234565    PK T SYS Language Forms             -- Check if they keys are unique  it is very possible they might not be           IF 1  gt    SELECT TOP 1 COUNT    AS cnt FROM T SYS Language Forms GROUP BY LANG UID ORDER BY cnt DESC          BEGIN              -- If no Primary key for this table             IF 0                                   SELECT COUNT    FROM INFORMATION SCHEMA TABLE CONSTRAINTS                  WHERE CONSTRAINT TYPE    PRIMARY KEY                   AND TABLE SCHEMA    dbo                   AND TABLE NAME    T SYS Language Forms                   -- AND CONSTRAINT NAME    PK T SYS Language Forms                                 ALTER TABLE T SYS Language Forms ADD CONSTRAINT PK T SYS Language Forms PRIMARY KEY CLUSTERED  LANG UID ASC                             -- Adding foreign key             IF 0    SELECT COUNT    FROM INFORMATION SCHEMA REFERENTIAL CONSTRAINTS WHERE CONSTRAINT NAME    FK T ZO SYS Language Forms T SYS Language Forms                    ALTER TABLE T ZO SYS Language Forms WITH NOCHECK ADD CONSTRAINT FK T ZO SYS Language Forms T SYS Language Forms FOREIGN KEY ZOLANG LANG UID  REFERENCES T SYS Language Forms LANG UID            END -- End uniqueness check         ELSE             PRINT  FSCK  this column has duplicate keys  and can thus not be changed to primary key          END -- End NULL check     ELSE         PRINT  FSCK  need to figure out how to update NULL value s       END

[sql] Change primary key column in SQL Server

Examples related to sql

Examples related to sql-server

Examples related to database

Examples related to shared-hosting