Constraint Type
|
Action
|
---|---|
CHECK
|
Ensures that a specific
set or range of values are the only values in a column. It can also
check the validity of a value in one column based on a value in another
column within the same row.
|
NOT NULL
|
Guarantees that a column
has nonmissing values in each row.
|
UNIQUE
|
Enforces uniqueness
for the values of a column.
|
PRIMARY KEY
|
Uniquely defines a row
within a table, which can be a single column or a set of columns.
A table can have only one PRIMARY KEY. The PRIMARY KEY includes the
attributes of the constraints NOT NULL and UNIQUE.
|
FOREIGN KEY
|
Links one or more rows
in a table to a specific row in another table by matching a column
or set of columns (a FOREIGN KEY) in one table with the PRIMARY KEY
in another table. This parent/child relationship limits modifications
made to both PRIMARY KEY and FOREIGN KEY constraints. The only acceptable
values for a FOREIGN KEY are values of the PRIMARY KEY or missing
values.
|
General form, column-constraint
in a column-specification:
column-definition <column-constraint-1<,
... column-constraint-n>>
<MESSAGE='message-string' <MSGTYPE=message-type>>
column-constraint
is one of the following:
CHECK (expression)
specifies that all
rows in the table (which is specified in the CREATE TABLE statement)
satisfy the expression, which
can be any valid where-expression.
DISTINCT
specifies that the
values of the column must be unique within the table. This constraint
is identical to UNIQUE.
NOT NULL
specifies that the
column does not contain a null or missing value, including special
missing values.
PRIMARY KEY
specifies that the
column is a PRIMARY KEY column, that is, a column that does not contain
missing values and whose values are unique.
REFERENCES table-name <ON
DELETE referential-action> <ON UPDATE referential-action>
specifies that the
column is a FOREIGN KEY, that is, a column whose values are linked
to the values of the PRIMARY KEY column in another table (the table-name that
is specified for REFERENCES). The referential-actions are
performed when the values of a PRIMARY KEY column that is referenced
by the FOREIGN KEY are updated or deleted. The referential-action specifies
the type of action to be performed on all matching FOREIGN KEY values
and is one of the following:
CASCADE
allows PRIMARY KEY
data values to be updated, and updates matching values in the FOREIGN
KEY to the same values.
Note: This referential action is
currently supported for updates only.
RESTRICT
occurs only if there
are matching FOREIGN KEY values. This referential action is the default.
SET NULL
sets all matching FOREIGN
KEY values to NULL.
UNIQUE
specifies that the
values of the column must be unique within the table. This constraint
is identical to DISTINCT.
|
Constraint Type
|
Default Name
|
---|---|
CHECK
|
_CKxxxx_
|
FOREIGN KEY
|
_FKxxxx_
|
NOT NULL
|
_NMxxxx_
|
PRIMARY KEY
|
_PKxxxx_
|
UNIQUE
|
_UNxxxx_
|
M
and F
.
proc sql; create table work.employees (ID char (5) primary key, Name char(10), Gender char(1) not null check(gender in ('M','F')), HDate date label='Hire Date'),
gender
in ('M','F').
NEWLINE | the text that is specified for MESSAGE= is displayed in addition to the default error message for that integrity constraint. |
USER | only the text that is specified for MESSAGE= is displayed. |
proc sql; create table work.discount3 (Destination char(3), BeginDate num Format=date9., EndDate num format=date9., Discount num, constraint ok_discount check (discount le .5), constraint notnull_dest not null(destination));
discount
le .5
to limit the values that can be added to the Discount
column.