Forum Stats

  • 3,839,935 Users
  • 2,262,549 Discussions
  • 7,901,101 Comments

Discussions

Autoincrement of primary key

dvohra21
dvohra21 Member Posts: 14,626 Gold Crown
edited Nov 16, 2019 8:04PM in Database Ideas - Ideas

Oracle Database does not presently support autoincrement of primary key, just as MySQL does. Would be a suitable feature to have.

dvohra21
6 votes

Active · Last Updated

«1

Comments

  • FatMartinR
    FatMartinR Member Posts: 190 Blue Ribbon

    1.  Natural keys have no need to auto-increment.

    2.  Surrogate PKs implemented using Oracle sequences DO auto-increment.

    William RobertsonThorsten Kettner
  • William Robertson
    William Robertson Member Posts: 9,568 Bronze Crown

    We can have an autoincrementing column using generated always as identity, and we can make that the primary key, so I am not sure what new functionality is being suggested.

    Actually, converting an existing column into an identity column is not currently supported, and that would be nice to have, but I don't think this is what is being suggested here.

    Thorsten Kettnerberx
  • dvohra21
    dvohra21 Member Posts: 14,626 Gold Crown

    We can have an autoincrementing column using generated always as identity, and we can make that the primary key, so I am not sure what new functionality is being suggested.

    Actually, converting an existing column into an identity column is not currently supported, and that would be nice to have, but I don't think this is what is being suggested here.

    We can have an autoincrementing column using generated always as identity, and we can make that the primary key, so I am not sure what new functionality is being suggested.

    Identity columns are available in 12c and latter versions only and identity columns are based on sequences internally. But identity columns have issues with DML (INSERT statement). As an example,

    CREATE TABLE identity_test_tab (

      id          NUMBER GENERATED ALWAYS AS IDENTITY,

      description VARCHAR2(30)

    );

    SQL> INSERT INTO identity_test_tab (id, description) VALUES (999, 'ID=999 and DESCRIPTION');

    INSERT INTO identity_test_tab (id, description) VALUES (999, 'ID=999 and DESCRIPTION')

                                   *

    ERROR at line 1:

    ORA-32795: cannot insert into a generated always identity column

  • William Robertson
    William Robertson Member Posts: 9,568 Bronze Crown

    We can have an autoincrementing column using generated always as identity, and we can make that the primary key, so I am not sure what new functionality is being suggested.

    Identity columns are available in 12c and latter versions only and identity columns are based on sequences internally. But identity columns have issues with DML (INSERT statement). As an example,

    CREATE TABLE identity_test_tab (

      id          NUMBER GENERATED ALWAYS AS IDENTITY,

      description VARCHAR2(30)

    );

    SQL> INSERT INTO identity_test_tab (id, description) VALUES (999, 'ID=999 and DESCRIPTION');

    INSERT INTO identity_test_tab (id, description) VALUES (999, 'ID=999 and DESCRIPTION')

                                   *

    ERROR at line 1:

    ORA-32795: cannot insert into a generated always identity column

    Well, if you are going to "generate always" then you can't use some other arbitrary value, so the error message makes sense. But back to your proposal for a new feature...

  • Sven W.
    Sven W. Member Posts: 10,541 Gold Crown
    edited Nov 26, 2019 6:37AM

    We can have an autoincrementing column using generated always as identity, and we can make that the primary key, so I am not sure what new functionality is being suggested.

    Identity columns are available in 12c and latter versions only and identity columns are based on sequences internally. But identity columns have issues with DML (INSERT statement). As an example,

    CREATE TABLE identity_test_tab (

      id          NUMBER GENERATED ALWAYS AS IDENTITY,

      description VARCHAR2(30)

    );

    SQL> INSERT INTO identity_test_tab (id, description) VALUES (999, 'ID=999 and DESCRIPTION');

    INSERT INTO identity_test_tab (id, description) VALUES (999, 'ID=999 and DESCRIPTION')

                                   *

    ERROR at line 1:

    ORA-32795: cannot insert into a generated always identity column

    There are many options how to use sequences in the correct way. The "autoincrement" feature is there since 12c.

    You never explained what you understand by it.

    For all objections raised so far, there are solutions, even if you don't know them.

    So I don't see a need to add any other option that does (almost?) the same as what is possible already

    For example when you call the feature AUTOincrement. Why would you allow MANUAL setting of the surrogate key?
    Btw. this is possible useing a very similar feature that was also introduced in 12c. We can set a default value for the column and say this default kicks in, if the value provided is null: "DEFAULT mySeq.nextval ON NULL"  . Which also means, we can set other values, where the default will not overwrite our value. However then it is not generated always.

    The main problem here seems to be, that you want to use some "magic values" like 999. Which is generally considered bad programming practice. Maybe I misunderstand your intention, but this is what your example does look like. If you have an unknown value, why not use NULL at this point in time.

    https://en.wikipedia.org/wiki/Magic_number_%28programming%29

    The term magic number or magic constant refers to the anti-pattern of using numbers directly in source code. This has been referred to as breaking one of the oldest rules of programming, dating back to the COBOL, FORTRAN and PL/1 manuals of the 1960s.
    [1]
  • FatMartinR
    FatMartinR Member Posts: 190 Blue Ribbon

    Guys, to be clear, there is no such thing as an 'auto-incrementing column'.     All this stuff with 12c having GENERATED ALWAYS AS IDENTITY  is a bit of a red herring.

    It is the sequence you assign to a column that auto-increments (never the column itself -- that is non-sensical).   It is a property of Oracle sequences.   All this GENERATED ALWAYS AS IDENTITY stuff is just a mechanism to hide the complexities.

    But to make another point clear too, you should not be using sequences numbers as primary keys universally.   Typically you use the natural keys available in the data.   Only when you cannot find one do you use a sequence as a surrogate key.     Using surrogate PKs universally leads to performance degradation, unnecessary storage increase & increased code complexity.

  • Sven W.
    Sven W. Member Posts: 10,541 Gold Crown

    Guys, to be clear, there is no such thing as an 'auto-incrementing column'.     All this stuff with 12c having GENERATED ALWAYS AS IDENTITY  is a bit of a red herring.

    It is the sequence you assign to a column that auto-increments (never the column itself -- that is non-sensical).   It is a property of Oracle sequences.   All this GENERATED ALWAYS AS IDENTITY stuff is just a mechanism to hide the complexities.

    But to make another point clear too, you should not be using sequences numbers as primary keys universally.   Typically you use the natural keys available in the data.   Only when you cannot find one do you use a sequence as a surrogate key.     Using surrogate PKs universally leads to performance degradation, unnecessary storage increase & increased code complexity.

    It depends.

    Natural keys would be translated to Unique Constraints. Whereas meaningless technical keys (surrogate keys) end up being Primary Keys.
    IMO when to use that pattern very much depends on the volatility of the data. Natuaral keys in general are subject to change (even the most stable ones could hold typos). And then are very hard to change. So code complexity decreases because of using surrogate keys instead.

    However I agree that this pattern does not need to be applied everywhere and in every case.

    On a different note,

    the term autoincrement is used in other databases. Some of them do not comply to all abilities that proper modern RDBMSes have. For example MS Access is not a true multi user system - therefore making a column that autoincrement its values, is a fairly easy task.

    The closest thing in the Oracle world is the identity column (which behind the scenes is implemented using a sequence). Now I don't see any problem in calling such a column also autoincrement. As long as the expectations do not imply something other then "generated automatically".

  • FatMartinR
    FatMartinR Member Posts: 190 Blue Ribbon

    It depends.

    Natural keys would be translated to Unique Constraints. Whereas meaningless technical keys (surrogate keys) end up being Primary Keys.
    IMO when to use that pattern very much depends on the volatility of the data. Natuaral keys in general are subject to change (even the most stable ones could hold typos). And then are very hard to change. So code complexity decreases because of using surrogate keys instead.

    However I agree that this pattern does not need to be applied everywhere and in every case.

    On a different note,

    the term autoincrement is used in other databases. Some of them do not comply to all abilities that proper modern RDBMSes have. For example MS Access is not a true multi user system - therefore making a column that autoincrement its values, is a fairly easy task.

    The closest thing in the Oracle world is the identity column (which behind the scenes is implemented using a sequence). Now I don't see any problem in calling such a column also autoincrement. As long as the expectations do not imply something other then "generated automatically".

    > "It depends.      Natural keys would be translated to Unique Constraints. Whereas meaningless technical keys (surrogate keys) end up being Primary Keys".

    Not true.    Natural keys become the database's primary keys.

    CREATE TABLE Natrual_PKs (COLUMN_1_of_PK  VARCHAR2(30) NOT NULL,

                                                        COLUMN_2_of_PK  INTEGER           NOT NULL,

                                                        COLUMN_3_of_PK  DATE                 NOT NULL,

                                                        ...

                                                       Other Columns Go Here

                                                       ...

    CONSTRAINT This_Table_PK PRIMARY KEY (COLUMN_1_of_PK, COLUMN_2_of_PK, COLUMN_3_of_PK))

    /


    > "Natural keys in general are subject to change..."

    No they are not.     Any/all primary keys are immutable -- that means they NEVER change.    If you find your 'natural key' changing, then it was never the natural key in the first place.

    > "So code complexity decreases because of using surrogate keys instead."

    I say code complexity increases due to the meaningless numbers used in the table referencing them, and the constant join-backs needed to make sense of them.

    > "However I agree that this pattern does not need to be applied everywhere and in every case".

    Static code & description tables will NEVER need a surrogate PK.    Always use the natural key in this instance.   

    The more dynamic tables in a system might sometimes be better referenced with a surrogate;  but not always.     Again, remember when you do, you lose performance (from the addition of an index on the surrogate PK and the join-backs associated with it), that you can never re-gain.    It is a judgement call as to whether the benefits brought about by a surrogate PK out-way the performance losses by its introduction.

    However, if you were to err on one side, then definitely go for the universal surrogate PK approach -- although I will call you lazy + amateur for not actually doing it properly.

    > "For example MS Access is not a true multi user system..."

    Ugh.   MS Access is a child's toy, not a serious database.

    > "The closest thing in the Oracle world is the identity column (which behind the scenes is implemented using a sequence)".

    You do know any column can have a sequence make use of it, don't you?   This IDENTITY business is just syntactical sugar designed to hide its complexities.

    Oracle sequences & the columns that use them are totally separate entities.    They only get linked when you do it with your code.

    I can create a sequence without any reference to a column.    Simply,

    CREATE SEQUENCE ABC;

    > "Now I don't see any problem in calling such a column also auto-increment".

    Well, the column itself doesn't increment, rather than value you as a coder put in it.    (Just a matter of semantics really).

  • Sven W.
    Sven W. Member Posts: 10,541 Gold Crown
    edited Nov 26, 2019 11:33AM

    > "It depends.      Natural keys would be translated to Unique Constraints. Whereas meaningless technical keys (surrogate keys) end up being Primary Keys".

    Not true.    Natural keys become the database's primary keys.

    CREATE TABLE Natrual_PKs (COLUMN_1_of_PK  VARCHAR2(30) NOT NULL,

                                                        COLUMN_2_of_PK  INTEGER           NOT NULL,

                                                        COLUMN_3_of_PK  DATE                 NOT NULL,

                                                        ...

                                                       Other Columns Go Here

                                                       ...

    CONSTRAINT This_Table_PK PRIMARY KEY (COLUMN_1_of_PK, COLUMN_2_of_PK, COLUMN_3_of_PK))

    /


    > "Natural keys in general are subject to change..."

    No they are not.     Any/all primary keys are immutable -- that means they NEVER change.    If you find your 'natural key' changing, then it was never the natural key in the first place.

    > "So code complexity decreases because of using surrogate keys instead."

    I say code complexity increases due to the meaningless numbers used in the table referencing them, and the constant join-backs needed to make sense of them.

    > "However I agree that this pattern does not need to be applied everywhere and in every case".

    Static code & description tables will NEVER need a surrogate PK.    Always use the natural key in this instance.   

    The more dynamic tables in a system might sometimes be better referenced with a surrogate;  but not always.     Again, remember when you do, you lose performance (from the addition of an index on the surrogate PK and the join-backs associated with it), that you can never re-gain.    It is a judgement call as to whether the benefits brought about by a surrogate PK out-way the performance losses by its introduction.

    However, if you were to err on one side, then definitely go for the universal surrogate PK approach -- although I will call you lazy + amateur for not actually doing it properly.

    > "For example MS Access is not a true multi user system..."

    Ugh.   MS Access is a child's toy, not a serious database.

    > "The closest thing in the Oracle world is the identity column (which behind the scenes is implemented using a sequence)".

    You do know any column can have a sequence make use of it, don't you?   This IDENTITY business is just syntactical sugar designed to hide its complexities.

    Oracle sequences & the columns that use them are totally separate entities.    They only get linked when you do it with your code.

    I can create a sequence without any reference to a column.    Simply,

    CREATE SEQUENCE ABC;

    > "Now I don't see any problem in calling such a column also auto-increment".

    Well, the column itself doesn't increment, rather than value you as a coder put in it.    (Just a matter of semantics really).

    Myself and Wikipedia disagrees with your definition of natural keys.

    https://en.wikipedia.org/wiki/Natural_key

    A natural key (also known as business key
    [1]) is a type of unique key in a database formed of attributes that exist and are used in the external world outside the database (i.e. in the business domain or domain of discourse).[2] In the relational model of data, a natural key is a candidate key and is therefore a functional determinant for all attributes in a relation. A natural key is sometimes called domain key.[3]

    A natural key serves two complementary purposes: it provides a means of identification for data and it imposes a rule, specifically a uniqueness constraint, to ensure that data remains unique within an information system. The uniqueness constraint assures uniqueness of data within a certain technical context (e.g. a set of values in a table, file or relation variable) by rejecting input of any data that would otherwise violate the constraint. This means that the user can rely on a guaranteed correspondence between facts identified by key values recorded in a system and the external domain of discourse (a single version of the truth).

    and later

    The presence of a key guarantees uniqueness within an information system but it is not always necessary that the key values be unique or immutable within some wider population of objects or concepts outside that system.

    The problem is natural keys are determined by factors outside of the system. Which mean the determining factors are subject to change.

    The main point I'm trying to make, is there there is a varying degree of likeliness that such a key changes.

    For some tables it is ok to rely on natural keys and use them as the primary key (which also implies that it is referred from other tables via a foreign key). For example the "static tables" that you mentioned but never clearly specified whan you consider a table as static. For many tables it is not ok to rely on a natural key, they are better served using a meaningless technical key - either because there is no good candidate key or because the natural key has a (low) chance to need changing.

    I also think our opinion differs because you look at that from an OLAP/DWH standpoint whereas I see it more from an OLTP side.

    For DWHs the input data usually comes from another IT system, for OLTP it comes from end users. The result is that all data from those end users are way more likely to change, and that includes the natural keys (like their social security number).

    btw: I know that we could even point a FK upon a UK, but here we are talking best practices and not esoteric side examples that nobody in the real world implements.

  • FatMartinR
    FatMartinR Member Posts: 190 Blue Ribbon

    Myself and Wikipedia disagrees with your definition of natural keys.

    https://en.wikipedia.org/wiki/Natural_key

    A natural key (also known as business key
    [1]) is a type of unique key in a database formed of attributes that exist and are used in the external world outside the database (i.e. in the business domain or domain of discourse).[2] In the relational model of data, a natural key is a candidate key and is therefore a functional determinant for all attributes in a relation. A natural key is sometimes called domain key.[3]

    A natural key serves two complementary purposes: it provides a means of identification for data and it imposes a rule, specifically a uniqueness constraint, to ensure that data remains unique within an information system. The uniqueness constraint assures uniqueness of data within a certain technical context (e.g. a set of values in a table, file or relation variable) by rejecting input of any data that would otherwise violate the constraint. This means that the user can rely on a guaranteed correspondence between facts identified by key values recorded in a system and the external domain of discourse (a single version of the truth).

    and later

    The presence of a key guarantees uniqueness within an information system but it is not always necessary that the key values be unique or immutable within some wider population of objects or concepts outside that system.

    The problem is natural keys are determined by factors outside of the system. Which mean the determining factors are subject to change.

    The main point I'm trying to make, is there there is a varying degree of likeliness that such a key changes.

    For some tables it is ok to rely on natural keys and use them as the primary key (which also implies that it is referred from other tables via a foreign key). For example the "static tables" that you mentioned but never clearly specified whan you consider a table as static. For many tables it is not ok to rely on a natural key, they are better served using a meaningless technical key - either because there is no good candidate key or because the natural key has a (low) chance to need changing.

    I also think our opinion differs because you look at that from an OLAP/DWH standpoint whereas I see it more from an OLTP side.

    For DWHs the input data usually comes from another IT system, for OLTP it comes from end users. The result is that all data from those end users are way more likely to change, and that includes the natural keys (like their social security number).

    btw: I know that we could even point a FK upon a UK, but here we are talking best practices and not esoteric side examples that nobody in the real world implements.

    Before I start, we need to clear up some terminology:

    "Key" - that which is used (predicates) to query the data,

    "Unique Key" - a key that is unique within the bounds of the tuple (aka. table),

    "Primary Key" - the main unique key.

    Wikipedia's definition is not contrary to mine.       A natural key (also known as business key[1]) is a type of unique key in a database

    Well that's true, but it doesn't stop it also being the primary key.    All PKs are - by definition - unique keys.     Primary Key is a subset of Unique Key;  a special case.

    The presence of a key guarantees uniqueness within an information system but it is not always necessary that the key values be unique or immutable within some wider population of objects or concepts outside that system.

    Well, I assume it means "The presence of a UNIQUE key...".        Then....  what is meant by "the system" ?      Ofc. you may have a unique key in your own database, but it might not be unique out in the real world.   It depends on how you draw the boundaries to your system.

    > "The problem is natural keys are determined by factors outside of the system. Which mean the determining factors are subject to change.  

    The main point I'm trying to make, is there there is a varying degree of likeliness that such a key changes".

    But the definition of a primary key includes the attribute that it must be immutable -- otherwise it is not (and never was) the primary key, and you incorrectly analysed it as such.

    > "For some tables it is ok to rely on natural keys and use them as the primary key (which also implies that it is referred from other tables via a foreign key). For many tables it is not, they are better served using a meaningless technical key..."

    Certainly, where a natural key exists, then sometimes a table can be better served with a surrogate PK, but you need to juggle the pros & cons and see if it's worth it.

    Don't just make all your PKs surrogate ones - justify their usage.

    > "... either becuase there is no good candidate or because the natural key has a (low) change to need changing".

    Remember, if the natural primary key is changing, then it never was the PK.

    > "I also think our opinion differs because you look at that from an OLAP/DWH standpoint whereas I see it more from an OLTP side.

    For DWHs the input data usually comes from another IT system, for OLTP it comes from end users. The result is that all data from those end users are way more likely to change, and that includes the natural keys (like their social security number)".

    Then their social security number never was the PK.     In fact, a PERSONS table is a classic example of where you DO need to use a surrogate PK.    NOTHING defines us humans as being unique, and certainly not a social security number, email address or telephone number, which although you might feel would all stay the same throughout our lifetime, can change.