Derby Reference Manual

Apache Software FoundationDerby Reference ManualApache Derby

Licensed under the Apache License, Version 2.0 (the "License"); you may not use this file except in compliance with the License. You may obtain a copy of the License at http://www.apache.org/licenses/LICENSE-2.0.

Related information

License

The Apache License, Version 2.0


                               Apache License
                           Version 2.0, January 2004
                        http://www.apache.org/licenses/

   TERMS AND CONDITIONS FOR USE, REPRODUCTION, AND DISTRIBUTION

   1. Definitions.

      "License" shall mean the terms and conditions for use,
      reproduction, and distribution as defined by Sections 1 through
      9 of this document.

      "Licensor" shall mean the copyright owner or entity authorized
      by the copyright owner that is granting the License.

      "Legal Entity" shall mean the union of the acting entity and all
      other entities that control, are controlled by, or are under
      common control with that entity. For the purposes of this
      definition, "control" means (i) the power, direct or indirect,
      to cause the direction or management of such entity, whether by
      contract or otherwise, or (ii) ownership of fifty percent (50%)
      or more of the outstanding shares, or (iii) beneficial ownership
      of such entity.

      "You" (or "Your") shall mean an individual or Legal Entity
      exercising permissions granted by this License.

      "Source" form shall mean the preferred form for making
      modifications, including but not limited to software source code,
      documentation source, and configuration files.

      "Object" form shall mean any form resulting from mechanical
      transformation or translation of a Source form, including but
      not limited to compiled object code, generated documentation,
      and conversions to other media types.

      "Work" shall mean the work of authorship, whether in Source or
      Object form, made available under the License, as indicated by a
      copyright notice that is included in or attached to the work
      (an example is provided in the Appendix below).

      "Derivative Works" shall mean any work, whether in Source or
      Object form, that is based on (or derived from) the Work and
      for which the editorial revisions, annotations, elaborations,
      or other modifications represent, as a whole, an original work
      of authorship.  For the purposes of this License, Derivative
      Works shall not include works that remain separable from, or
      merely link (or bind by name) to the interfaces of, the Work
      and Derivative Works thereof.

      "Contribution" shall mean any work of authorship, including
      the original version of the Work and any modifications or
      additions to that Work or Derivative Works thereof, that is
      intentionally submitted to Licensor for inclusion in the Work
      by the copyright owner or by an individual or Legal Entity
      authorized to submit on behalf of the copyright owner. For the
      purposes of this definition,
      "submitted" means any form of electronic, verbal, or written
      communication sent to the Licensor or its representatives,
      including but not limited to communication on electronic mailing
      lists, source code control systems, and issue tracking systems
      that are managed by, or on behalf of, the Licensor for the
      purpose of discussing and improving the Work, but excluding
      communication that is conspicuously marked or otherwise
      designated in writing by the copyright owner as "Not a
      Contribution."

      "Contributor" shall mean Licensor and any individual or Legal
      Entity on behalf of whom a Contribution has been received by
      Licensor and subsequently incorporated within the Work.

   2. Grant of Copyright License. Subject to the terms and conditions
      of this License, each Contributor hereby grants to You a
      perpetual, worldwide, non-exclusive, no-charge, royalty-free,
      irrevocable copyright license to reproduce, prepare Derivative
      Works of, publicly display, publicly perform, sublicense, and
      distribute the Work and such Derivative Works in Source or
      Object form.

   3. Grant of Patent License. Subject to the terms and conditions of
      this License, each Contributor hereby grants to You a perpetual,
      worldwide, non-exclusive, no-charge, royalty-free, irrevocable
      (except as stated in this section) patent license to make, have
      made, use, offer to sell, sell, import, and otherwise transfer
      the Work, where such license applies only to those patent claims
      licensable by such Contributor that are necessarily infringed by
      their Contribution(s) alone or by combination of their
      Contribution(s) with the Work to which such Contribution(s) was
      submitted. If You institute patent litigation against any entity
      (including a cross-claim or counterclaim in a lawsuit) alleging
      that the Work or a Contribution incorporated within the Work
      constitutes direct or contributory patent infringement, then any
      patent licenses granted to You under this License for that Work
      shall terminate as of the date such litigation is filed.

   4. Redistribution. You may reproduce and distribute copies of the
      Work or Derivative Works thereof in any medium, with or without
      modifications, and in Source or Object form, provided that You
      meet the following conditions:

      (a) You must give any other recipients of the Work or
          Derivative Works a copy of this License; and

      (b) You must cause any modified files to carry prominent notices
          stating that You changed the files; and

      (c) You must retain, in the Source form of any Derivative Works
          that You distribute, all copyright, patent, trademark, and
          attribution notices from the Source form of the Work,
          excluding those notices that do not pertain to any part of
          the Derivative Works; and

      (d) If the Work includes a "NOTICE" text file as part of its
          distribution, then any Derivative Works that You distribute
          must include a readable copy of the attribution notices
          contained within such NOTICE file, excluding those notices
          that do not pertain to any part of the Derivative Works, in
          at least one of the following places: within a NOTICE text
          file distributed as part of the Derivative Works; within the
          Source form or documentation, if provided along with the
          Derivative Works; or, within a display generated by the
          Derivative Works, if and wherever such third-party notices
          normally appear. The contents of the NOTICE file are for
          informational purposes only and do not modify the License.
          You may add Your own attribution notices within Derivative
          Works that You distribute, alongside or as an addendum to
          the NOTICE text from the Work, provided that such additional
          attribution notices cannot be construed as modifying the
          License.

      You may add Your own copyright statement to Your modifications
      and may provide additional or different license terms and
      conditions for use, reproduction, or distribution of Your
      modifications, or for any such Derivative Works as a whole,
      provided Your use, reproduction, and distribution of the Work
      otherwise complies with the conditions stated in this License.

   5. Submission of Contributions. Unless You explicitly state
      otherwise, any Contribution intentionally submitted for
      inclusion in the Work by You to the Licensor shall be under the
      terms and conditions of this License, without any additional
      terms or conditions.  Notwithstanding the above, nothing herein
      shall supersede or modify the terms of any separate license
      agreement you may have executed with Licensor regarding such
      Contributions.

   6. Trademarks. This License does not grant permission to use the
      trade names, trademarks, service marks, or product names of the
      Licensor, except as required for reasonable and customary use
      in describing the origin of the Work and reproducing the content
      of the NOTICE file.

   7. Disclaimer of Warranty. Unless required by applicable law or
      agreed to in writing, Licensor provides the Work (and each
      Contributor provides its Contributions) on an "AS IS" BASIS,
      WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or
      implied, including, without limitation, any warranties or
      conditions of TITLE, NON-INFRINGEMENT, MERCHANTABILITY, or
      FITNESS FOR A PARTICULAR PURPOSE. You are solely responsible for
      determining the appropriateness of using or redistributing the
      Work and assume any risks associated with Your exercise of
      permissions under this License.

   8. Limitation of Liability. In no event and under no legal theory,
      whether in tort (including negligence), contract, or otherwise,
      unless required by applicable law (such as deliberate and
      grossly negligent acts) or agreed to in writing, shall any
      Contributor be liable to You for damages, including any direct,
      indirect, special, incidental, or consequential damages of any
      character arising as a result of this License or out of the use
      or inability to use the Work (including but not limited to
      damages for loss of goodwill, work stoppage, computer failure or
      malfunction, or any and all other commercial damages or losses),
      even if such Contributor has been advised of the possibility of
      such damages.

   9. Accepting Warranty or Additional Liability. While redistributing
      the Work or Derivative Works thereof, You may choose to offer,
      and charge a fee for, acceptance of support, warranty, indemnity,
      or other liability obligations and/or rights consistent with this
      License. However, in accepting such obligations, You may act only
      on Your own behalf and on Your sole responsibility, not on behalf
      of any other Contributor, and only if You agree to indemnify,
      defend, and hold each Contributor harmless for any liability
      incurred by, or claims asserted against, such Contributor by
      reason of your accepting any such warranty or additional
      liability.

   END OF TERMS AND CONDITIONS

   APPENDIX: How to apply the Apache License to your work.

      To apply the Apache License to your work, attach the following
      boilerplate notice, with the fields enclosed by brackets "[]"
      replaced with your own identifying information. (Don't include
      the brackets!)  The text should be enclosed in the appropriate
      comment syntax for the file format. We also recommend that a
      file or class name and description of purpose be included on the
      same "printed page" as the copyright notice for easier
      identification within third-party archives.

   Copyright [yyyy] [name of copyright owner]

   Licensed under the Apache License, Version 2.0 (the "License");
   you may not use this file except in compliance with the License.
   You may obtain a copy of the License at

       http://www.apache.org/licenses/LICENSE-2.0

   Unless required by applicable law or agreed to in writing, software
   distributed under the License is distributed on an "AS IS" BASIS,
   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or
   implied.  See the License for the specific language governing
   permissions and limitations under the License.

