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.
- ALTER TABLE syntax
- 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.
- Cursors
- 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.
- Transactions
- 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.