0

I've almost completed my very complex project, then realized I had one issue still hanging me up. How do you (or is it even possible) to have the foreign key auto update (to reflect the parent) for each child table?

I guess I should mention the main site is built through Joomla. Everything I will be referring to in this post resides outside of Joomla but on the same domain, with the exception to the user profiles.

For instance... I have table dataCompany that stores the company information. This table will be a child table to the userProfile table. Now, userProfile table has an auto-incremented column (user_id) and I created a column by the same name in dataCompany (but not auto-incremented). I want the user_id in dataCompany to auto fill to match that from userProfile.

The user will be logged in when entering data into dataCompany, which I thought would make it easier to autofill the user_id foreign constraint. However, I still get the invalid error.

Perhaps I'm missing something, but I was under the impression foreign keys were the only way to hold separate tables together.

EDIT: I'm pretty sure I didn't explain what I'm doing very well.... so here's another try.

I have a website. The user will sign up for said website. That creates user_id in database. I have a form the user will fill out, and the information will be stored in dataCompany. This table has primary key of companyID but also has column user_id. I want the data the user inserts into dataCompany to be associated with the user data tied together by user_id. Now, when the user signs up the user_id is auto-incremented. I can't have the user putting in their user_id when filling out their company information (as they don't know what it is)... that's where the 'auto update foreign key' comes from. I just want it to replicate what is already in the parent table (userData).

Jawanaut
  • 11
  • 1
  • 5
  • http://stackoverflow.com/questions/1481476/when-to-use-on-update-cascade? as an Auto ID there should be no need to update the foreign key. However, if you must.... `on update cascade` I believe is the syntax you're looking for. – xQbert May 01 '17 at 19:59
  • "I still get the invalid error" - What is the exact error message? And what are you doing? There is no magic with foreign keys. You need to insert a value into the `user_id` column, that exists in the `userProfile` table. – Paul Spiegel May 01 '17 at 20:00
  • Okay... thanks. Well, since you can't do it with foreign keys, is there another way? I thought it would work like carrying the token through the browser. Everything entered when that user is logged in, should go to that user's db right? – Jawanaut May 01 '17 at 20:28

0 Answers0