Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

sql server alter table command to add primary key

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

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

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

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
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 a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
Screencast - Getting to Know the Pipeline

824 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