3

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.

5

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) 
) ;
  • I did not know of virtual computed columns. This appears to do exactly what I was looking for, and does it better than the "this works" approach in my original question. Thanks. – Keith Davies Nov 10 '16 at 17:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.