Java DB Reference Manual

Apache Software FoundationJava DB 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.

Relationship between Java(TM) DB and Derby

Java(TM) DB is a relational database management system that is based on the Java programming language and SQL. Java DB is a commercial release of the Apache Software Foundation's (ASF) open source relational database project. The Apache project is called Derby.

The Java DB product includes Derby without any modification whatsoever to the underlying source code.

Technical support is available for purchase for the Java DB product through Sun.

Because Java DB and Derby have the same functionality, the Java DB documentation refers to the core functionality as Derby.

Java DB Version 10.3 is based on the Derby Version 10.3 release. References to "Derby" in the Java DB documentation refer to the Version 10.3 release of Apache Derby.

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 Java DB.

Purpose of this document

This book, the Java DB 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 Java DB 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.
•		Two dashes (--) and a newline character delimit a comment, as per the SQL-92 standard. The two dashes start the comment and the newline character ends the comment.

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 Java DB 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 Java DB 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 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 { 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 {ConstantExpression | NULL } ]

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 does not affect any view that references the table being altered. This includes views that have an "*" in their SELECT list. You must drop and re-create those views if you wish them to return the new columns.