Primary key in MS SQL database

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?
Rupert EghardtProgrammerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
Rupert EghardtProgrammerAuthor Commented:
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
Guy Hengel [angelIII / a3]Billing EngineerCommented:
yes, exactly, and also put "id_field" name as you want/need.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
Rupert EghardtProgrammerAuthor Commented:
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
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PortletPaulfreelancerCommented:
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
Rupert EghardtProgrammerAuthor Commented:
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
ste5anSenior DeveloperCommented:
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
PortletPaulfreelancerCommented:
"So you should identify those candidate keys."
Absolutely! well said ste5an

e.g. this index:

2.  (Unique, Non-clustered)  ... linked to two columns
0
Scott PletcherSenior DBACommented:
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
ste5anSenior DeveloperCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.