Re: Foreign Key Constraints - Mailing list pgsql-general

joemono wrote:
>
> Actually, I think I figured it out.  I had altered the foreign key within
> config to it's current condition, but config_values still existed.  I guess
> there was some data stored somewhere that kept assuming (for config_values)
> that config tag was using just ONE column as its foreign key? Or maybe I
> have no idea what I'm talking about.  That's probably it. :)
>
> When I deleted, and then recreated config_values, and then also config, it
> worked.
>
> Anyone have any explanations?

Well,

you've setup a 2 column foreign key exactly as it should be to ensure
that the config table can only hold possible value combinations that
exist in config_values. What happened was that you cannot change
config_values as long as rows in config actually reference them.

If you specify ON UPDATE CASCADE, then you can change config_values and
referencing rows in config will automatically be updated as well. Would
that make sense to you?


Jan

>
> joemono
>
> "joemono" <montero7@msu.edu> wrote in message
> news:afhubr$2k5b$1@msunews.cl.msu.edu...
> > Hi,
> > I'm trying to understand foreign key constraints more, but having a heck
> of
> > a time doing so.  I've been looking through Google groups to try to find
> > answers to the problems I'm having, but I haven't come across any as of
> yet.
> >
> > Here is the situation:
> >
> > I have a table of configurations.  The config table has config_tag,
> > config_value columns.  I also have a table of config_values, ones that are
> > valid for the config table.  The config_values table has all the possible
> > configurations (only about 40 or so) that can be put into the config
> table.
> >
> > Currently, the config_values table has as its primary key (tag, value),
> and
> > the config table has as a foreign key (config_tag, config_value) which
> > references config_values (tag, value).  I've also messed around with match
> > full, but I'm not sure I understand it completely, and it hasn't solved
> the
> > problem I'm having yet.
> >
> > I'm adding some new options, and so I added rows to the config_values
> table,
> > with completely new tags, but with values that other tags also use. (I'm
> > expanding existing options to cover other areas of the project).  Now that
> > the rows are in the config_values table, I've decided to change them
> around,
> > and use different values, so I want to delete them. However, I keep
> getting:
> >
> > "fk_config referential integrity violation - key in config_values still
> > referenced from config"
> >
> > Like I said, I've been trying the match full option, because it only makes
> > sense to match the config_tag-config_value combination, since many of the
> > values have the same...value.  Right?
> >
> > Anyway, I hope this makes sense.  Any help is greatly appreciated!
> >
> > joemono
> >
> >
> >
> >
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #