Updating a child table

[BettyGay]

Okay, here is my problem, I have a systems table with systems, category, type and designation fields about 500 records. The category, type and designation are just a number like 01, 02, 03. Then we have a category, type and designation table with addition fields holding descriptions and such about the specific categories, types,and designations. So there is a one to many relationship between the category, type and designation table to the systems table. I need to update the systems table but i am getting the following error:

INSERT statement conflicted with COLUMN FOREIGN KEY constraint

I just need a general idea of how to update a table or group of tables like this. Thank you.

 

[lespaul]

I would assume this message means that you are trying to insert a record that there is no matching foreign key in one of your other tables.

For example, if you have an order system and you try to insert a new order that contains a customer Id field and that customer id doesn't exist in the customer table, you would get an error like this.

Are you sure that all the information you are trying to insert exists in the proper tables?

Now, your title indicates you are trying to do some kind of update, but your SQL indicates that you are trying to insert a record, which is it?

Leslie

 

[BettyGay]

I am inserting multiple records. And you are correct that all of the information I am trying to insert does not exist in the proper tables. How do I check for that first?

 

[lespaul]

depends on how you are doing this...in code? a macro? where is the data coming from that you are trying to insert?

Leslie

 

[BettyGay]

Actually I am using coldfusion, but I often write my queries in access to get the logic and then work them into coldfusion. So if you tell me what you would have to do in Access then I can figure it out the rest.

 

[lespaul]

I would just run a query to make sure the data exists:

SELECT * FROM LookupTable where KEY = SomeValue

if it comes back empty then the keyvalue doesn't exist and needs to be inserted; or use a count:

SELECT COUNT(*) As Counter FROM LookupTable where KEY = SomeValue

Then check if the value of Counter is > 0 to decide whether you need to insert any values.

HTH

leslie

 

[PHV]

How do I check for that first?
Execute an "INSERT INTO ... WHERE NOT EXISTS ..." for all the Foreign Keys first.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[BettyGay]

Thank you Lespaul and PHV, I can take this and run with it.
Thanks again.