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

x
?
Solved

Primary key in MS SQL database

Posted on 2014-09-29
12
Medium Priority
?
214 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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
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 750 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 750 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 36

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 70

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 36

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

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

972 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