Solved

Primary key in MS SQL database

Posted on 2014-09-29
12
168 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 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
Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
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 48

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 33

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 48

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 33

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
I have a large data set and a SSIS package. How can I load this file in multi threading?
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

828 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