CREATE TABLE DOSENOTWORK (num int, created_by varchar(40) default user, create_date_string varchar(40) default char(current_date))
give an error as below - any idea why ?!??!
The rules for what is acceptable as a column default in Derby say that the only valid functions are datetime functions. The logic that enforces this can be seen in the "defaultTypeIsValid" method of the file:
./java/engine/org/apache/derby/impl/sql/compile/ColumnDefinitionNode.java
The Derby Reference Manual also states this same restriction (albeit rather briefly):
----
Column Default
For the definition of a default value, a ConstantExpression is an expression that does not refer to any table. It can include constants, date-time special registers, current schemas, users, and null.
----
A "date-time special register" here means a date-time function such as "date(current_date)" in your first example. Since the function "char" is NOT a date-time function, it will throw an error.
I believe this restriction was put in place as part of the "DB2 compatibility" work was that done in Cloudscape a while back.
Hope that answers your question, Army
