Derby and standards

Derby adheres to SQL99 standards wherever possible. This section describes those features currently in Derby that are not standard; these features are currently being evaluated and might be removed in future releases.

This section describes those parts of Derby that are non-standard or not typical for a database system.

Derby uses a slightly different ALTER TABLE syntax for altering column defaults. While SQL99 uses DROP and SET, Derby uses DEFAULT.
Calling functions and procedures
Derby supports the CALL (procedure) statement for calling external procedures declared by the CREATE PROCEDURE statement. Built-in functions and user-defined functions declared with the CREATE FUNCTION command can be called as part of an SQL select statement or by using either a VALUES clause or VALUES expression.
CLOB and BLOB data types
Derby supports the standard CLOB and BLOB data types. BLOB and CLOB values are limited to a maximum of 2,147,483,647 characters.
Derby uses JDBC's result sets, and does not provide SQL for manipulating cursors except for positioned update and delete. Derby's scrollable insensitive cursors are provided through JDBC, not through SQL commands.
DECIMAL max precision
For Derby, the maximum precision for DECIMAL columns is 31 digits. SQL99 does not require a specific maximum precision for decimals, but most products have a maximum precision of 15-32 digits.
Dynamic SQL
Derby uses JDBC's Prepared Statement, and does not provide SQL commands for dynamic SQL.
Expressions on LONGs
Derby permits expressions on LONG VARCHAR; however, LONG VARCHAR data types are not allowed in the following clauses, operations, constraints, functions, and predicates:
  • GROUP BY clauses
  • ORDER BY clauses
  • JOIN operations
  • PRIMARY KEY constraints
  • Foreign KEY constraints
  • UNIQUE key constraints
  • MIN aggregate function
  • MAX aggregate function
  • [NOT] IN predicate
  • UNION, INTERSECT, and EXCEPT operators
SQL99 also places some restrictions on expressions on LONG types.
Information schema
Derby uses its own system catalog that can be accessed using standard JDBC DatabaseMetadata calls. Derby does not provide the standard Information Schema views.
NOT NULL characteristic
The SQL standard says NOT NULL is a constraint, and can be named and viewed in the information schema as such. Derby does not provide naming for NOT NULL, nor does it present it as a constraint in the information schema, only as a characteristic of the column.
Stored routines and PSM
Derby supports external procedures using the Java programming language. Procedures are managed using the CREATE PROCEDURE and DROP PROCEDURE statements.
All operations in Derby are transactional. Derby supports transaction control using JDBC 3.0 Connection methods. This includes support for savepoints and for the four JDBC transaction isolation levels. The only SQL command provided for transaction control is SET TRANSACTION ISOLATION.
Unique constraints and nulls
The SQL standard defines that unique constraints on nullable columns allow any number of nulls; Derby does not permit unique constraints on nullable columns.