System_Maintained Logical Keys

System_maintained logical key columns are assigned values by the DBMS Server, and cannot be assigned values by applications or end users. The following restrictions apply to logical keys specified as WITH SYSTEM_MAINTAINED:

• The only valid default clause is WITH DEFAULT. If the default clause is omitted, WITH DEFAULT is assumed.
• The only valid null clause for system_maintained logical keys is NOT NULL. If a column constraint or null clause is not specified, NOT NULL is assumed.
• No table constraint can include a system_maintained logical key column. For details about table constraints, see Column-Level Constraints and Table-Level Constraints.

  System_Maintained Clause	Null Clause	Valid?
  WITH SYSTEM_MAINTAINED	NOT NULL	Yes
  	WITH NULL	No
  	NOT NULLWITH DEFAULT	Yes
  	NOT NULL NOT DEFAULT	No
  	(none specified)	Yes
  NOT SYSTEM_MAINTAINED	NOT NULL	Yes
  	WITH NULL	Yes
  	NOT NULL WITH DEFAULT	Yes
  	NOT NULL NOT DEFAULT	Yes

Sequence Defaults

The default value for a column can be the next value in a sequence.

The Sequence-operator can be in either form NEXT VALUE FOR sequence or sequence.NEXTVAL, where sequence is a sequence name, optionally specified as owner.sequence.

For example:

create sequence lineitemsequence;

create table lineitem

           (itemid integer not null,

            itemseq integer not null with default next value for lineitemsequence);

insert into lineitem (itemid) values (4);

insert into lineitem values (8, next value for lineitemsequence);

insert into lineitem values (15, lineitemsequence.nextval);

insert into lineitem values (16, 23);

insert into lineitem (itemid) values (42);

Note: If the the schema of the sequence providing the default value is not specified, then it defaults to the schema (owner) of the table.

Sequence defaults are allowed on numeric columns only (integer, float, decimal). The column data type need not match the sequence data type exactly; sequence values are coerced to column values, if necessary.

If a row is inserted with some auto-incrementing columns defaulted, all relevant sequences are incremented once before the row is inserted, and the new sequence values are used whenever referenced in the row. This means that if two columns reference the same sequence for their default, they receive the same value, not two successive values.

Sequence defaulting is allowed in all contexts where column defaulting is allowed: INSERT, UPDATE (using SET COLUMN=DEFAULT), and COPY (including bulk copy).

Columns added or altered with the ALTER TABLE statement cannot use sequence defaults.

Defining a sequence default does not prevent you from explicitly assigning a value to the column upon insert or update. An explicitly assigned value may collide with a defaulted value from the sequence.