Specifies that the column value cannot be NULL. If NOT NULL is not specified, the column may contain NULL values. On INSERT, a non-null value must be supplied, or a DEFAULT must be specified. On UPDATE, the column cannot be set to the null value. If NOT NULL WITH DEFAULT is specified as a constraint with DATE, TIME, or TIMESTAMP, the default value is the current date, current time, or current timestamp. You must specify NOT NULL to use the column-level UNIQUE constraint. If you specify NOT NULL, you cannot specify NULL as the column default.
Specifying the column-level UNIQUE constraint after an individual column name indicates that the value for the column is to be unique for each row of the table.
You must specify NOT NULL to use the UNIQUE column-level constraint.
The UNIQUE column-level constraint restricts update or insertion of a row if it contains a column value which has been previously assigned. For example, if COLUMN_A has been assigned the value '75252' for one row in the table, you cannot insert a row or update another row to assign that same value to the column.
You can specify the column-level UNIQUE on a maximum 50 individual columns per table. The column length must not exceed 180 bytes if you specify this constraint on the column.
When you use the column-level UNIQUE constraint, CA Datacom Datadictionary generates a KEY entity-occurrence with the UNIQUE attribute to enforce uniqueness. This key may not be deleted directly. You must DROP the UNIQUE constraint to delete the key. The CA Datacom Datadictionary name for the key is the same as the column. The 5-character CA Datacom/DB name of the key is SQnnn, where nnn is a sequential number unique to each database.
Same as UNIQUE, except for the following:
This is a CA Datacom/DB extension. Specifying WITH DEFAULT means that if you do not specify a value, blanks is used if the data type is CHARACTER. If the data type is not CHARACTER, zero (0) is used of the appropriate type, length, and scale.
If NOT NULL WITH DEFAULT is specified as a constraint with DATE, TIME, or TIMESTAMP, the default value is the current date, current time, or current timestamp.
You must specify NOT NULL to use the WITH DEFAULT column-level constraint.
Do not use WITH DEFAULT if you specify a DEFAULT in the column definition.
Specify the name of the table you want to reference. The table-name must identify a table described in the CA Datacom Datadictionary other than a CA Datacom Datadictionary table.
If you specify a ref-col-name, the data type, length, and scale of the column-name specified in the column definition (see Column Definition) must be identical to those of the ref-col-name.
When no ref-col-name is specified, the table-name must have a PRIMARY KEY, and its column-name is the column-name specified in the column definition.
The referenced column must be a UNIQUE or PRIMARY KEY in the referenced table. If the referenced column is not a UNIQUE or PRIMARY KEY, CA Datacom/DB issues a -169 SQL return code.
Specifies the referential action to be taken when a row in the referenced table is deleted or updated.
Allows you to check domain constraints specified in the search condition.
The search condition must be enclosed in parentheses.
When you check a constraint through the CREATE TABLE or ALTER TABLE statements, do not include a function, special register, host variable, subquery, or external table reference in the search condition. If you do, CA Datacom/DB issues an error message informing you that none of those are allowed.
For more information on the search condition, see Search Conditions.
Allows you to specify a constraint-name. Constraint names must be unique within all constraints defined in the same schema. A fully qualified constraint name contains the schema ID, that is to say, the authorization ID or creator ID, and a 32-byte name.
If the name you specify already exists, CA Datacom/DB issues an error message. Query the SYSCONSTRSRC table (see Schema Information Tables (SIT)) to see which names are already in use.
If you do not specify a constraint-name, the system generates a name in the form CONSTRAINT_nnnn where nnnn is 0001 to 9999. For example, the first constraint-name generated by the system would be CONSTRAINT_0001, the second would be CONSTRAINT_0002, and so on.
Constraint names are returned in the SQLCA Error Message to indicate which of possibly several constraints were violated, and in the ALTER TABLE DROP statement to specify which constraint is to be dropped.
|
Copyright © 2014 CA.
All rights reserved.
|
|