About this guide

For general information about the Derby documentation, such as a complete list of books, conventions, and further reading, see Getting Started with Derby.

Purpose of this document

This book, the Derby Reference Manual, provides reference information about Derby. It covers Derby's SQL language, the Derby implementation of JDBC, Derby system catalogs, Derby error messages, Derby properties, and SQL keywords.

Audience

This book is a reference for Derby users, typically application developers. Derby users who are not familiar with the SQL standard or the Java programming language will benefit from consulting books on those topics.

Derby users who want a how-to approach to working with Derby or an introduction to Derby concepts should read the Derby Developer's Guide.

How this guide is organized

This guide includes the following sections:

•		SQL language reference Reference information about Derby's SQL language, including manual pages for statements, functions, and other syntax elements.
•		SQL reserved words SQL keywords beyond the standard SQL-92 keywords.
•		Derby support for SQL-92 features A list of SQL-92 features that Derby does and does not support.
•		Derby system tables Reference information about the Derby system catalogs.
•		Derby exception messages and SQL states Information about Derby exception messages.
•		JDBC reference Information about Derby's implementation of the JDBC interface including support for JDBC 2.0 features.
•		Setting attributes for the database connection URL Information about the supported attributes to Derby's JDBC database connection URL.
•		J2EE Compliance: Java Transaction API and javax.sql Interfaces Information about the supported attributes to Derby's support for the Java Transaction API.
•		Derby API Notes about proprietary APIs for Derby.

