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));
proc sql; insert into work.discount3 values('CDG','03MAR2000'd,'10MAR2000'd,.15) values('LHR','10MAR2000'd,'12MAR2000'd,.55);
ERROR: Add/Update failed for data set WORK.DISCOUNT3 because data value(s) do not comply with integrity constraint ok_discount. NOTE: This insert failed while attempting to add data from VALUES clause 2 to the data set. NOTE: Deleting the successful inserts before error noted above to restore table to a consistent state. |
UNDO_POLICY=Setting
|
Description
|
---|---|
REQUIRED
|
PROC SQL performs UNDO
processing for INSERT and UPDATE statements. If the UNDO operation
cannot be done reliably, PROC SQL does not execute the statement and
issues an ERROR message.
This is the PROC SQL
default.
|
NONE
|
PROC SQL skips records
that cannot be inserted or updated, and writes a warning message to
the SAS log similar to that written by PROC APPEND. Any data that
meets the integrity constraints is added or updated.
|
OPTIONAL
|
PROC SQL performs UNDO
processing if it can be done reliably. If the UNDO cannot be done
reliably, then no UNDO processing is attempted.
This action is a combination
of REQUIRED and NONE. If UNDO can be done reliably, then it is done,
and PROC SQL proceeds as if UNDO_POLICY=REQUIRED is in effect. Otherwise,
it proceeds as if UNDO_POLICY=NONE was specified.
|
proc sql undo_policy=none;
create table work.discount4
(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));
insert into work.discount4
values('CDG','03MAR2000'd,'10MAR2000'd,.15)
values('LHR','10MAR2000'd,'12MAR2000'd,.55);
WARNING: The SQL option UNDO_POLICY=REQUIRED is not in effect. If an error is detected when processing this INSERT statement, that error will not cause the entire statement to fail. ERROR: Add/Update failed for data set WORK.DISCOUNT4 because data value(s) do not comply with integrity constraint ok_discount. NOTE: This insert failed while attempting to add data from VALUES clause 2 to the data set. NOTE: 2 rows were inserted into WORK.DISCOUNT4 -- of these 1 row was rejected as an ERROR, leaving 1 row that was inserted successfully. |