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

Question

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.

Actually I was on the verge of deleting that comment because I've expanded it into an actual answer :) — APC, Apr 12 '18 at 07:24

APC · Accepted Answer · 2018-04-12T08:05:34.240

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.

For your test data, if you do `insert into PMT_TRN_FAMILY values (1, 'Sibling', 7);` then you violate the unique index as there is a duplicate `M_CODE`, `N_CODE` pair even though neither is a `SPOUSE`. — MT0, Apr 12 '18 at 08:24
@mt0 - and that would be wrong because it is valid to be somebody's father and somebody's brother simultaneously? Hmmm, maybe in the mountains of Appalachia or the flats of Norfolk. ;-) But you're right, I made an assumption about the business rules which isn't in the text of the question. — APC, Apr 12 '18 at 09:21

MT0 · Answer 2 · 2018-04-12T08:34:00.283

Guessing at your table structure:

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE PEOPLE(
  M_CODE NUMBER(20,0) CONSTRAINT PEOPLE__M_CODE__PK PRIMARY KEY
);

CREATE TABLE PMT_TRN_FAMILY (
  M_CODE     NUMBER(20,0) CONSTRAINT FAMILY__M_CODE__FK
                            REFERENCES PEOPLE ( M_CODE ),
  RELATIONS  VARCHAR2(20),
  RELATED_TO NUMBER(20,0) CONSTRAINT FAMILY__RELATED_TO__FK
                            REFERENCES PEOPLE ( M_CODE )
);

Then we can modify it adding a unique index checking that for each RELATIONS='SPOUSE' there is only one M_CODE:

CREATE UNIQUE INDEX FAMILY__ONE_SPOUSE__U ON PMT_TRN_FAMILY (
  CASE RELATIONS WHEN 'SPOUSE' THEN M_CODE END
);

Inserting some test data:

INSERT INTO PEOPLE ( M_CODE )
  SELECT LEVEL FROM DUAL CONNECT BY LEVEL <= 7;

INSERT INTO PMT_TRN_FAMILY ( M_CODE, RELATIONS, RELATED_TO )
  SELECT 1, 'SPOUSE', 2 FROM DUAL UNION ALL
  SELECT 1, 'PARENT', 3 FROM DUAL UNION ALL
  SELECT 1, 'PARENT', 4 FROM DUAL UNION ALL
  SELECT 1, 'SIBLING', 4 FROM DUAL UNION ALL
  SELECT 1, 'CHILD',  5 FROM DUAL UNION ALL
  SELECT 1, 'CHILD',  6 FROM DUAL;

Query 1:

SELECT * FROM PMT_TRN_FAMILY

Results:

| M_CODE | RELATIONS | RELATED_TO |
|--------|-----------|------------|
|      1 |    SPOUSE |          2 |
|      1 |    PARENT |          3 |
|      1 |    PARENT |          4 |
|      1 |   SIBLING |          4 |
|      1 |     CHILD |          5 |
|      1 |     CHILD |          6 |

Then there are multiple parents/children (and even someone who has multiple non-spouse relationships) but only one spouse.

Query 2:

INSERT INTO PMT_TRN_FAMILY ( M_CODE, RELATIONS, RELATED_TO )
  VALUES ( 1, 'SPOUSE', 7 )

Results:

Trying to add a second spouse then you get an error of "one spouse violated":

ORA-00001: unique constraint (USER_4_2700A6.FAMILY__ONE_SPOUSE__U) violated

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

2 Answers2