Solved

sql server alter table command to add primary key

Posted on 2016-08-22
2
116 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 66

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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
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 this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…

628 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