SQL language reference

Derby implements an SQL-92 core subset, as well as some SQL-99 features.

This section provides an overview of the current SQL language by describing the statements, built-in functions, data types, expressions, and special characters it contains.

Capitalization and special characters

Using the classes and methods of JDBC, you submit SQL statements to Derby as strings. The character set permitted for strings containing SQL statements is Unicode. Within these strings, the following rules apply:

•		Double quotation marks delimit special identifiers referred to in SQL-92 as delimited identifiers.
•		Single quotation marks delimit character strings.
•		Within a character string, to represent a single quotation mark or apostrophe, use two single quotation marks. (In other words, a single quotation mark is the escape character for a single quotation mark.) A double quotation mark does not need an escape character. To represent a double quotation mark, simply use a double quotation mark. However, note that in a Java program, a double quotation mark requires the backslash escape character. Example: -- a single quotation mark is the escape character -- for a single quotation mark VALUES 'Joe''s umbrella' -- in ij, you don't need to escape the double quotation marks VALUES 'He said, "hello!"' n = stmt.executeUpdate( "UPDATE aTable setStringcol = 'He said, \"hello!\"'");
•		SQL keywords are case-insensitive. For example, you can type the keyword SELECT as SELECT, Select, select, or sELECT.
•		SQL-92-style identifiers are case-insensitive (see SQL92Identifier), unless they are delimited.
•		Java-style identifiers are always case-sensitive.
•		* is a wildcard within a SelectExpression. See The * wildcard. It can also be the multiplication operator. In all other cases, it is a syntactical metasymbol that flags items you can repeat 0 or more times.
•		% and _ are character wildcards when used within character strings following a LIKE operator (except when escaped with an escape character). See Boolean expressions.
•		Comments can be either single- or multiline as per the SQL-92 standard. Singleline comments start with two dashes (--) and end with the newline character. Multiline comments are bracketed and start with forward slash star (/), and end with star forward slash (/). Note that bracketed comments may be nested. Any text between the starting and ending comment character sequence is ignored.

