Member Avatar for zachattack05

Is it bad practice to create relationships between tables where the PK is an identity AKA auto incremented?

The reason why I ask is, while identity keys won't repeat, they are set based on the order that someone enters data, so the first entry is 1, second is 2 etc...

If the data is exported, or somehow backed up and needs to be restored, is there a chance that those numbers could change?

For some reason I seem to remember someone saying back in my VBA MS Access days that PK shouldn't be auto increment. I can't remember why though?

  • 2 Contributors
  • 4 Replies
  • 128 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by zachattack05

Recommended Answers

All 4 Replies

Member Avatar for Momerath

It's not a bad practice, it's probably the most common one around :)

Yes, if the data is exported then imported *improperly* then you could experience loss of relations.

Using VBA is a bad idea, probably why doing database stuff with it is a bad idea :)

Member Avatar for zachattack05

If programming languages were drugs, VBA was my "gateway drug" :P

Hi, my name is Zach, and I'm a programmer. I wrote my first "Hello World" program when I was 12 and I've been addicted ever since...Sorry, got caught up in the joke there.

Anyway, do you have any words of wisdom for this? I understand that it's okay if things are imported properly, but now my question is, if using a PK that the user can set will prevent the error from ever happening to begin with, why would you bother taking the risk of improper imports?

A user defined PK will prevent breaks...or am I missing something?

Member Avatar for Momerath

Sometimes you have columns that don't lend themselves to be good keys in related tables. Take an address book as an example. You'd have someones first name, last name, phone number, address, etc. All of these are possibly duplicated so don't make good unique keys. So you add a field (ID).

Then the question becomes: Why should I maintain this internal to the DB only field? So you let the DB handle it with an autonumber field. For SqlServer (at least) all the standard export/import methods understand autonumber fields and will deal with it for you. If you write your own import/export routines, then you'll have to deal with this.

Member Avatar for zachattack05

Momerath,

Thanks for clarifying that. I appreciate it :) I'm starting to catch on to this SQL stuff :)

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.