I have an existing table referred to as Persion. in this table I actually have five columns:
persionId
Pname
PMid
Pdescription
Pamt

When I created this table, I set PersionId and Pname because the primary key. I now wish to include an extra column within the primary key - PMID. however can i write an ALTER statement to try and do this? (I already have 1000 records in the table). First, you can drop constraint and recreate it with the following code:
alter table Persion drop CONSTRAINT <constraint_name>
alter table Persion add primary key (persionId,Pname,PMID)


You can find the constraint name by using the code below:
select OBJECT_NAME(OBJECT_ID) AS NameofConstraint
FROM sys.objects
where OBJECT_NAME(parent_object_id)='Persion'
and type_desc LIKE '%CONSTRAINT'


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

HostForLIFE.eu SQL Server 2014 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.