SQL - Add constraint check using object's member function

Question

I'm using Oracle 11g to create my SQL queries.

So far I have an object:

CREATE OR REPLACE TYPE property_type AS OBJECT (
    propertyNo                NUMBER,
    dateOfRegistration        DATE, [etc..]

  MEMBER FUNCTION       date_of_registration_is_valid RETURN NUMBER
);
/

Which compiles no problem and has the body:

CREATE OR REPLACE TYPE BODY property_type AS
  MEMBER FUNCTION date_of_registration_is_valid RETURN NUMBER IS
    is_valid        NUMBER;
    BEGIN
      IF (self.dateOfRegistration < SYSDATE) THEN
        is_valid := 1;
      ELSE
        is_valid := 0;
      END IF;

    RETURN is_valid;
  END;
END;
/

Which also compiles and works fine, then I get to my table:

CREATE TABLE property_table OF property_type (
  PRIMARY KEY           (propertyNo),
  CONSTRAINT            property_not_null
  CHECK                 (propertyNo IS NOT NULL),
  CONSTRAINT            property_reg_is_valid
  CHECK                 (property_object.date_of_registration_is_valid() = 1)
) NESTED TABLE relates_to STORE AS relates_to_table;
/

Which I expect to check the date_of_registration_is_valid() function to see if it returns the value 1 (true). I've tried all sorts of alternatives such as replacing the property_object value for self, leaving it blank etc, but I get an error. For the above it's the error:

Error report -
SQL Error: ORA-00904: "PROPERTY_OBJECT"."DATE_OF_REGISTRATION_IS_VALID": invalid identifier
00904. 00000 -  "%s: invalid identifier"
*Cause:    
*Action:

So how can I use a member function as a CHECK CONSTRAINT?

Answer 1

From the documentation, check constraints can't call user-defined functions, which presumably includes member functions.

You could get similar behaviour with a constructor function that raises an exception:

CREATE OR REPLACE TYPE property_type AS OBJECT (
    propertyNo                NUMBER,
    dateOfRegistration        DATE,
    -- [etc ]
    CONSTRUCTOR FUNCTION property_type (SELF IN OUT NOCOPY property_type,
        propertyNo NUMBER, dateOfRegistration DATE /* [etc] */ )
    RETURN SELF AS RESULT
);
/

CREATE OR REPLACE TYPE BODY property_type AS
    CONSTRUCTOR FUNCTION property_type (SELF IN OUT NOCOPY property_type,
        propertyNo NUMBER, dateOfRegistration DATE /* [etc */ )
    RETURN SELF AS RESULT IS
    BEGIN
        IF dateOfRegistration < SYSDATE THEN
            RAISE_APPLICATION_ERROR(-20001, 'Registration date before today');
        END IF;
        SELF.propertyNo := propertyNo;
        SELF.dateOfRegistration := dateOfRegistration;
        RETURN;
    END;
END;
/

Then without the member function check constraint (or the nested table as my simplified type doesn't have one), and without the explicit null check ans the primary key on the same column implies that anyway:

CREATE TABLE property_table OF property_type (
  PRIMARY KEY           (propertyNo)
);
/

Table property_table created.

A couple of trial inserts:

insert into property_table values (property_type(1, sysdate));

1 rows inserted.

insert into property_table values (property_type(2, sysdate - 1));

Error starting at line : 32 in command -
insert into property_table values (property_type(2, sysdate - 1))
Error report -
SQL Error: ORA-20001: Registration date before today
ORA-06512: at "STACKOVERFLOW.PROPERTY_TYPE", line 7

Depending on the data, you might want the validation to be against trunc(sysdate) so it's from midnight this morning, not the current time.