Adaptive Server Anywhere DEFAULT values are a lot more powerful than many people realize. Not only can you specify ordinary literal values like 1 and 'Hello', and special constants like CURRENT DATE, but you can use global variables like @@spid and even expressions like dateadd ( day, 15, getdate() ).

Not only that, but there are two special values that aren't just defaults: DEFAULT TIMESTAMP and DEFAULT LAST USER columns are automatically filled in whenever the row is updated as well as inserted, with the current date/time and user id respectively.

Here is an ISQL script which demonstrates the different kind of DEFAULT values:

// Connect as "dba" and insert the 1st row. CONNECT DBA IDENTIFIED BY SQL; DROP TABLE auto; CREATE TABLE auto ( autoincrement_key INTEGER NOT NULL DEFAULT AUTOINCREMENT, null_string VARCHAR ( 10 ) NULL DEFAULT NULL, simple_string VARCHAR ( 10 ) NOT NULL DEFAULT 'Hello', simple_number INTEGER NOT NULL DEFAULT 1, inserted_by_publisher VARCHAR ( 128 ) NULL DEFAULT CURRENT PUBLISHER, inserted_by_remote VARCHAR ( 128 ) NULL DEFAULT CURRENT REMOTE USER, inserted_by_user VARCHAR ( 128 ) NOT NULL DEFAULT CURRENT USER, inserted_by_user2 VARCHAR ( 128 ) NOT NULL DEFAULT USER, inserted_on_date DATE NOT NULL DEFAULT CURRENT DATE, inserted_at_time TIME NOT NULL DEFAULT CURRENT TIME, inserted_at_date_time TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP, updated_by_user VARCHAR ( 128 ) NOT NULL DEFAULT LAST USER, updated_at_date_time TIMESTAMP NOT NULL DEFAULT TIMESTAMP, complex_date_time TIMESTAMP NOT NULL DEFAULT (dateadd(day,15,getdate())), version_string VARCHAR ( 128 ) NOT NULL DEFAULT @@version, servername_string VARCHAR ( 128 ) NOT NULL DEFAULT @@servername, spid_number INTEGER NOT NULL DEFAULT @@spid, procid_number INTEGER NOT NULL DEFAULT @@procid, db_name_string VARCHAR ( 128 ) NOT NULL DEFAULT DB_NAME(), user_id_number INTEGER NOT NULL DEFAULT User_id(), user_name_string VARCHAR ( 128 ) NOT NULL DEFAULT User_name(), suser_id_number INTEGER NOT NULL DEFAULT Suser_id(), suser_name_string VARCHAR ( 128 ) NOT NULL DEFAULT Suser_name(), getdate_date_time TIMESTAMP NOT NULL DEFAULT getdate(), ordinary INTEGER NOT NULL, PRIMARY KEY ( autoincrement_key ) ); INSERT INTO auto ( ordinary ) VALUES ( 1 ); // Define "abc" as the publisher. REVOKE GROUP FROM dba; GRANT GROUP TO dba; REVOKE CONNECT FROM abc; GRANT CONNECT TO abc IDENTIFIED BY abc; GRANT PUBLISH TO abc; // Connect as "xyz" and insert the 2nd row. REVOKE CONNECT FROM xyz; GRANT CONNECT TO xyz IDENTIFIED BY xyz; GRANT DBA TO xyz; GRANT MEMBERSHIP IN GROUP DBA TO xyz; CONNECT xyz; INSERT INTO auto ( ordinary ) VALUES ( 1 ); // Connect as "pqr" and update the 2nd row. REVOKE CONNECT FROM pqr; GRANT CONNECT TO pqr IDENTIFIED BY pqr; GRANT DBA TO pqr; GRANT MEMBERSHIP IN GROUP DBA TO pqr; CONNECT pqr; UPDATE auto SET ordinary = ordinary + 1 WHERE autoincrement_key = 2;

1. DEFAULT AUTOINCREMENT

DEFAULT Value	Row 1	Row 2
AUTOINCREMENT	1	2

Autoincrement is cool: it works, it's reliable, it's well-described in the Help, and it only has two minor quirks. First of all, for performance reasons every autoincrement column should be the first column in a primary key or unique index. And second, if you insert, delete and then re-insert rows, gaps will appear in the numbering scheme... unless you shut down and restart the engine between the delete and re-insert.

2. DEFAULT NULL

DEFAULT Value	Row 1	Row 2
NULL	NULL	NULL

The NULL default is the default; it's what you get if don't specify DEFAULT at all.

3. DEFAULT 'Hello'

DEFAULT Value	Row 1	Row 2
'Hello'	'Hello'	'Hello'

A string literal: one of the simplest default values. Beware of front-end tools that don't understand special constants like LAST USER, however; sometimes they put your value inside quotes. For example, if you want DEFAULT LAST USER but you get DEFAULT 'LAST USER', that's wrong. 'LAST USER' a string literal, not a special constant.

4. DEFAULT 1

DEFAULT Value	Row 1	Row 2
1	1	1

A numeric literal: the other simple kind of default.

5. DEFAULT CURRENT PUBLISHER

DEFAULT Value	Row 1	Row 2
CURRENT PUBLISHER	'abc'	'abc'

If you use publish-subscribe replication, one single user id is identified as the "publisher" of each database. This user id is unique across all databases that participate in the replication scheme and can be used as a "database identifier".

Together with DEFAULT AUTOINCREMENT you can use DEFAULT CURRENT PUBLISHER to define a two-column primary key that is unique across all databases that are being replicated.

6. DEFAULT CURRENT REMOTE USER

DEFAULT Value	Row 1	Row 2
CURRENT REMOTE USER	NULL	NULL

The special constant DEFAULT CURRENT REMOTE USER is always NULL except:

• inside a RESOLVE UPDATE replication conflict trigger fired by DBREMOTE.EXE on the consolidated database in a publish-subscribe replication setup, and
  
  
• inside a SET OPTION REPLICATION_ERROR stored procedure when, for example, there is a primary key error.