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?

Andris Krauze · Accepted Answer · 2016-04-05T10:05:50.837

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

Couldn't get back to test til now, but this one works. Thanks! I'm in a SQL course but virtual columns had not been discussed. Is this a common way, or an "ideal" way, of enforcing a constraint like this? I didn't have a strong preference for DDL over triggers but I was stumped either way. — Mike Embick, Apr 09 '16 at 21:48
To my mind it's not very common to have a need for a constraint like this, this might signal data model design issue. For anything else over triggers preference I could suggest reading "triggers are evil" google hits. As for virtual columns, there is actually more you can do with them, check Oracle Magazine 2008-March Tom Kyte's column for reference: http://www.oracle.com/technetwork/issue-archive/2008/08-mar/o28asktom-087592.html — Andris Krauze, Apr 11 '16 at 06:39

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

1 Answers1