My table has two columns called offer_date and availability_date

and I want to put a constraint that availability_date should

always be earlier than offer date and if values are inserted into

the table it should return DBMS_server_output:

"offer_date cannot be earlier than availability_date"

Name Null? Type
PROPERTY_ID VARCHAR2(10)
TYPE VARCHAR2(10)
ASKING_PRICE NUMBER(10,2)
SELLING_PRICE NUMBER(10,2)
OFFER_DATE DATE
AVAILABILITY_DATE DATE

You will most likely have to implement using a TRIGGER since you have a specific message you want to display. However, since this is a non-dbms specific area, and not all DBMSs will allow you to implement a trigger, I will not recommend any specific trigger syntax.

An alternative could be to store the difference of the two instead of the availability_date (possibly with a view on top of this, returning the two dates).
Then you could use a simple check constraint ( >= 0) on that difference.

I'm 99.9% certain you are using Oracle, right?

You can create a check constraint like this:
However, that will not give you the specific error message you mentioned, it will give you:

ORA-02290: check constraint (MYSCHEMA.DATES_CHK) violated

It is easy to trap that error message in an application, see what constraint was violated, and present a better message. I would not advocate using a trigger instead merely to allow a bespoke message. (Aside: it would be nice if Oracle allowed you to define bespoke error messages for each constraint, wouldn't it!)

I note you said this: "it should return DBMS_server_output [the message]". I presume you mean DBMS_OUTPUT.PUT_LINE? If so, that is wrong: never use DBMS_OUTPUT to handle error messages, it is only suitable for simple debugging etc. If you were writing a trigger you should call raise_application_error like this: