locked
Is it always best to use autonumber with primary key? RRS feed

  • Question

  • User-1885804455 posted

    I have a table with a field called "Calendar_ID" which is set as primary key and have the data type "AutoNumber".

    I also have a field called "Date". I figure, since this field is unique (because the same date can only appear once in this table), could I then use this field as primary key instead? Or is it better to always have an "AutoNumer" as primary key? 

    Saturday, October 3, 2015 6:33 PM

Answers

  • User603616845 posted

    Hi,

    If you have a table with a column that is of date type and where no two rows will ever have the same date, then you can surely use this column as PRIMARY KEY.

    You see a lot of examples where the Primary Key is a simple UNSIGNED INTEGER because there are many cases where there is no perfect candidate for Primary Key. The AUTO_INCREMENT allows this column to be automatically filled by the database (and be unique) during inserts .

    Please find discussion over primary key and date  here.

    https://asktom.oracle.com/pls/apex/f?p=100:11:::YES:RP:P11_QUESTION_ID:1330002539879

    Hope this will help you.

    thanks

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, October 3, 2015 11:29 PM
  • User-1923420989 posted

    It is better to keep the primary key as AutoNumber since you dont have to worry about uniqueness about across the records since

    Database server will take care of it.

    Or else if you want to set other field as primary key then you need to make sure the uniqueness by doing necessary checks.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, October 3, 2015 11:30 PM
  • User-1199946673 posted

    abrly

    It is better to keep the primary key as AutoNumber since you dont have to worry about uniqueness about across the records since

    Database server will take care of it.

    Or else if you want to set other field as primary key then you need to make sure the uniqueness by doing necessary checks.

    If the requirement is that the date is unique, the checks needs to be performed anyway, even if you add an autonumber field. Also, when you set the date to be the primary key, it wil be impossible to insert a duplicate date in the database, so the database wil take care of it also, even when the check is not performed before the insert. The same will happen when you add an autonumber field and create a (unique) index on the date field.

    I don't see any problem by using a (unique) date column as the primary key.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, October 5, 2015 5:51 AM

All replies

  • User603616845 posted

    Hi,

    If you have a table with a column that is of date type and where no two rows will ever have the same date, then you can surely use this column as PRIMARY KEY.

    You see a lot of examples where the Primary Key is a simple UNSIGNED INTEGER because there are many cases where there is no perfect candidate for Primary Key. The AUTO_INCREMENT allows this column to be automatically filled by the database (and be unique) during inserts .

    Please find discussion over primary key and date  here.

    https://asktom.oracle.com/pls/apex/f?p=100:11:::YES:RP:P11_QUESTION_ID:1330002539879

    Hope this will help you.

    thanks

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, October 3, 2015 11:29 PM
  • User-1923420989 posted

    It is better to keep the primary key as AutoNumber since you dont have to worry about uniqueness about across the records since

    Database server will take care of it.

    Or else if you want to set other field as primary key then you need to make sure the uniqueness by doing necessary checks.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, October 3, 2015 11:30 PM
  • User-1716253493 posted

    Use AutoNumber to determine each record in the database instead the date

    Sunday, October 4, 2015 10:27 PM
  • User-1199946673 posted

    abrly

    It is better to keep the primary key as AutoNumber since you dont have to worry about uniqueness about across the records since

    Database server will take care of it.

    Or else if you want to set other field as primary key then you need to make sure the uniqueness by doing necessary checks.

    If the requirement is that the date is unique, the checks needs to be performed anyway, even if you add an autonumber field. Also, when you set the date to be the primary key, it wil be impossible to insert a duplicate date in the database, so the database wil take care of it also, even when the check is not performed before the insert. The same will happen when you add an autonumber field and create a (unique) index on the date field.

    I don't see any problem by using a (unique) date column as the primary key.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, October 5, 2015 5:51 AM