0

I want to switch the primary key away from my existing identity column to a different column.

This is my table:

CREATE TABLE dbo.ParkingLot
(
  ID      int IDENTITY(1,1) PRIMARY KEY,
  Address ???,
  Status  ???, 
  newID   ???
);

I want to remove the primary key on the ID column and instead have newID be the primary key (this is a new column but it is already populated with values).

Hadi
  • 36,233
  • 13
  • 65
  • 124
InbalK
  • 31
  • 5
  • 2
    No, you probably shouldn't do that. Just declare `address` to be unique. – Gordon Linoff Jan 01 '18 at 17:59
  • [IDENTITY](https://learn.microsoft.com/en-us/sql/t-sql/statements/create-table-transact-sql-identity-property) does not mean what you think it means. You can't make "address" an identity column, since an address is (usually) not a numeric column. Please clarify what exactly you want to change. – Heinzi Jan 01 '18 at 18:05
  • What I need eventually is to change the values of the ID column (That is a primary key). So I've read that I need to create new column ->define her as the the primary key ->delete the old primary key. So I need to know how to define the new column as primary key instead the "old" one – InbalK Jan 01 '18 at 18:08
  • I've created new column with the values that I need and now I want to make her as the primary key – InbalK Jan 01 '18 at 18:10

1 Answers1

2

Drop primary Key constraint and Re-Add a new one

You have to drop the primary key constraint and add a new one

Drop primary key Constraint

-- Return the name of primary key.  
SELECT name  
FROM sys.key_constraints  
WHERE type = 'PK' AND OBJECT_NAME(parent_object_id) = 
N'Tablename';  
GO  
-- Delete the primary key constraint.  
ALTER TABLE Production.Tablename
DROP CONSTRAINT PK_Tablename;  
GO

Add new primary key Constraint

ALTER TABLE Tablename ADD CONSTRAINT pk_NewPrimary PRIMARY KEY (Newid)

References

Hadi
  • 36,233
  • 13
  • 65
  • 124
  • 2
    I don't think the OP wants to remove the identity column... they just want a different column to serve as the primary key. At least the way I read it. So a new table should not be necessary (even if they want to remove the `ID` column, that can also be done without the need for a new table). – Aaron Bertrand Jan 01 '18 at 20:09
  • @AaronBertrand you're right. i remove the first method and updated my answer. thx for the remark – Hadi Jan 01 '18 at 20:11