Error in calling user defined function while adding check constraint in oracle

I want to add a check constraint that will make sure no one can enter more than one spouse in family table. I am trying to use a custom defined function for the same. I am doing some thing like this:

ALTER TABLE PMT_TRN_FAMILY
ADD CONSTRAINT CK_SPOUSE
CHECK (GETSPOUSE(M_CODE) = 'True');

Definition of Function GETSPOUSE is here:

CREATE OR REPLACE FUNCTION GETSPOUSE (
    P_M_CODE IN VARCHAR2
)
RETURN VARCHAR
IS Output VARCHAR2(5);
S_CNT NUMBER(2,0);
BEGIN
    SELECT COUNT(1) INTO S_CNT FROM PMT_TRN_FAMILY WHERE M_CODE = P_M_CODE AND RELATIONS='Spouse';
    IF S_CNT > 0 THEN
        return ('False');
    END IF;
    return ('True');
END;

Here M_code is the code of a candidate and Relations is column which stores type of relations.

Here I got to know that we cannot use user defined functions in check constraint, so is there any other way I can accomplish this in oracle?

Thanks in advance.

Solution

We can't use functions in check constraints, due to the internal architecture of the Oracle database: compiling a function and validating a constraint are separate concerns.

Maybe what you're looking for is a SQL assertion. Oracle doesn't support these (yet) but please read this answer in another thread which explains how you could implement a workaround using a materialized view.

However, it may be that all you need is a function-based index. I'm assuming that PMT_TRN_FAMILY implements some form of directed graph. So that M_CODE -> RELATIONS -> N_CODE can be used to represent MIKKI is SPOUSE of NIKKI or MO is FATHER of NOAH. You can enforce monogamy between spouses while still allowing people to have more than one kid like this:

create UNIQUE index only_one_spouse_fbi on  PMT_TRN_FAMILY
        (M_CODE
         , case when RELATIONS='Spouse' then M_CODE else N_CODE end)
/

Here is a SQL Fiddle demo.

If this doesn't solve your issue then please edit your question to include more details, such as table structures and some sample data.