Breaking The Golden Rule: DEFAULT_TIMESTAMP_INCREMENT

The Golden Rule Of SQL Anywhere states "Watcom Does Things The Way They Should Be Done."

That rule has been so consistent over the years that any deviation comes as a shock, and therefore should be well publicized... like when the default page size was 2K instead of 4K once upon a time.

Here's another: Did you know that the DEFAULT_TIMESTAMP_INCREMENT database option does not affect columns defined with DEFAULT CURRENT TIMESTAMP, but only DEFAULT TIMESTAMP?

Why not?

Are DEFAULT CURRENT TIMESTAMP primary keys not worthy of uniqueness?

Does it make any sense to define a primary key with DEFAULT TIMESTAMP which implies the column is subject to update?

No, no sense at all... the DEFAULT_TIMESTAMP_INCREMENT option guarantees that default values assigned to a TIMESTAMP column will have ascending (and thus unique) values no matter how fast the INSERT statements are executed. Setting DEFAULT_TIMESTAMP_INCREMENT to '1' means each successive default value will be at least 0.000001 seconds later than the previous default value.

Great idea, right? Who can argue with that? If someone wants a repeating value they can bloody well provide it in the INSERT VALUES list, but I want my timestamp defaults to increment!

ALL my timestamp defaults, including DEFAULT CURRENT TIMESTAMP.

I thought it was a bug!

No, it's not a bug... it's even documented as a feature... and there was probably a "good reason" for not having DEFAULT_TIMESTAMP_INCREMENT work for DEFAULT CURRENT TIMESTAMP. Just like there once was a "good reason" for the 2K default page size... and a "good reason" for once not having row-level locking in Sybase SQL Server.

Need proof?

Here's proof that DEFAULT_TIMESTAMP_INCREMENT has no effect on DEFAULT CURRENT TIMESTAMP columns:

SET OPTION PUBLIC.DEFAULT_TIMESTAMP_INCREMENT = '1';
SET OPTION PUBLIC.TIMESTAMP_FORMAT = 'yyyy-mm-dd hh:nn:ss.ssssss';

CREATE TABLE t (
   a    TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
   b    INTEGER   NOT NULL );

BEGIN
INSERT t ( b ) VALUES ( 1 );
INSERT t ( b ) VALUES ( 2 );
INSERT t ( b ) VALUES ( 3 );
INSERT t ( b ) VALUES ( 4 );
INSERT t ( b ) VALUES ( 5 );
INSERT t ( b ) VALUES ( 6 );
INSERT t ( b ) VALUES ( 7 );
INSERT t ( b ) VALUES ( 8 );
INSERT t ( b ) VALUES ( 9 );
INSERT t ( b ) VALUES ( 10 );
COMMIT;
END;

SELECT * FROM t ORDER BY b;

a,b
'2012-12-20 09:41:58.717000',1
'2012-12-20 09:41:58.718000',2
'2012-12-20 09:41:58.718000',3
'2012-12-20 09:41:58.718000',4
'2012-12-20 09:41:58.718000',5
'2012-12-20 09:41:58.718000',6
'2012-12-20 09:41:58.718000',7
'2012-12-20 09:41:58.718000',8
'2012-12-20 09:41:58.719000',9
'2012-12-20 09:41:58.719000',10

Here's what happens if you try to make column "a" the PRIMARY KEY:

CREATE TABLE t (
   a    TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP PRIMARY KEY,
   b    INTEGER   NOT NULL );

BEGIN
INSERT t ( b ) VALUES ( 1 );
INSERT t ( b ) VALUES ( 2 );
INSERT t ( b ) VALUES ( 3 );
INSERT t ( b ) VALUES ( 4 );
INSERT t ( b ) VALUES ( 5 );
INSERT t ( b ) VALUES ( 6 );
INSERT t ( b ) VALUES ( 7 );
INSERT t ( b ) VALUES ( 8 );
INSERT t ( b ) VALUES ( 9 );
INSERT t ( b ) VALUES ( 10 );
COMMIT;
END;

Could not execute statement.
Primary key for table 't' is not unique: Primary key value ('2012-12-20 09:42:31.822000')
SQLCODE=-193, ODBC 3 State="23000"

So what?

By simply changing column "a" from DEFAULT CURRENT TIMESTAMP to DEFAULT TIMESTAMP you can make it work as the PRIMARY KEY because the DEFAULT_TIMESTAMP_INCREMENT option will now force all the values to be unique:

CREATE TABLE t (
   a    TIMESTAMP NOT NULL DEFAULT TIMESTAMP PRIMARY KEY,
   b    INTEGER   NOT NULL );

BEGIN
INSERT t ( b ) VALUES ( 1 );
INSERT t ( b ) VALUES ( 2 );
INSERT t ( b ) VALUES ( 3 );
INSERT t ( b ) VALUES ( 4 );
INSERT t ( b ) VALUES ( 5 );
INSERT t ( b ) VALUES ( 6 );
INSERT t ( b ) VALUES ( 7 );
INSERT t ( b ) VALUES ( 8 );
INSERT t ( b ) VALUES ( 9 );
INSERT t ( b ) VALUES ( 10 );
COMMIT;
END;

SELECT * FROM t ORDER BY b;

a,b
'2012-12-20 09:43:25.025000',1
'2012-12-20 09:43:25.038000',2
'2012-12-20 09:43:25.038001',3
'2012-12-20 09:43:25.038002',4
'2012-12-20 09:43:25.038003',5
'2012-12-20 09:43:25.038004',6
'2012-12-20 09:43:25.038005',7
'2012-12-20 09:43:25.038006',8
'2012-12-20 09:43:25.038007',9
'2012-12-20 09:43:25.039000',10

But wait!

DEFAULT TIMESTAMP columns exhibit a behavior you do not want in a primary key, at least you hardly ever want: When you UPDATE a row and don't explicitly SET a new value for the DEFAULT TIMESTAMP column, SQL Anywhere will set it for you.

DEFAULT TIMESTAMP is a "Magic Default" value: it works for UPDATE as well as INSERT. For all those "last_updated_datetime" columns, DEFAULT TIMESTAMP is a wonderful thing, you don't have SET it to CURRENT TIMESTAMP when you do an update, and you don't have to write an update trigger to keep it up to date, both of which are easy to forget.

But for a primary key? DEFAULT TIMESTAMP is a bad idea because changing a primary key value is (usually) a bad idea, especially behind-the-scenes (think MobiLink).

CREATE TABLE t (
   a    TIMESTAMP NOT NULL DEFAULT TIMESTAMP PRIMARY KEY,
   b    INTEGER   NOT NULL );

INSERT t ( b ) VALUES ( 1 );

SELECT 'before', * FROM t WHERE b = 1;

WAITFOR DELAY '00:00:10';

UPDATE t SET b = 1 WHERE b = 1;

SELECT 'after', * FROM t WHERE b = 1;

'before',a,b
'before','2012-12-20 09:43:48.269000',1

'after',a,b
'after','2012-12-20 09:43:58.435000',1

Product Suggestion: DEFAULT_CURRENT_TIMESTAMP_INCREMENT

This would work on DEFAULT CURRENT TIMESTAMP values the same way DEFAULT_TIMESTAMP_INCREMENT works on DEFAULT TIMESTAMP columns.

To preserve current behavior, the default setting for DEFAULT_CURRENT_TIMESTAMP_INCREMENT would be '0' instead of '1', where '0' means "do not increment".

Then, folks who wanted unique values assigned to TIMESTAMP primary key values on INSERT, but not changed on UPDATE, could use DEFAULT CURRENT TIMESTAMP plus this:

   SET OPTION PUBLIC.DEFAULT_CURRENT_TIMESTAMP_INCREMENT = '1';

A side effect of this would be when DEFAULT_CURRENT_TIMESTAMP_INCREMENT is set to something other than '0', all DEFAULT CURRENT TIMESTAMP columns would be included in the calculation of the @@DBTS global variable which returns the last unique generated timestamp values.

---