creating table with sysdate in check

[krzys]

Hi!
I tired to create table with sysdate check on one of the fields for
instance:

create table aa (
field_1 char(20) not null,
field_2 date check (field_2 <= sysdate);

but I had the message: ORA-02436

Explanation in doc is
ORA-02436 date or system variable wrongly specified in CHECK constraint
Cause: An attempt was made to use a date constant or system variable, such
as
USER, in a check constraint that was not completely specified in a CREATE
TABLE or ALTER TABLE statement. For example, a date was specified without
the century.
Action: Completely specify the date constant or system variable.

Question: How should I specify variable to use it in CHECK statement?

Thanks in advance.

[Michel Cadot]

You can't use SYSDATE in a check constraint but you can
use a trigger, something like:

create trigger bi_aa before insert or update on aa
for each row
begin
if ( :new.field_2 > sysdate ) then
raise_application_error (-20000, 'Wrong date in field_2');
end if;
end;
/

--
Have a nice day
Michel

krzys <kka...@ae.katowice.pl> a écrit dans le message : 905heu$ctk$2...@uranos.cto.us.edu.pl...