Results 1 to 3 of 3
  1. #1
    Join Date
    May 2015
    Posts
    1

    Which is better, use Autonumber or create unique primary key?

    hi there,
    quick question on using autonumber. A text message is to be send to many recipients on different dates. Following normalisation I have created a table just for the text message and another one to show who it is scheduled to go to.

    Table: Text Message
    Columns: TxtMsgID: subject size, text

    Table:TextMessageScheduled
    TxtMsgID, UserId, RecipientID, send date

    TxtMsgID, UserId, RecipientID are composite keys. There is a user table and a recipients table too.

    My query is should I use autonumber as a primary key for the TextMessage table. As I have to insert the TxtMsgId in to the TextMessageScheduled table after adding a record to TextMessage, how can I tell what is new auto number is?

    Thanks in advance

  2. #2
    Join Date
    Jun 2015
    Location
    Boston, MA
    Posts
    16
    If you're manually selecting the text message to be sent, you just need to query the Text Message table for the TxtMsgID field and another field by which you can identify the specific message you want.

    - Bruce Hyatt
    Last edited by mrthnmn; 06-02-15 at 17:40.

  3. #3
    Join Date
    Feb 2012
    Posts
    76
    How to get the last autonumber depends on the DBMS you're using. Since you're calling it an autonumber I suspect you may be using MS Access. If so, and you're using ADO to interact with it, you can get the last inserted autonumber with:
    Code:
    SELECT @@IDENTITY
    TxtMsgID, UserId, RecipientID are composite keys. There is a user table and a recipients table too.
    If those three form a composite key for the TextMessageScheduled table, it means each combination of TxtMsgID, UserId, RecipientID can only occur once in the table, i.e. each user can only send each text message to each recipient once. Is that right, or do you perhaps mean they're foreign keys?

    My query is should I use autonumber as a primary key for the TextMessage table.
    If you're using MS Access, I have to advise against autonumbers. Access still has a bug where the autonumber seed can revert to an earlier value. It's much more likely to occur in multiuser situations but not limited to it. In the best case, if you set up your own unique indices on those fields, you'll only get error messages and not corrupted data. It's possible to handle those errors via code, but not worth it.

    For other DBMSs, an autonumber sounds reasonable there.

Posting Permissions

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