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
Solved

sql server alter table command to add primary key

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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

856 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