Skip to Main Content
  • Questions
  • Add Foreign key based on a value in a column

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, asg.

Asked: October 13, 2016 - 2:25 pm UTC

Last updated: October 17, 2016 - 1:16 pm UTC

Version: Oracle 11g

Viewed 10K+ times! This question is

You Asked

Hi Tom

I have a table with 2 columns. If the value in column1 ='A', the value in column2 must refer the

Table 1:
due1 due2
1 saw
2 mil

Table 2:
at1 at2
35 element
45 method

Table 3:
atdue1 atdue2
S 2
P 35

I would like to have a foreign key constraint in Table 3 in such a way that,
in Table 3, if the value of column 'atdue1'='S' then column atdue2 must have a foreign key pointing the reference from column 'due1' of Table 1
in Table 3, if the value of column 'atdue1'='P' then column atdue2 must have a foreign key pointing the reference from column 'at1' of Table 2 .

Is this possible? Please assist.

and Chris said...

No. You can't have a conditional foreign key like this.

There are a few ways around this:

1. Create optional columns for T1 and T2 on T3 pointing to the relevant table. Ensure only one is set
2. Create a master table for T1 and T2. Point the FK of T3 to the master table
3. Create M:M tables, reversing the relationship

So how do they work? Let's see:

1. Optional Columns

This would work something like:

CREATE TABLE t1 (due1 int primary key, due2 varchar2(3));
    
INSERT INTO t1 VALUES (1, 'saw');
INSERT INTO t1 VALUES (2, 'mil');
 
CREATE TABLE t2 (at1 int primary key, at2 varchar2(7));
    
INSERT INTO t2 VALUES (35, 'element');
INSERT INTO t2 VALUES (45, 'method');

create table t3 (
  tp varchar2(1),
  t1_id int references t1 (due1),
  t2_id int references t2 (at1),
  constraint one_fk check (
    ( tp = 'S' and t1_id is not null and t2_id is null ) or
    ( tp = 'P' and t1_id is null and t2_id is not null )
  )
);

insert into t3 values ('S', null, 35);

SQL Error: ORA-02290: check constraint (CHRIS.ONE_FK) violated

insert into t3 values ('P', null, 35);
insert into t3 values ('S', 2, 35);

SQL Error: ORA-02290: check constraint (CHRIS.ONE_FK) violated

insert into t3 values ('P', 2, null);

SQL Error: ORA-02290: check constraint (CHRIS.ONE_FK) violated


2. Master table

drop table t3 purge;
drop table t1 purge;
drop table t2 purge;
drop table tmaster purge;

create table tmaster (
  id int primary key,
  tp varchar2(1) not null,
  unique (id, tp)
);

INSERT INTO tmaster VALUES (1, 'S');
INSERT INTO tmaster VALUES (2, 'S');
INSERT INTO tmaster VALUES (35, 'P');
INSERT INTO tmaster VALUES (45, 'P');

CREATE TABLE t1 (
  due1 int primary key, due2 varchar2(3), 
  tp varchar2(1) not null check (tp = 'S'),
  foreign key (due1, tp) references tmaster (id, tp)
);
    
INSERT INTO t1 VALUES (1, 'saw', 'S');
INSERT INTO t1 VALUES (2, 'mil', 'S');
 
CREATE TABLE t2 (at1 int primary key, at2 varchar2(7), 
  tp varchar2(1) not null check (tp = 'P'),
  foreign key (at1, tp) references tmaster (id, tp)
);
    
INSERT INTO t2 VALUES (35, 'element', 'P');
INSERT INTO t2 VALUES (45, 'method', 'P');

create table t3 (
  tmaster_id int references tmaster (id)
);

insert into t3 values (1);
insert into t3 values (35);


3. M:M Tables

drop table tmaster purge;
drop table t3 purge;
drop table t1 purge;
drop table t2 purge;

CREATE TABLE t1 (due1 int primary key, due2 varchar2(3));
    
INSERT INTO t1 VALUES (1, 'saw');
INSERT INTO t1 VALUES (2, 'mil');
 
CREATE TABLE t2 (at1 int primary key, at2 varchar2(7));
    
INSERT INTO t2 VALUES (35, 'element');
INSERT INTO t2 VALUES (45, 'method');

create table t3 (
  id int primary key,
  tp varchar2(1) not null,
  unique (id, tp)
);

insert into t3 values (1, 'S');
insert into t3 values (2, 'P');

create table t1t3 (
  t1_id int references t1 (due1),
  t3_id int primary key,
  tp    varchar2(1) check (tp = 'S'),
  foreign key (t3_id, tp) references t3 (id, tp)
);

create table t2t3 (
  t2_id int references t2 (at1),
  t3_id int primary key references t3 (id),
  tp    varchar2(1) check (tp = 'P'),
  foreign key (t3_id, tp) references t3 (id, tp)
);

insert into t1t3 values (35, 1, 'S');

SQL Error: ORA-02291: integrity constraint (CHRIS.SYS_C005387) violated - parent key not found

insert into t1t3 values (1, 1, 'S');

insert into t2t3 values (35, 2, 'P');

insert into t2t3 values (1, 2, 'P');

SQL Error: ORA-00001: unique constraint (CHRIS.SYS_C005390) violated


Personally I prefer the master table method (2). You'll need to weigh up the practicality of the different approaches yourself though.

If you want to know more about these, check out this presentation (slide 32):

http://www.slideshare.net/billkarwin/sql-antipatterns-strike-back

Or

http://stackoverflow.com/a/922341/1485955

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Forgotten something?

Thomas Brotherton, October 14, 2016 - 2:19 pm UTC

In your proposed master table solution, haven't you lost the referential integrity between master and t1 / t2?
Chris Saxon
October 14, 2016 - 3:19 pm UTC

T1 and T2 have foreign keys to tmaster?

CREATE TABLE t1 (
  due1 int primary key, due2 varchar2(3), 
  tp varchar2(1) not null check (tp = 'S'),
  foreign key (due1, tp) references tmaster (id, tp)
);
    
CREATE TABLE t2 (at1 int primary key, at2 varchar2(7), 
  tp varchar2(1) not null check (tp = 'P'),
  foreign key (at1, tp) references tmaster (id, tp)
);

Thank you!

A reader, October 18, 2016 - 10:39 am UTC

Thanks Chris for the providing the available options in detail.