Primary Key

drmopco used Ask the Experts™
Should every table have a primary key?  Why or Why Not.  
Can you explien to me?
Thank you.


Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Yes because you (and your database) must have a way to uniquely identify every record in a table.
Database Consultant
Top Expert 2009
I think ,Not always it is necessary,
If you are sure that you are storing only unique values, or it is a read only table it is not necessary that the table should have aprimary key. The problem with primary keys is that by default it will create a Clustured index, which will make some overhead during the insert operations.
Anyway it is better to create a primary key for almost all the tables
It is your chioce...
If you want to enter unique records in the table then it is necessary....

If you want to use any field value/column as a foreign key in the other table then also the primary key is necessary in the table...
Should you be charging more for IT Services?

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

A primary key has to be unique, if you don't have a single column that is unique you can always make the primary key up more than 1 column.


Thank you for your comments.
So you are saying that the reason why every table should have a primary key is because the primary key makes the column unique. For example if everyone in this class was put into a database, the best primary key would be the student's ID number; because no two students will have the same number.
It really comes down to whether you need to identify specific rows in a table.

If you'd have to have one for every table ever made then you'd forced to choose one at compile time.

If you were only interested in the colour of students eyes then you could just have:

Colour         Quantity            
Green                3    
Hazel                 4
Blue                   5
Green                7

You wouldn't necessarily need a primary key, you can add the data up and see that there are 10 students with green eyes, 5 blue, 4 hazel.

If you wanted a more complicated scenario like attendence records and which classes students are meant to go to you'd have a lot of data over several tables, and to find the relevant information you'd need a unqiue identifier to get the right data. A student id would be better than a name, as a name wouldn't likely be unqiue. A primary key is a constraint, it forces the data to be unique.

A lot of it comes down to normalisation- ie when you seperate data into down into and therefore need to link between the tables.

N.B. A primary key doesn't have to be numeric, just unique i.e social security number / national insurance number or like you said student id

Oh yeah a primary key made up of several columns (or unique keys) is called a composite primary key...

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial