Primary Keys - are they a must?

[33216CLC]

Hi,

I program in VB6 and I mostly use Access databases. I would like to know what effect removing the primary key from a table would have on that table. I know that they are needed for linking tables, but none of the tables in my database are linked. Could my doing thig pose a problem?

Thanks,

Cooleen

 

[culshaja]

The purpose of a primary key is to identify and enforce the unique method of identifying that record. Of you do not need that functionalty then it is not fatal to remove it. Howerver, do remember that the primary key is also an index which speeds up searching on that data, so if you do remove it you might want to create a non-unique index on that column(s) - a unique index will just enforce the same as a primary key!!

James James Culshaw
jculshaw@active-data-solutions.co.uk

http://www.active-data-solutions.co.uk

http://www.brainbench.com/transcript.jsp?pid=193305

 

[RonB]

I agree with Culshaw. I use several tables which I download from our mainframe. I do not set a primary key (reason is too lengthy of an explanation) on my tables and everything I do with them works fine. Like Mr. Culshaw says, a primary key speeds up searchs and makes each record unique. If at all possible always use a primary key.

 

[33216CLC]

James, Ron, Thanks.

 

[Craig0201]

Always worth remembering that with a primary key does not have to be a unique field but can be a combination of fields that themselves are not unique. An example of this is an appointment book. A meeting room can be booked more than once i.e. at different times and more than one meeting room can exists. Therefore neither meeting time nor meeting place are unique but the combination of them is.......

Craig

 

[33216CLC]

Thanks Craig. In the table under discussion, I have two fields that make up the primary Key, Employee Id and a Period End Date (for Payroll package). My situation is that at any given time (pre or post payroll processing/issuing of cheques) an employee is paid a separate amount for whatever reason. Since the aforementioned fields are the primary key, another record cannot be added for the same employee and period. I considered adding another field to the primary key, but I thought, "what if a similar situation arises in the future?". I felt removing the primary key would be best.

I made the two fields index fields alllowing duplicate values.

Thanks,

Cooleen