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

x
?
Solved

Need SQL to add a Key to an Existing Table

Posted on 2006-06-14
5
Medium Priority
?
316 Views
Last Modified: 2011-10-03
Can someone please show the SQL to add an key an existing table.  I need to add a primary key to a table but to not have access to enterprise manager only query analyzer.  
0
Comment
Question by:Hojoformo
[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
5 Comments
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 400 total points
ID: 16908238
ALTER TABLE YourTableName ADD CONSTRAINT PK_YourTableName PRIMARY KEY CLUSTERED  (YourColumnName)
0
 
LVL 13

Accepted Solution

by:
MikeWalsh earned 1000 total points
ID: 16908240
There is no primary key in this table already? Do you plan on adding data for the primary key column for the existing records? Adding a primary key after the fact with data in a table can cause some issues which need working around.. but the syntax for adding a key is, or is this is an existing column?:


-- To add a column
ALTER TABLE tableName
ADD columnName dataType

-- To add a primary key
ALTER TABLE tableName
ADD Constraint
[PK_KeyName] PRIMARY KEY  CLUSTERED
      ([ColumnName])

This is the basic syntax. There are other options you can add. you may already have a clustered index, you can make this a nonclustered index. it has to be an index of one of those sorts.

You have query analyzer so I would look up ALTER TABLE in the index of books online (SQL Server Help) to see the full syntax.. Type ALTER TABLE, highlight it and press Shift + F1 and you will see more on this.

Also search for Primary Key and select creating.
0
 
LVL 13

Assisted Solution

by:MikeWalsh
MikeWalsh earned 1000 total points
ID: 16908245
you don't need to add the column if it already exists. If it doesn't exist you will need to add the column, fill it with values and then assign the primary key constraint. Hopefully you already have the column and are just asking to identify an existing column(or columns) as a primary key.
0
 
LVL 25

Assisted Solution

by:jrb1
jrb1 earned 200 total points
ID: 16908255
First make sure the column you want to be the primary key is defined as NOT NULL.  Then:

ALTER TABLE Customer ADD PRIMARY KEY (SID);
0
 
LVL 23

Assisted Solution

by:Racim BOUDJAKDJI
Racim BOUDJAKDJI earned 400 total points
ID: 16910709
Just a little additional comment concerning UNIQUENESS on a relational standpoint which states that all rows must be unique.

Adding a numerical key defining a counter only is equivalent to implement a surrogate arbitrary key (It is not sufficient to define UNIQUENESS on a relational standpoint)...You need to adress the issue of candidate keys defining uniqueness on the row then create an additional constraint of uniqueness based on one candidate key or concatenation of candidate keys.  If you don't adresse such issue there is little usefulness into defining a primary key whose primary role is to identify uniquely the row and avoid redundance.

For instance if Table1 is made of Field1, Field2, Field3, Field4 (all being int for instance) and the combination of Field1 and Field4 is what makes the row unique then you can create an additional column using both candidate keys to make a concatenated primary key...


alter table tableName add primary_key as (Field1 + Field4) int
alter table tableName add constraint [PK_KeyName] primary key clustered  ([primary_key])

or you can create a surrogate key as stated above but you will still need to adresse the issue of uniqueness...

Of course, if the script above refuses to run ...that means you will have to deal with dupplicate records first...

Hope this helps...
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

722 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