Skip to Main Content

Support literals in foreign key definitions

mattknowlesFeb 13 2015 — edited Jan 11 2016

I would like to create a generic table for managing entity types in order to simplify the standard maintenance tasks like activating and deactivating them.

create table entity_types (

entity_type_id number(14) not null,

entity_type varchar2(30) not null,

entity_type_group varchar2(30) not null,

activation_date date not null,

deactivation_date date,

--

constraint entity_types_pk primary key (entity_type_id),

constraint entity_types_k01 unique (entity_type_id, entity_type_group), -- Does not enforce uniqueness, just allows foreign keys to reference and ensures the entity type is appropriate for the group.

constraint entity_types_u01 unique (entity_type, deactivation_date)

);

I would also like to be able to reference the appropriate entity types by group by specifying the entity type group directly in the foreign key definition as a literal.

create table some_entities (

some_entity_id number(14) not null,

entity_type_id number(14) not null,

...

--

constraint some_entity_pk primary key (some_entity_id),

constraint some_entity_f01 foreign key (entity_type_id, 'some_group')

references entity_types (entity_type_id, entity_type_group)

);

I can accomplish the same effect by using virtual columns, but that clutters the table - especially if there are a number of different types being used on the same entity.

Comments
Post Details
Added on Feb 13 2015
7 comments
496 views