SQL identifiers

An identifier is the representation within the language of items created by the user, as opposed to language keywords or commands. Some identifiers stand for dictionary objects, which are the objects you create- such as tables, views, indexes, columns, and constraints- that are stored in a database. They are called dictionary objects because Derby stores information about them in the system tables, sometimes known as a data dictionary. SQL also defines ways to alias these objects within certain statements.

Each kind of identifier must conform to a different set of rules. Identifiers representing dictionary objects must conform to SQL-92 identifier rules and are thus called SQL92Identifiers.

Rules for SQL92 identifiers

Ordinary identifiers are identifiers not surrounded by double quotation marks. Delimited identifiers are identifiers surrounded by double quotation marks.

An ordinary identifier must begin with a letter and contain only letters, underscore characters (_), and digits. The permitted letters and digits include all Unicode letters and digits, but Derby does not attempt to ensure that the characters in identifiers are valid in the database's locale.

A delimited identifier can contain any characters within the double quotation marks. The enclosing double quotation marks are not part of the identifier; they serve only to mark its beginning and end. Spaces at the end of a delimited identifier are insignificant (truncated). Derby translates two consecutive double quotation marks within a delimited identifier as one double quotation mark-that is, the "translated" double quotation mark becomes a character in the delimited identifier.

Periods within delimited identifiers are not separators but are part of the identifier (the name of the dictionary object being represented).

So, in the following example:

"A.B"

is a dictionary object, while

"A"."B"

is a dictionary object qualified by another dictionary object (such as a column named "B" within the table "A").

SQL92Identifier

An SQL92Identifier is a dictionary object identifier that conforms to the rules of SQL-92. SQL-92 states that identifiers for dictionary objects are limited to 128 characters and are case-insensitive (unless delimited by double quotes), because they are automatically translated into uppercase by the system. You cannot use reserved words as identifiers for dictionary objects unless they are delimited. If you attempt to use a name longer than 128 characters, SQLException X0X11 is raised.

Derby defines keywords beyond those specified by the SQL-92 standard (see SQL reserved words).

Example

-- the view name is stored in the
-- system catalogs as ANIDENTIFIER
CREATE VIEW AnIdentifier (RECEIVED) AS VALUES 1
-- the view name is stored in the system
-- catalogs with case intact
CREATE VIEW "ACaseSensitiveIdentifier" (RECEIVED) AS VALUES 1

This section describes the rules for using SQL92Identifiers to represent the following dictionary objects.

Qualifying dictionary objects

Since some dictionary objects can be contained within other objects, you can qualify those dictionary object names. Each component is separated from the next by a period. An SQL92Identifier is "dot-separated." You qualify a dictionary object name in order to avoid ambiguity.

column-Name

In many places in the SQL syntax, you can represent the name of a column by qualifying it with a table-Name or correlation-Name.

In some situations, you cannot qualify a column-Name with a table-Name or a correlation-Name, but must use a Simple-column-Name instead. Those situations are:

•		creating a table (CREATE TABLE statement)
•		specifying updatable columns in a cursor
•		in a column's correlation name in a SELECT expression (see SelectExpression)
•		in a column's correlation name in a TableExpression (see TableExpression)

