CODEWITHSUNDEEP
sqloracle-database
Danielesk
Danielesk

Reputation: 127

Future Dates SQL Developer

I'm trying to create a constraint that does not allow dates in future years. I have this: 

ALTER TABLE PACIENTE ADD CONSTRAINT ck_FechaNacimiento
CHECK (FechaNacimiento<=current_date);

But i'm getting error 02436.

Upvotes: 2

Views: 486

Answers (3)

Danielesk
Danielesk

Reputation: 127

I'd tried again with this and didn't show error, thanks by the way:

ALTER TABLE EXAMENPACIENTE ADD CONSTRAINT ExamenPaciente_FechaExamen_c1 CHECK (FechaExamen<='30-SEP-2013');

Upvotes: 0

Justin Cave
Justin Cave

Reputation: 231781

You cannot create a non-deterministic constraint. So you cannot create a constraint that references a function like current_date or sysdate that returns a different value every time you call it.

If you want to enforce this sort of thing, you'd need to create a trigger on the table that throws an error if the business rule is violated, i.e.

CREATE OR REPLACE TRIGGER trg_paciente
  BEFORE INSERT OR UPDATE 
  ON paciente
  FOR EACH ROW
BEGIN
  IF( :new.FechaNacimiento > current_date )
  THEN
    RAISE_APPLICATION_ERROR( -20001, 'FechaNacimiento<=current_date must be in the past' );
  END IF;
END;

Upvotes: 4

tilley31
tilley31

Reputation: 688

ALTER TABLE PACIENTE ADD CONSTRAINT ck_FechaNacimiento CHECK (FechaNacimiento<=SYSDATE);

Upvotes: -3

Related Questions