Derby
  1. Derby
  2. DERBY-167 Inserting values in an identity column
  3. DERBY-275

Add documentation support for BY DEFAULT option, once code changes are made.

    Details

    • Type: Sub-task Sub-task
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 10.0.2.1
    • Fix Version/s: 10.1.1.0
    • Component/s: Documentation
    • Labels:
      None

      Description

      Once a patch is accepted, new syntax needs to be documented.

        Activity

        Hide
        Kathey Marsden added a comment -

        REGARDING THIS PARAGRAPH:

        Note that unlike a GENERATED ALWAYS column, a GENERATED BY DEFAULT column does not guarantee uniqueness. Thus, in the above example, the hi and salut rows will both have an identity value of "1", because the generated column starts at "1" and the user-specified value was also "1". To prevent this, you can use the "STARTS WITH" keyword described below. To check for this condition and disallow it, you can use a primary key or unique constraint on the GENERATED BY DEFAULT identity column.

        In the paragraph STARTS WITH should be START WITH and loading or importing data should be covered.

        CHANGE:
        To prevent this, you can use the "STARTS WITH" keyword described below.

        TO:

        To prevent duplication especially when loading or importing data, create the table using a "START WITH" value which corresponds to the first
        identity value that the system should assign.

        ADD AN EXAMPLE:

        create table greetings
        (i int generated by default as identity (START WITH 2, INCREMENT BY 1),
        ch char(50));
        – specify value "1":
        insert into greetings values (1, 'hi');
        – use generated default
        insert into greetings values (DEFAULT, 'salut');
        – use generated default
        insert into greetings(ch) values ('bonjour');

        Show
        Kathey Marsden added a comment - REGARDING THIS PARAGRAPH: Note that unlike a GENERATED ALWAYS column, a GENERATED BY DEFAULT column does not guarantee uniqueness. Thus, in the above example, the hi and salut rows will both have an identity value of "1", because the generated column starts at "1" and the user-specified value was also "1". To prevent this, you can use the "STARTS WITH" keyword described below. To check for this condition and disallow it, you can use a primary key or unique constraint on the GENERATED BY DEFAULT identity column. In the paragraph STARTS WITH should be START WITH and loading or importing data should be covered. CHANGE: To prevent this, you can use the "STARTS WITH" keyword described below. TO: To prevent duplication especially when loading or importing data, create the table using a "START WITH" value which corresponds to the first identity value that the system should assign. ADD AN EXAMPLE: create table greetings (i int generated by default as identity (START WITH 2, INCREMENT BY 1), ch char(50)); – specify value "1": insert into greetings values (1, 'hi'); – use generated default insert into greetings values (DEFAULT, 'salut'); – use generated default insert into greetings(ch) values ('bonjour');
        Hide
        Jean T. Anderson added a comment -

        Committed, revision 201514, Jeff Levitt's patch that documents the BY DEFAULT option. Modified files:
        $ svn status src
        M src/ref/rrefsqlj24513.dita
        M src/ref/rrefsqlj37836.dita
        M src/ref/rrefsqlj30540.dita

        Show
        Jean T. Anderson added a comment - Committed, revision 201514, Jeff Levitt's patch that documents the BY DEFAULT option. Modified files: $ svn status src M src/ref/rrefsqlj24513.dita M src/ref/rrefsqlj37836.dita M src/ref/rrefsqlj30540.dita
        Hide
        Satheesh Bandaram added a comment -

        I did a quick review... This looks good and can be committed.

        Show
        Satheesh Bandaram added a comment - I did a quick review... This looks good and can be committed.
        Hide
        A B added a comment -

        This looks okay to me. I think Satheesh and/or Tomohito should look it over to make sure, but if they have no comments, then I say commit it...

        Show
        A B added a comment - This looks okay to me. I think Satheesh and/or Tomohito should look it over to make sure, but if they have no comments, then I say commit it...
        Hide
        Jeff Levitt added a comment -

        Attached is a new patch fixing the problems requested by Army in:
        http://mail-archives.apache.org/mod_mbox/db-derby-dev/200506.mbox/%3c42B83585.3020708@sbcglobal.net%3e

        Army, I think your points are valid, and no one else had any objections, so I made the changes. The column default link was bad because when we moved it to the other file, the pointer to it wasn't changed to point to the new file, but it should look fine now.

        Show
        Jeff Levitt added a comment - Attached is a new patch fixing the problems requested by Army in: http://mail-archives.apache.org/mod_mbox/db-derby-dev/200506.mbox/%3c42B83585.3020708@sbcglobal.net%3e Army, I think your points are valid, and no one else had any objections, so I made the changes. The column default link was bad because when we moved it to the other file, the pointer to it wasn't changed to point to the new file, but it should look fine now.
        Hide
        Jeff Levitt added a comment -

        Attached is the new patch. I just made all the changes you requested, it was easy to just make the changes in this issue, since they are all related. Please let me know if all looks good. Thanks!

        Show
        Jeff Levitt added a comment - Attached is the new patch. I just made all the changes you requested, it was easy to just make the changes in this issue, since they are all related. Please let me know if all looks good. Thanks!
        Hide
        A B added a comment -

        With this latest patch, it looks like some text in the "CREATE TABLE" section was orphaned. More specifically, the following lines should be moved to the "Identity column attributes" section with the rest of the related info:


        The IDENTITY keyword can only be specified if the data type associated with the column is one of the following exact integer types.

        • SMALLINT
        • INT
        • BIGINT

        Actually, there seems to be a lot of column-specific information under the "CREATE TABLE" section that would be better if moved to the "Column-definition" section. For example, we talk about "Data types", "Column-level-constraints", and column defaults when none of those are used in the syntax diagram for CREATE TABLE. Wouldn't all of that make more sense under the "Column-definition" section? Of course, that's a problem that existed before this patch, so perhaps that should be a different issue to be filed against the Reference Manual later...?

        In any event, the above lines (at the very least) should be moved to the same section that describes the GENERATED ALWAYS/BY DEFAULT keywords, since that's where IDENTITY columns are specified.

        Show
        A B added a comment - With this latest patch, it looks like some text in the "CREATE TABLE" section was orphaned. More specifically, the following lines should be moved to the "Identity column attributes" section with the rest of the related info: The IDENTITY keyword can only be specified if the data type associated with the column is one of the following exact integer types. SMALLINT INT BIGINT Actually, there seems to be a lot of column-specific information under the "CREATE TABLE" section that would be better if moved to the "Column-definition" section. For example, we talk about "Data types", "Column-level-constraints", and column defaults when none of those are used in the syntax diagram for CREATE TABLE. Wouldn't all of that make more sense under the "Column-definition" section? Of course, that's a problem that existed before this patch, so perhaps that should be a different issue to be filed against the Reference Manual later...? In any event, the above lines (at the very least) should be moved to the same section that describes the GENERATED ALWAYS/BY DEFAULT keywords, since that's where IDENTITY columns are specified.
        Hide
        Jeff Levitt added a comment -

        Attached patch add Army's feedback as requested in:

        http://article.gmane.org/gmane.comp.apache.db.derby.devel/5792

        Please verify that all looks good. Thanks!

        Show
        Jeff Levitt added a comment - Attached patch add Army's feedback as requested in: http://article.gmane.org/gmane.comp.apache.db.derby.devel/5792 Please verify that all looks good. Thanks!
        Hide
        Jeff Levitt added a comment -

        Attached zip includes a patch that attempts to document this in the generated-column-spec topic. HTML output is included for review.

        Show
        Jeff Levitt added a comment - Attached zip includes a patch that attempts to document this in the generated-column-spec topic. HTML output is included for review.
        Hide
        Jeff Levitt added a comment -

        I was going to try to make a pass at a patch for the docs for this issue. It seems to me the only place that needs a change is in the Reference Manual, to change the syntax for generated column spec for Create Table to allow the option of either ALWAYS or BY DEFAULT. Is that the only place that needs changing? It didn't look to me like there needed to be any changes to anything about dblook, because that change was under the covers...?

        If someone can enlighten me, I could submit a patch very soon...Thanks!

        Show
        Jeff Levitt added a comment - I was going to try to make a pass at a patch for the docs for this issue. It seems to me the only place that needs a change is in the Reference Manual, to change the syntax for generated column spec for Create Table to allow the option of either ALWAYS or BY DEFAULT. Is that the only place that needs changing? It didn't look to me like there needed to be any changes to anything about dblook, because that change was under the covers...? If someone can enlighten me, I could submit a patch very soon...Thanks!

          People

          • Assignee:
            Unassigned
            Reporter:
            Satheesh Bandaram
          • Votes:
            1 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved:

              Development