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.