Solved

sql server alter table command to add primary key

Posted on 2016-08-22
2
93 Views
Last Modified: 2016-08-22
I'm using sql server 2008.

I read this article:
http://stackoverflow.com/questions/2626158/why-use-multiple-columns-as-primary-keys-composite-primary-key

I have this create table script
CREATE TABLE Persons
(
    P_Id int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Address varchar(255),
    City varchar(255),
    CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName)
)

Open in new window


When I run this script that creates a table with two primary key columns that looks like this:

table
I read that on a sql server table on some other article, that you can only have one primary key, but you can have multiple columns in your primary key.

If I want to have the City column also be a primary key column.

Any know the syntax for the Alter Table command so that after I create the table with the script above I can then use the Alter Table command to then add the City column as one of the primary key columns?
0
Comment
Question by:maqskywalker
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 65

Accepted Solution

by:
Jim Horn earned 500 total points
ID: 41765572
>If I want to have the City column also be a primary key column.
Drop the PK, change City from NULL to NOT NULL, then recreate the PK

ALTER TABLE Persons
DROP CONSTRAINT pk_PersonID
GO

ALTER TABLE Persons
ALTER COLUMN City varchar(255) NOT NULL
GO

ALTER TABLE Persons
ADD CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName, City) 
GO

Open in new window

0
 
LVL 1

Author Closing Comment

by:maqskywalker
ID: 41765587
Thanks.
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
query optimization 6 31
Display SQL 2008 last modified/update Database 11 78
How come this XML node is not read? 3 53
SQL 2008 - Disappearing Temp Table in Stored Procedure 24 63
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question