Foreign key on partial primary key

Question

This is implemented in Oracle, if it matters.

I understand how to build foreign keys to tables with composite primary keys. Is it possible to have a foreign key reference a table with a composite primary key with a static value for one of the key values?

Imagine I want to have a table of status codes. I could easily have a table of status codes for each purpose (i.e. one for ThingStatus, one for OtherThingStatus, etc.). Trivial, but leads to lots of small tables. I could also have a single table with all the status codes, with each record also identifying what the status code applies to (shown below).

create table StatusCode (
  StatusTable  varchar2(20) not null,
  StatusValue  varchar2(20) not null
);

alter table StatusCode add constraint pkStatusCode primary key (StatusTable, StatusValue);

create table Thing (
  ThingId integer not null, -- primary key
  TableName varchar2(20) default 'Thing',
  ThingStatus varchar2(20) not null
);

alter table Thing add constraint fkThingStatus
  foreign key (TableName, ThingStatus)
  references StatusCode(StatusTable, StatusValue);

Pretty straightforward, easy to implement, annoying to have a field (present in every record) that exists solely to satisfy the foreign key definition.

Is there a way to do something like

create table NewThing (
  NewThingId integer not null, -- primary key
  NewThingStatus varchar2(20) not null
);

alter table add constraint fkNewThingStatus
  foreign key ('NewThing', NewThingStatus)
  references StatusCode(StatusTable, StatusValue);

I could also just treat StatusCode as a look up table and not implement the foreign key reference explicitly, but I'd rather see it explicitly implemented.

Answer 1

From version 11g, you can use a virtual computed column:

create table StatusCode (
  StatusTable  varchar2(20) not null,
  StatusValue  varchar2(20) not null,
  primary key (statustable, statusvalue)
) ;

create table Thing (
  ThingId integer not null primary key, 
  TableName varchar2(20) generated always as ('Thing') virtual,
  ThingStatus varchar2(20) not null,
  foreign key 
          (TableName, ThingStatus)
      references StatusCode 
          (StatusTable, StatusValue) 
) ;