You cannot use correlation-Names for updatable columns; using correlation-Names in this way will cause an SQL exception. For example:

SELECT c11 AS col1, c12 AS col2, c13 FROM t1 FOR UPDATE of c11,c13

In this example, the correlation-Name col1 FOR c11 is not permitted because c11 is listed in the FOR UPDATE list of columns. You can use the correlation-Name FOR c12 because it is not in the FOR UPDATE list.

Syntax

[ { table-Name | correlation-Name } . ] SQL92Identifier

Example

-- C.Country is a column-Name qualified with a
-- correlation-Name.
SELECT C.Country
FROM APP.Countries C

correlation-Name

A correlation-Name is given to a table expression in a FROM clause as a new name or alias for that table. You do not qualify a correlation-Name with a schema-Name.

You cannot use correlation-Names for updatable columns; using correlation-Names in this way will cause an SQL exception. For example:

SELECT c11 AS col1, c12 AS col2, c13 FROM t1 FOR UPDATE of c11,c13

Syntax

SQL92Identifier

Example

-- C is a correlation-Name
SELECT C.NAME
FROM SAMP.STAFF C

new-table-Name

A new-table-Name represents a renamed table. You cannot qualify a new-table-Name with a schema-Name.

Syntax

SQL92Identifier

Example

-- FlightBooks is a new-table-Name that does not include a schema-Name
RENAME TABLE FLIGHTAVAILABILITY TO FLIGHTAVAILABLE

schemaName

A schemaName represents a schema. Schemas contain other dictionary objects, such as tables and indexes. Schemas provide a way to name a subset of tables and other dictionary objects within a database.

You can explicitly create or drop a schema. The default user schema is the APP schema (if no user name is specified at connection time). You cannot create objects in schemas starting with SYS.

Thus, you can qualify references to tables with the schema name. When a schemaName is not specified, the default schema name is implicitly inserted. System tables are placed in the SYS schema. You must qualify all references to system tables with the SYS schema identifier. For more information about system tables, see Derby system tables.

A schema is hierarchically the highest level of dictionary object, so you cannot qualify a schemaName.

Syntax

SQL92Identifier

Example

-- SAMP.EMPLOYEE is a table-Name qualified by a schemaName
SELECT COUNT(*) FROM SAMP.EMPLOYEE
-- You must qualify system catalog names with their schema, SYS
SELECT COUNT(*) FROM SYS.SysColumns

Simple-column-Name

A Simple-column-Name is used to represent a column when it cannot be qualified by a table-Name or correlation-Name. This is the case when the qualification is fixed, as it is in a column definition within a CREATE TABLE statement.

Syntax

SQL92Identifier

Example

-- country is a Simple-column-Name
CREATE TABLE CONTINENT (COUNTRY VARCHAR(26) NOT NULL PRIMARY KEY,
COUNTRY_ISO_CODE CHAR(2), REGION VARCHAR(26))

synonym-Name

A synonym-Name represents a synonym for a table or a view. You can qualify a synonym-Name with a schema-Name.

Syntax

[ schemaName. ] SQL92Identifier

table-Name

A table-Name represents a table. You can qualify a table-Name with a schemaName.

Syntax

[ schemaName. ] SQL92Identifier

Example

-- SAMP.PROJECT is a table-Name that includes a schemaName
SELECT COUNT(*) FROM SAMP.PROJECT

view-Name

A view-Name represents a table or a view. You can qualify a view-Name with a schema-Name.

Syntax

[ schemaName. ] SQL92Identifier

Example

-- This is a View qualified by a schema-Name
SELECT COUNT(*) FROM SAMP.EMP_RESUME

index-Name

An index-Name represents an index. Indexes live in schemas, so you can qualify their names with schema-Names. Indexes on system tables are in the SYS schema.

Syntax

[ schemaName . ] SQL92Identifier

Example

DROP INDEX APP.ORIGINDEX;
-- OrigIndex is an index-Name without a schema-Name 
CREATE INDEX ORIGINDEX ON FLIGHTS (ORIG_AIRPORT)

