Oracle 5.0, Справочное руководство

Страница: 865 / 3232

Date and Time Types
845
CREATE TABLE t1 (
ts TIMESTAMP DEFAULT 0 ON UPDATE CURRENT_TIMESTAMP
);
• With an
ON UPDATE CURRENT_TIMESTAMP
clause but no
DEFAULT
clause, the column is
automatically updated to the current timestamp. The default is 0 unless the column is defined with
the
NULL
attribute, in which case the default is
NULL
.
CREATE TABLE t1 (
ts TIMESTAMP ON UPDATE CURRENT_TIMESTAMP -- default 0
);
CREATE TABLE t2 (
ts TIMESTAMP NULL ON UPDATE CURRENT_TIMESTAMP -- default NULL
);
It need not be the first
TIMESTAMP
column in a table that is automatically initialized or updated to the
current timestamp. However, to specify automatic initialization or updating for a different
TIMESTAMP
column, you must suppress the automatic properties for the first one. Then, for the other
TIMESTAMP
column, the rules for the
DEFAULT
and
ON UPDATE
clauses are the same as for the first
TIMESTAMP
column, except that if you omit both clauses, no automatic initialization or updating occurs.
To suppress automatic properties for the first
TIMESTAMP
column, do either of the following:
• Define the column with a
DEFAULT
clause that specifies a constant default value.
• Specify the
NULL
attribute. This also causes the column to permit
NULL
values, which means that
you cannot assign the current timestamp by setting the column to
NULL
. Assigning
NULL
sets the
column to
NULL
.
Consider these table definitions:
CREATE TABLE t1 (
ts1 TIMESTAMP DEFAULT 0,
ts2 TIMESTAMP DEFAULT CURRENT_TIMESTAMP
ON UPDATE CURRENT_TIMESTAMP);
CREATE TABLE t2 (
ts1 TIMESTAMP NULL,
ts2 TIMESTAMP DEFAULT CURRENT_TIMESTAMP
ON UPDATE CURRENT_TIMESTAMP);
CREATE TABLE t3 (
ts1 TIMESTAMP NULL DEFAULT 0,
ts2 TIMESTAMP DEFAULT CURRENT_TIMESTAMP
ON UPDATE CURRENT_TIMESTAMP);
The tables have these properties:
• In each table definition, the first
TIMESTAMP
column has no automatic initialization or updating.
• The tables differ in how the
ts1
column handles
NULL
values. For
t1
,
ts1
is
NOT NULL
and
assigning it a value of
NULL
sets it to the current timestamp. For
t2
and
t3
,
ts1
permits
NULL
and
assigning it a value of
NULL
sets it to
NULL
.
•
t2
and
t3
differ in the default value for
ts1
. For
t2
,
ts1
is defined to permit
NULL
, so the default
is also
NULL
in the absence of an explicit
DEFAULT
clause. For
t3
,
ts1
permits
NULL
but has an
explicit default of 0.
TIMESTAMP
Initialization and the
NULL
Attribute
By default,
TIMESTAMP
columns are
NOT NULL
, cannot contain
NULL
values, and assigning
NULL
assigns the current timestamp. To permit a
TIMESTAMP
column to contain
NULL
, explicitly declare
it with the
NULL
attribute. In this case, the default value also becomes
NULL
unless overridden with
a
DEFAULT
clause that specifies a different default value.
DEFAULT NULL
can be used to explicitly
specify
NULL
as the default value. (For a
TIMESTAMP
column not declared with the
NULL
attribute,
DEFAULT NULL
is illegal.) If a
TIMESTAMP
column permits
NULL
values, assigning
NULL
sets it to
NULL
, not to the current timestamp.