Solved

Primary key in MS SQL database

Posted on 2014-09-29
12
157 Views
Last Modified: 2014-10-13
Hi Guys,

I have a MS SQL database where the initial developer did not include Primary keys for any of the tables.

I have to retrieve/report information from database and require Primary keys for the tables to do so.

My questions;

1.  Can I add a primary key to each table, without breaking the original applications currently connecting to these tables?

2.  Is it possible to add a primary key for each table that can auto-number records retrospectively? and auto-number new records going forward?
0
Comment
Question by:Rupert Eghardt
  • 4
  • 3
  • 2
  • +2
12 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
Q1.  Can I add a primary key to each table, without breaking the original applications currently connecting to these tables?
short answer is : yes
of course, sql server will deny the creation of the primary key constraint if there are duplicate values already, in which case you will need to fix that first.


Q2. Is it possible to add a primary key for each table that can auto-number records retrospectively? and auto-number new records going forward?

yes:
alter table yourtable add id_field int identity

and even adding the primary key at the same time:
alter table yourtable add id_field int identity primary key
0
 

Author Comment

by:Rupert Eghardt
Comment Utility
Thank you Guy!

There is currently no unique column values in the table.
I have to setup a new column with unique (auto-number) values, and assign a primary key to this column.

Will the script commands you suggested work for the above?
(Do I just replace the "mytable" section) ?

alter table "mytable" add id_field int identity
alter table "mytable" add id_field int identity primary key
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
yes, exactly, and also put "id_field" name as you want/need.
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
note, you will only need the lastest one to add both the field with identity and set is as primary key:
alter table "mytable" add "id_field" int identity primary key


I just have to note 2 things:
* the application may fail if it does not insert properly into the table... as the number of columns changes...
* do this on a backup of the database...
0
 

Author Comment

by:Rupert Eghardt
Comment Utility
The database is over 1Tb in size, very difficult to backup and test on a separate instance, as the server HDD is limited.

1.  If creating the primary column/key causes a problem, can I simply go and delete the column and key, and all will be back to square one?

2.  Will the command (alter table "mytable" add "id_field" int identity primary key) also number all records retrospectively?
0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 250 total points
Comment Utility
>The database is over 1Tb in size, very difficult to backup and test on a separate instance, as the server HDD is limited.
if you want to take the responsibiliy of eventually breaking things...

1.  If creating the primary column/key causes a problem, can I simply go and delete the column and key, and all will be back to square one?
=> YES

2.  Will the command (alter table "mytable" add "id_field" int identity primary key) also number all records retrospectively? => YES
though the numbering may not be in the order as the records had been entered;
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 48

Assisted Solution

by:PortletPaul
PortletPaul earned 250 total points
Comment Utility
but a "key" does not have to be an auto incrementing integer (that is a common convention, not an absolute rule)

is it possible the logical keys are composite keys?  or that the developer was following a convention you are not familiar with?

how are the clustered indexes on these tables defined? do these reveal the logical keys of each table?
(clustered indexes do not have to be created on auto-incrementing integers either)

I only raise these questions so that you pause before embarking an what might be a substantial amount of risk and effort.
0
 

Author Comment

by:Rupert Eghardt
Comment Utility
For the current table I'm looking at, there is no keys defined.

Constrains are also empty, and so are triggers

There are 3 x indexes:
1.  (Non-unique, Non-clustered)
2.  (Unique, Non-clustered)  ... linked to two columns
3.  (Non-unique, Non-clustered)
0
 
LVL 32

Expert Comment

by:Stefan Hoffmann
Comment Utility
Can you post some sample DDL scripts?

1. Under most circumstances: Yes, when wisely choosen. Have you already examined the data in your tables? You need to look for duplicates.

2. Yes. But auto-numbers are here imho not necessary. Cause these tables must alread have candidate keys. Otherwise the application could not work. So you should identify those candidate keys.
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
"So you should identify those candidate keys."
Absolutely! well said ste5an

e.g. this index:

2.  (Unique, Non-clustered)  ... linked to two columns
0
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
I guess you are reporting from Access or something similar where it absolutely requires a PK.  Very stupid requirement, but unfortunately no way around it AFAIK (at least when I did a lot of Access, it was required to be specifically a PK).


Be aware that it will take a *LONG* time to add a column to a 1TB db.  Converting the existing unique index to a pk is probably best for overall performance, but that will take a *LONG* time too, since the clus index must be dropped and then recreated as a PK, both of which will take a long time.
0
 
LVL 32

Expert Comment

by:Stefan Hoffmann
Comment Utility
Access does not require a primary key per se. It needs it only one for DML operations. And the more important: This key must not exist on the server side. ACE only needs one candidate key on the Access side. So link your tables and create a primary key on this table. Then Access can DML also in tables without primary key or views.
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Suggested Solutions

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

743 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now