SQL constraint: two attributes, at least one foreign key corresponds to one table

Question

I have a table of phone numbers owned by the company and a table of phone call records. Each call record includes a (non-zero) source and destination number. I am assigned an integrity constraint that either the source number or the recipient number, but not both, can be numbers that are not listed in the phone number table (since they are numbers that do not belong to this company). In other words, I need to make sure that at least one of them is a foreign key in 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) ); 

It sounds like I want, but SQL is invalid:

 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 can I write a trigger to provide this?

Answer 1

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

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

Create a function to manually verify the foreign key.

 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 xc>=1 AND xc <= 2 THEN RETURN 'OK'; END IF; END LOOP; RETURN 'NOK'; END; 

If you use 11g and above add a virtual column and add validation for 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 the 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