Using Autonumber fields as primary keys

Results 1 to 3 of 3

Thread: Using Autonumber fields as primary keys

  1. #1
    Join Date
    Dec 1969

    Default Using Autonumber fields as primary keys

    As a database consultant I have noticed that there seem to be two schools of thought about determining the primary key of a table. <BR><BR>The "Autonumber" camp, so to speak, assumes that every table should implement an autonumber field as primary key and that this key should be used as a foreign key in other tables. The advantages often stated are:<BR><BR>- Integer joins are faster than character field joins<BR>- Users are insulated from the key making every column mutable<BR>- Where clauses are shorter <BR>- SQL server uses this method in the system tables demonstrating this concepts validity<BR><BR>The "Use Data Columns" camp assumes primary keys should be implemented on tables based on the combined columns which make each row unique.<BR><BR>When I first began designing databases I was lured into the "Autonumber" camp but experience has taught me that this is a hack, bad form and in general wrong, I&#039;ll explain why. Refuting the "advantages" stated above:<BR><BR>- Integer joins might be faster than character joins yet in practice where clauses will contain data column values anyway, forcing the query optimizer to search on the data column. An index would speed this up in which case the join is using the indexed character column which we wanted to avoid in the first place.<BR><BR>- Users are able to modify key columns in a properly structured database using cascading updates/deletes on all the related foreign keys in other tables. As an aside, using an autonumber field PK allows duplicate rows in tables invalidating entity integrity thus forcing implementation of EI through triggers or some other ruse.<BR><BR>- Where clauses may be shorter but it is inherently difficult to decipher the true meaning of the integer id values as their meaning is originally based on the order in which the rows are inserted rather than the intrinsic data.<BR><BR>- Although MS uses this syntax with the system tables, the information schema views do not.<BR><BR>When autonumber fields are used as pks, significant problems arise when attempting to merge databases or import/export. I strongly advise not to use this practice, am I wrong? And if so why?<BR><BR><BR><BR>

  2. #2
    Join Date
    Dec 1969

    Default You analyze too much

    Primary Keys in one table do not necessarily have to be the foreign key or the only foreign key.<BR><BR>Primary Keys should simply be a way of making the row unique. <BR><BR>If the design warrants its use a foreign key, so be it. Otherwise, you are free to use another column in the table to avoid pitfalls specific to your own implementation.<BR>

  3. #3
    Join Date
    Dec 1969

    Default On top of that...

    Just because you are using autonumber for PK doesn&#039;t mean you can&#039;t have *other* contraints that prevent duplicated data.<BR><BR>You can certainly create an index with no-duplicates-allowed that is built from any combo of fields you want. Granted, if you do this, then this might well used as the PK. But suppose that this PK ends up being a CHAR(300) or some such (quite possible with real data!). Now you have to have that same CHAR(300) as the FK in all the related tables. Sure it works. But...<BR><BR>There&#039;s another nice think about an autonumber PK that is seldom discussed. Paging!<BR><BR>One way to page is to do something like:<BR><BR>SELECT TOP 20 * FROM table ORDER BY anyOldField<BR><BR>Now, you get the value of the autonumber field from the last record of that recordset (call it "lastIDof20"). Then you can do:<BR><BR>SELECT TOP 20 * FROM table WHERE autoID &#062; lastIDof20 ORDER BY anyOldField<BR><BR>I suspect this might give you the most efficient possible DB paging! (Granted, you can only go forward or backward one page at a time, rather than jump to a random page...but still...)<BR><BR>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts