FK -> non PK - bad design?

From: OtisUsenet <otis_usenet_at_yahoo.com>
Date: 7 Apr 2003 21:55:19 -0700
Message-ID: <5606b639.0304072055.29001bfe@posting.google.com>

Hello,

I created 2 tables and was told that having a FK in one point to a non-PK colum in the other one is a bad relational model design. I was wondering if anyone could teach me why that is so. The tables in questions are simple and look like this:

CREATE TABLE user_info
(

    id                  SERIAL
                        CONSTRAINT pk_user_info_id PRIMARY KEY        
                         ,
    account_id          INTEGER
                        CONSTRAINT fk_user_info_account_id REFERENCES
account(id)               ,
    plan_type           INTEGER
                        CONSTRAINT fk_user_info_service_plan_type

REFERENCES service_plan(type)
...
);

CREATE TABLE service_plan
(

    id          SERIAL
                CONSTRAINT pk_service_plan_id PRIMARY KEY       ,
    type        INTEGER         NOT NULL UNIQUE                 

...
);

I was told that this design, where 'plan_type' FK in user_info table points to a column in service_plan that is _not_ a PK, is bad. Is it really?
Could somebody please explain why this is bad?

When I designed these two tables I was dwelling on whether I should point plan_type FK in user_info to service_plan's PK (the id column) or the non-PK type column. I decided that pointing to 'type' instead would be better, because 'type' will always contain a known value specified when the row is INSERTed.
Therefore, I reasoned, if I DELETE a row from service_plan, and INSERT a new row with the same value for 'type' back into service_plan, I will not break all rows in user_info that pointed to the deleted row in service_plan.
I realize that I should not be able to DELETE a row from service_plan while rows in user_info point to it.....but still, I found this design more intuitive at first....now I am no longer sure.

Thank you! Received on Mon Apr 07 2003 - 23:55:19 CDT