constraint-Name

You cannot qualify constraint-names.

Syntax

SQL92Identifier

Example

-- country_fk2 is a constraint name
CREATE TABLE DETAILED_MAPS (COUNTRY_ISO_CODE CHAR(2)
CONSTRAINT country_fk2 REFERENCES COUNTRIES)

cursor-Name

A cursor-Name refers to a cursor. No SQL language command exists to assign a name to a cursor. Instead, you use the JDBC API to assign names to cursors or to retrieve system-generated names. For more information, see the Derby Developer's Guide. If you assign a name to a cursor, you can refer to that name from within SQL statements.

You cannot qualify a cursor-Name.

Syntax

SQL92Identifier

Example

stmt.executeUpdate("UPDATE SAMP.STAFF SET COMM = " +
"COMM + 20 " + "WHERE CURRENT OF " + ResultSet.getCursorName());

TriggerName

A TriggerName refers to a trigger created by a user.

Syntax

[ schemaName . ] SQL92Identifier

Example

DROP TRIGGER TRIG1

AuthorizationIdentifier

User names within the Derby system are known as authorization identifiers. The authorization identifier represents the name of the user, if one has been provided in the connection request. The default schema for a user is equal to its authorization identifier. User names can be case-sensitive within the authentication system, but they are always case-insensitive within Derby's authorization system unless they are delimited. For more information, see the Derby Developer's Guide.

Syntax

SQL92Identifier

Example

CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(
    'derby.database.fullAccessUsers', 'Amber,FRED')

Statements

This section provides manual pages for both high-level language constructs and parts thereof. For example, the CREATE INDEX statement is a high-level statement that you can execute directly via the JDBC interface. This section also includes clauses, which are not high-level statements and which you cannot execute directly but only as part of a high-level statement. The ORDER BY and WHERE clauses are examples of this kind of clause. Finally, this section also includes some syntactically complex portions of statements called expressions, for example SelectExpression and TableSubquery. These clauses and expressions receive their own manual pages for ease of reference.

Unless it is explicitly stated otherwise, you can execute or prepare and then execute all the high-level statements, which are all marked with the word statement, via the interfaces provided by JDBC. This manual indicates whether an expression can be executed as a high-level statement.

The sections provide general information about statement use, and descriptions of the specific statements.

Interaction with the dependency system

Derby internally tracks the dependencies of prepared statements, which are SQL statements that are precompiled before being executed. Typically they are prepared (precompiled) once and executed multiple times.

Prepared statements depend on the dictionary objects and statements they reference. (Dictionary objects include tables, columns, constraints, indexes, views, and triggers.) Removing or modifying the dictionary objects or statements on which they depend invalidates them internally, which means that Derby will automatically try to recompile the statement when you execute it. If the statement fails to recompile, the execution request fails. However, if you take some action to restore the broken dependency (such as restoring the missing table), you can execute the same prepared statement, because Derby will recompile it automatically at the next execute request.

Statements depend on one another-an UPDATE WHERE CURRENT statement depends on the statement it references. Removing the statement on which it depends invalidates the UPDATE WHERE CURRENT statement.

In addition, prepared statements prevent execution of certain DDL statements if there are open results sets on them.

Manual pages for each statement detail what actions would invalidate that statement, if prepared.

Here is an example using the Derby tool ij:

ij> CREATE TABLE mytable (mycol INT);
0 rows inserted/updated/deleted
ij> INSERT INTO mytable VALUES (1), (2), (3);
3 rows inserted/updated/deleted
-- this example uses the ij command prepare,
-- which prepares a statement
ij> prepare p1 AS 'INSERT INTO MyTable VALUES (4)';
-- p1 depends on mytable;
ij> execute p1;
1 row inserted/updated/deleted
-- Derby  executes it without recompiling
ij> CREATE INDEX i1 ON mytable(mycol);
0 rows inserted/updated/deleted
-- p1 is temporarily invalidated because of new index
ij> execute p1;
1 row inserted/updated/deleted
-- Derby  automatically recompiles p1 and executes it
ij> DROP TABLE mytable;
0 rows inserted/updated/deleted
-- Derby  permits you to drop table
-- because result set of p1 is closed
-- however, the statement p1 is temporarily invalidated
ij> CREATE TABLE mytable (mycol INT);
0 rows inserted/updated/deleted
ij> INSERT INTO mytable VALUES (1), (2), (3);
3 rows inserted/updated/deleted
ij> execute p1;
1 row inserted/updated/deleted
-- Because p1 is invalid, Derby  tries to recompile it
-- before executing.
-- It is successful and executes.
ij> DROP TABLE mytable;
0 rows inserted/updated/deleted
-- statement p1 is now invalid,
-- and this time the attempt to recompile it
-- upon execution will fail
ij> execute p1;
ERROR 42X05: Table/View 'MYTABLE' does not exist.

ALTER TABLE statement

The ALTER TABLE statement allows you to:

•		add a column to a table
•		add a constraint to a table
•		drop a column from a table
•		drop an existing constraint from a table
•		increase the width of a VARCHAR, CHAR VARYING, and CHARACTER VARYING column
•		override row-level locking for the table (or drop the override)
•		change the increment value and start value of the identity column
•		change the nullability constraint for a column
•		change the default value for a column

Syntax

ALTER TABLE table-Name
{
    ADD COLUMN column-definition |
    ADD CONSTRAINT clause |
    DROP [ COLUMN ] column-name [ CASCADE | RESTRICT ]
    DROP { PRIMARY KEY | FOREIGN KEY constraint-name | UNIQUE 
	 constraint-name | CHECK constraint-name | CONSTRAINT constraint-name }
    ALTER [ COLUMN ] column-alteration |
    LOCKSIZE { ROW | TABLE }
}

column-definition

Simple-column-NameDataType
[ Column-level-constraint ]*
[ [ WITH ] DEFAULT DefaultConstantExpression ]

For details on DefaultConstantExpression, see Column default.

column-alteration

column-Name SET DATA TYPE VARCHAR(integer) |
column-name SET INCREMENT BY integer-constant |
column-name RESTART WITH integer-constant |
column-name [ NOT ] NULL |
column-name [ WITH ] DEFAULT default-value

In the column-alteration, SET INCREMENT BY integer-constant, specifies the interval between consecutive values of the identity column. The next value to be generated for the identity column will be determined from the last assigned value with the increment applied. The column must already be defined with the IDENTITY attribute.

RESTART WITH integer-constant specifies the next value to be generated for the identity column. RESTART WITH is useful for a table that has an identity column that was defined as GENERATED BY DEFAULT and that has a unique key defined on that identity column. Because GENERATED BY DEFAULT allows both manual inserts and system generated values, it is possible that manually inserted values can conflict with system generated values. To work around such conflicts, use the RESTART WITH syntax to specify the next value that will be generated for the identity column. Consider the following example, which involves a combination of automatically generated data and manually inserted data:

CREATE TABLE tauto(i INT GENERATED BY DEFAULT AS IDENTITY, k INT)
CREATE UNIQUE INDEX tautoInd ON tauto(i)
INSERT INTO tauto(k) values 1,2

The system will automatically generate values for the identity column. But now you need to manually insert some data into the identity column:

INSERT INTO tauto VALUES (3,3)
INSERT INTO tauto VALUES (4,4)
INSERT INTO tauto VALUES (5,5)

The identity column has used values 1 through 5 at this point. If you now want the system to generate a value, the system will generate a 3, which will result in a unique key exception because the value 3 has already been manually inserted. To compensate for the manual inserts, issue an ALTER TABLE statement for the identity column with RESTART WITH 6:

ALTER TABLE tauto ALTER COLUMN i RESTART WITH 6

ALTER TABLE