Re: Foreign key constraints with constant values?
- From: Michael Moore <michaeljmoore@xxxxxxxxx>
- To: jkstill@xxxxxxxxx
- Date: Thu, 28 Oct 2010 11:12:28 -0700
I've seen several approaches to solving this problem. Each seems to have
it's pros and cons. One solution is to have a TYPE_TABLE and a CODE_TABLE
like:
CREATE TABLE TYPE_TABLE
(
TYPE_KEY NUMBER(10), -- primary key: sequence generated
TYPE_NAME VARCHAR2(150 BYTE), --unique
TYPE_DESC VARCHAR2(2000 BYTE)
);
CREATE TABLE CODE_TABLE
(
CODE_KEY NUMBER(10), -- primary key: sequence generated
TYPE_KEY_FK NUMBER(10), --foreign key to type_table.type_key
CODE_NAME VARCHAR2(150 BYTE), --unique
CODE_DESC VARCHAR2(2000 BYTE)
);
create table client(
id integer not null,
name varchar2(200),
CODE_KEY_FK integer not null
foreign key (CODE_KEY_FK) references CODE_TABLE(CODE_KEY )
);
Let the application logic find the proper code_key_fk base on hard_coded
code_name.
I.E
Select code_key from code_table where code_name = 'Preferred Client';
One advantage of this approach is that you only need a single GUI
application to maintain all TYPES and CODEs. One disadvantage is that proper
population of the CODE_KEY_FK depends on application logic.
An alternative approach is to have many little "code tables". This approach
can have an advantage as the functionality of the system grows over time and
certain type of codes take on attributes of their own.
For example, you might want to define one of the CLIENT_CODES as the
'default code'. 'default code' might only be applicable to CLIENT_CODES, but
not to ORDER_STATUS_CODES. If you have your CLIENT_CODES and
ORDER_STATUS_CODES in the same table and you add a new column 'default_code'
which applies only to CLIENT_CODES then developers need to 'know' when
default_code applies and when it doesn't. If you have multiple little
'special-purpose' code tables, you can add new code attribute columns only
to the specific code table where it is applicable. This allows the database
structure itself to enforce the intrinsic data relationships rather than
pushing the enforcement into the application layer.
My personal preference is always "redundant simplicity" over "unified
complexity".
Mike
On Tue, Oct 26, 2010 at 11:36 AM, Jared Still <jkstill@xxxxxxxxx> wrote:
> On Tue, Oct 26, 2010 at 11:10 AM, Toon Koppelaars <
> toon.koppelaars@xxxxxxxxxxx> wrote:
>
>>
>> I know, i know, you'll now say: "but I don't want to create a seperate
>> ID's - table per TYPE."
>>
> Why not? If you do, you can use the declarative FK to ensure data
>> integrity.
>> If you don't, SQL's support for declarative constraints won't be able to
>> help you out.
>>
>>
> Another of my favorite arguments for this:
>
> Changes to app behavior then become DML, rather than DDL.
>
> Jared Still
> Certifiable Oracle DBA and Part Time Perl Evangelist
> Oracle Blog: http://jkstill.blogspot.com
> Home Page: http://jaredstill.com
>
>
Other related posts:
