Solved

Primary key in MS SQL database

Posted on 2014-09-29
12
184 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
[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
  • 4
  • 3
  • 2
  • +2
12 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 40349553
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
ID: 40349559
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 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 40349570
yes, exactly, and also put "id_field" name as you want/need.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 40349573
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
ID: 40349582
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 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 250 total points
ID: 40349590
>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
 
LVL 49

Assisted Solution

by:PortletPaul
PortletPaul earned 250 total points
ID: 40349599
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
ID: 40349616
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 34

Expert Comment

by:ste5an
ID: 40349653
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 49

Expert Comment

by:PortletPaul
ID: 40349665
"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:Scott Pletcher
ID: 40350414
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 34

Expert Comment

by:ste5an
ID: 40350658
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

Veeam gives away 10 full conference passes

Veeam is a VMworld 2017 US & Europe Platinum Sponsor. Enter the raffle to get the full conference pass. Pass includes the admission to all general and breakout sessions, VMware Hands-On Labs, Solutions Exchange, exclusive giveaways and the great VMworld Customer Appreciation Part

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

630 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