Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

sql server alter table command to add primary key

Posted on 2016-08-22
2
Medium Priority
?
139 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 2000 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

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

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

     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
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…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…

704 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