Add primary key to existing table

Question

I have an existing table called Persion  In this table I have 5 columns     persionId Pname PMid Pdescription Pamt   When I created this table  I set PersionId and Pname as the primary key   I now want to include one more column in the primary key - PMID  How can I write an ALTER statement to do this   I already have 1000 records in the table

User · Answer

drop constraint and recreate it  alter table Persion drop CONSTRAINT  lt constraint name gt   alter table Persion add primary key  persionId Pname PMID    edit   you can find the constraint name by using the query below   select OBJECT NAME OBJECT ID  AS NameofConstraint FROM sys objects where OBJECT NAME parent object id   Persion  and type desc LIKE   CONSTRAINT

User · Answer

Necromancing  Just in case anybody has 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  it is very possible it does not have the name you think it has             -- 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                         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

User · Answer

The PRIMARY KEY constraint uniquely identifies each record in a database table  Primary keys must contain UNIQUE values and column cannot contain NULL Values     -- DROP current primary key    ALTER TABLE tblPersons DROP CONSTRAINT  lt constraint name gt    Example    ALTER TABLE tblPersons    DROP CONSTRAINT P Id      -- ALTER TABLE tblpersion   ALTER TABLE tblpersion add primary key  P Id LastName

User · Answer

I think something like this should work  -- drop current primary key constraint ALTER TABLE dbo persion  DROP CONSTRAINT PK persionId  GO  -- add new auto incremented field ALTER TABLE dbo persion  ADD pmid BIGINT IDENTITY  GO  -- create new primary key constraint ALTER TABLE dbo persion  ADD CONSTRAINT PK persionId PRIMARY KEY NONCLUSTERED  pmid  persionId   GO

User · Answer

-- create new primary key constraint ALTER TABLE dbo persion  ADD CONSTRAINT PK persionId PRIMARY KEY NONCLUSTERED  pmid  persionId     is a better solution because you have control over the naming of the primary key     It s better than just using  ALTER TABLE Persion ADD PRIMARY KEY persionId Pname PMID    which yeilds randomized names and can cause problems when scripting out or comparing databases

User · Answer

Try using this code       ALTER TABLE  table name       CHANGE COLUMN  column name   column name  datatype NOT NULL       ADD PRIMARY KEY   column name

User · Answer

There is already an primary key in your table  You can t just add primary key otherwise will cause error  Because there is one primary key for sql table   First  you have to drop your old primary key   MySQL   ALTER TABLE Persion DROP PRIMARY KEY    SQL Server   Oracle   MS Access   ALTER TABLE Persion DROP CONSTRAINT  constraint name        You have to find the constraint name in your table  If you had   given constraint name when you created table you can easily use   the constraint name ex PK Persion     Second Add primary key   MySQL   SQL Server   Oracle   MS Access   ALTER TABLE Persion ADD PRIMARY KEY  PersionId Pname PMID     or the better one below  ALTER TABLE Persion ADD CONSTRAINT PK Persion PRIMARY KEY  PersionId Pname PMID     This can set constraint name by developer  It s more easily to maintain the table   I got a little confuse when i have looked all answers  So I research some document to find every detail  Hope this answer can help other SQL beginner   Reference https   www w3schools com sql sql primarykey asp

User · Answer

If you add primary key constraint   ALTER TABLE  lt TABLE NAME gt  ADD CONSTRAINT  lt CONSTRAINT NAME gt  PRIMARY KEY  lt COLUMNNAME gt      for example   ALTER TABLE DEPT ADD CONSTRAINT PK DEPT PRIMARY KEY  DEPTNO

User · Answer

Please try this-  ALTER TABLE TABLE NAME DROP INDEX  PRIMARY   ADD PRIMARY KEY  COLUMN1  COLUMN2

User · Answer

ALTER TABLE TABLE NAME ADD PRIMARY KEY  persionId   Pname   PMID

[sql] Add primary key to existing table

Examples related to sql

Examples related to sql-server

Examples related to sql-server-2008

Examples related to constraints

Examples related to primary-key