1

The answer to a similar question was to just ignore the problem. I simply don't buy that's the best answer.

The relevant tables in my database:

  • venues (venueid, province, city, venue)
  • members (memberid, lastname, firstname, birthday, phone, recruitmentid)
  • events (venueid, type, memberid, vip, eventfee, costid, date)

Venueid is an auto_incremented primary key in venues, and the composite of province, city, and venue must be unique or the insert will fail.

It all works, but it bothers me that venueid increases even when the insert fails, so I end up getting primary key gaps. Is there a better way to enforce uniqueness, or maybe the way I'm trying to relate these tables is not best practice, and they should really be combined?

Community
  • 1
  • 1
skkevinperson
  • 57
  • 1
  • 8

2 Answers2

1

I simply don't buy that's the best answer.

Well, you need to learn more then.

Frankly, don't take an unique identifier as a number at all. It resembles a number only by an accident. Take it as an abstract matter that identifies your records.

Your Common Sense
  • 156,878
  • 40
  • 214
  • 345
1

There is no real way to work around this MYSQL and PostGre have the same behavior, even in transactions the auto_incremented value does not roll back if a transaction fails.

http://dev.mysql.com/doc/refman/5.1/en/innodb-auto-increment-handling.html

The closest proper solution to your problem may be to utilize auto increment locking.

Oluwakayode Dagbo
  • 191
  • 5