Sybase SQL Anywhere - Column DEFAULT Clause

The DEFAULT clause allows you specifying the default value for a column. The default value is assigned if you omit the column or specify DEFAULT keyword in a INSERT statement.

Quick Example:

  -- Create a table 
   CREATE TABLE teams
   (
     id int,
     name VARCHAR(80) DEFAULT 'N/A'
   );
 
   -- Insert default value 'N/A' to name column   
   INSERT INTO teams(id) VALUES (1);
   INSERT INTO teams VALUES (2, DEFAULT);
 
   -- Insert default values to all columns  
   INSERT INTO teams DEFAULT VALUES;
 
   -- Insert NULL, not default (!) to name column
   INSERT INTO teams VALUES (2, NULL);

Summary information:

Syntax (full...)	DEFAULT default_value
String Literal
Numeric Value
Datetime Literal
Special Value		CURRENT TIMESTAMP, CURRENT USER etc.
Functions and Expressions
NULL Value	Specifying NULL explicitly in INSERT does not assign default

Last Update: Sybase SQL Anywhere 12.0

You can specify a string literal, numeric value or datetime literal as well as functions and expressions as the default value for a column:

   CREATE TABLE products
   ( 
      name VARCHAR(80) NOT NULL DEFAULT 'N/A',
      category VARCHAR(80) NULL DEFAULT 'Not Defined', 
      created TIMESTAMP DEFAULT CURRENT TIMESTAMP,
      expiry TIMESTAMP DEFAULT DATEADD(month, 1, CURRENT TIMESTAMP),
      price MONEY DEFAULT 0
   );

You can use DEFAULT VALUES keyword to insert default values to all columns:

  INSERT INTO products DEFAULT VALUES;

To insert the default value for a specified column, you can omit it or specify DEFAULT keyword in INSERT statement:

  -- Insert default values for category, created and expiry columns
  INSERT INTO products(name, price) VALUES ('Apple', 1.29);
  -- or
  INSERT INTO products VALUES ('Orange', DEFAULT, DEFAULT, DEFAULT, 1.39);

Specifying NULL values does not assign default values:

  -- Column name is defined as NOT NULL, so the insert fails
  INSERT INTO products (name) VALUES (NULL);
  -- Could not execute statement.
  -- Column 'name' in table 'products' cannot be NULL
  -- SQLCODE=-195, ODBC 3 State="23000"
 
  -- Column category allows NULLs, so NULL is inserted
  INSERT INTO products (category) VALUES (NULL);

Table content:

Both DEFAULT CURRENT TIMESTAMP and DEFAULT TIMESTAMP specify that the current date and time is used as the default value.

The difference is that DEFAULT CURRENT TIMESTAMP is set by INSERT statement only, while DEFAULT TIMESTAMP is set by INSERT and UPDATE statements:

   CREATE TABLE items 
   (
      name VARCHAR(90),
      added TIMESTAMP DEFAULT CURRENT TIMESTAMP,
      updated TIMESTAMP DEFAULT TIMESTAMP,
      price NUMERIC(7,2)
   );
 
   -- Insert a row with default values for added and updated columns
   INSERT INTO items(name, price) VALUES ('Apple', 1.29);

Table content:

Now let's update the price:

   -- Update the price
   UPDATE items SET price = price - 0.1 WHERE name = 'Apple';

Table content:

You can see that besides price column, the UPDATE statement also modified updated column, while added column remained the same.

Note that if there are multiple concurrent sessions updating the same column having DEFAULT TIMESTAMP, Sybase SQL Anywhere generates unique timestamp values even if updates was done at the same time.

To differentiate between two identical timestamp values, Sybase adds an increment defined by default_timestamp_increment option (1 microsecond, by default).

Sybase SQL Anywhere DEFAULT clause conversion to other databases:

denotes syntax or functional differences, or more strict restrictions

Oracle:

DEFAULT Clause	Datetime literals, functions and expressions conversion
	DEFAULT TIMESTAMP conversion requires is trigger
	DEFAULT VALUES is not supported. More...

• Reference and Articles

Sybase SQL Anywhere 12.0 Manual

• CREATE TABLE Statement