SQL constraint: two attributes, at least one foreign key match on same table

Question

I have a table of phone numbers owned by a company, and a table of phone call records. Every call record includes (non-null) source and destination numbers. I am given the integrity constraint that either the source number or the destination number, but not both, are allowed to be numbers that are not in the phone number table (because they are numbers not owned by this company). In other words, I need to ensure that at least one of them is a foreign key to the phone number table.

create table phonenumber (
    phonenum numeric(10,0) not null,
    primary key (phonenum)
);
create table call_record (
    URID varchar(20) not null,
    c_src numeric(10,0) not null,
    c_dst numeric(10,0) not null,
    primary key (URID)
);

The following sounds like what I want but isn't valid SQL:

constraint call_constraint check (
    foreign key (c_src) references phonenumber (phonenum) or
    foreign key (c_dst) references phonenumber (phonenum)
)

Is there a way to specify this in DDL? If not, how would I write a trigger to enforce this?

Answer 1

Edited: Here is another idea using DDL and not using trigger:

create table phonenumber (
    phonenum numeric(10,0) not null,
    primary key (phonenum)
);

Create a function to validate foreign key "by hand".

CREATE OR REPLACE FUNCTION call_check(p_src NUMBER, p_dst NUMBER) RETURN VARCHAR2 DETERMINISTIC IS
BEGIN
  FOR x IN (SELECT COUNT(*) c
              FROM (SELECT 1
                      FROM phonenumber
                     WHERE phonenum = p_src
                    UNION ALL
                    SELECT 1
                      FROM phonenumber
                     WHERE phonenum = p_dst)) LOOP
    IF x.c>=1 AND x.c <= 2 THEN
      RETURN 'OK';
    END IF;
  END LOOP;
  RETURN 'NOK';
END;

If you're on 11g and up, then add virtual column and add check on that column

--drop table call_record
create table call_record (
    URID varchar(20) not null,
    c_src numeric(10,0) not null,
    c_dst numeric(10,0) not null,
    call_check_col GENERATED ALWAYS AS (call_check(c_src, c_dst)),
    primary key (URID)
);

ALTER TABLE call_record ADD CONSTRAINT call_check_con CHECK (call_check_col='OK');

Let's test

SQL>     INSERT INTO phonenumber VALUES ('123');
1 row inserted
SQL>     INSERT INTO call_record (urid, c_src, c_dst) VALUES ('C1', '123', '321');
1 row inserted
SQL>     INSERT INTO call_record (urid, c_src, c_dst) VALUES ('C3', '123', '123');
1 row inserted
SQL>     INSERT INTO call_record (urid, c_src, c_dst) VALUES ('C2', '321', '321');
INSERT INTO call_record (urid, c_src, c_dst) VALUES ('C2', '321', '321')
ORA-02290: check constraint (TST.CALL_CHECK_CON) violated