Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2005
    Posts
    4

    Unanswered: check's or constraints?

    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

  2. #2
    Join Date
    Aug 2004
    Posts
    330
    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.

  3. #3
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    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.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    I'm 99.9% certain you are using Oracle, right?

    You can create a check constraint like this:
    Code:
    alter table x add constraint dates_chk check (offer_date >= availability_date);
    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:
    Code:
    raise_application_error(-20001,'offer_date cannot be earlier than availability_date');

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •