Primary Key VS No Primary Key

[LucL]

Hi Guys,

I'm trying to figure out whether I should have a primary key or not.

Here is my situation..

I have a primary table, say: ptable that stores id (autoincrement primary key),name (varchar)

Then I'll have lots of child tables that store various properties but those properties are linked directly (via foreign key) to the ptable.id. There will never be any duplicates in the child tables (no single table will ever have 2 entries for the same ptable.id).

So, is it necessary to have primary keys in the children
even though they will never actually be used for anything?

I know it's good practice to always have them, just not sure if there is an imortant thing i'm missing here.

Speed and size are important to me because there will be a lot of records.

Thanks!

 

[r937]

how do you control "there will never be any duplicates in the child tables" if not with a primary key?

r937.com | rudy.ca

 

[shadedecho]

hopefully you are at least index'ing the foreign key field in each of those child records. If not, then the easy answer is that any SELECT/JOIN to get those matching records (regardless of your enforced uniqueness) from the child table(s) will not be efficient, but will at worst have to do a full table scan to match with the ON/WHERE clause.

if you are indexing the field, then technically, what you are describing is that the foreign key field in the child table is actually serving the dual purpose of being the primary key for that table as well, in that it is likely the primary field used to select child table records via joins/wheres, as well as the primary way you probably enforce non-duplication in the child tables on insert's, etc.

the issue here then is that while performance-wise this will be just fine (unless you are doing more complicated query'ing that what we've assumed here), you are doing something that is generally not considered good from a db-design perspective. ideally, primary keys should not be used for any other purpose (ie, as a business logic data member or, as in your case, as a foreign key). It makes your db schema more difficult to interpret (less intuitive), and more rigid (less flexible/extensible). While it may not be a concern now, a responsible DB design accounts for things like this for future design needs or possible extensions to functionality.

Trust me, if you get 10 million child records in there and then a year from now realize that this non-standard, non-recommended design in deed actually needs to be adjusted, you'll wish you had just kept the primary keys as only that from the start. It's a "best practice", and it's that way for a reason... because you are definitely not the first person to attempt this, and all sides have been seen before, and overall this is what most people accept as best.