Thread: Foreign Key Constraints
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
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? 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 > > > >
simple possible explanation: you have a parent table and child table. foreign key in parent table references values in child table. you can only insert values in the field in the parent table that exist in the child table. to delete values in the child table you must also remove all rows in the parent table that reference that value which is where your problem exisited i think. Steve > -----Original Message----- > From: joemono [mailto:montero7@msu.edu] > Sent: 28 June 2002 17:27 > To: pgsql-general@postgresql.org > Subject: Re: [GENERAL] Foreign Key Constraints > > > 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? > > 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 > >
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 #