Why foreign key doesn't update?

Question

I insert data in the column TEMP1.aa (primary key in temp1), but it doesn't import into the TEMP2.cc column (foreign key in TEMP2).

Why?

I thought if insert data in primary key it automatic insert into foreign key! If this true that if we insert in a primary key, foreign key must be updated?

Answer 1

A foreign key does not update child references, it only ensures that the value stored in the column already exists in the parent table.

But MySQL supports cascading an update, by adding the ON UPDATE CASCADE to the foreign key constraint in the TEMP1 table. Otherwise, you need to consider using a trigger for more elaborate rules/requirements.

Reference:

• MySQL - Foreign Key Constraints documentation
• MySQL - Triggers documentation

Answer 2

Consider these tables

Fruits
ID   Name
1    Apple
2    Orange
3    Grapes

Sales
ID   Fruit   Amount
1    1       2.45
2    3       1.23
3    1       2.23
4    2       6.22

The foreign key on Sales(Fruit) to Fruits(ID) ensures that each Fruit value in Sales must match an ID from Fruits. Not, on to your question

I insert data in the column aa (primary key in temp1), but it doesn't import into the TMP2.cc column (foreign key in temp2).

So we insert a record in to Fruits (ID: 4, Name: Pear). You want to import it into Sales? How?

Read up on some basics about Foreign Keys

• http://en.wikipedia.org/wiki/Foreign_key
• http://www.1keydata.com/sql/sql-foreign-key.html
• http://dev.mysql.com/doc/refman/5.5/en/innodb-foreign-key-constraints.html

Answer 3

No, foreign keys do not work like that.

Foreign keys are used on to link two tables together via the key. On one table, you have the PRIMARY KEY and all the attributes relative to that entity.

On the other table, you have the FOREIGN KEY which tells the database "hey you, the user, you can only insert a value here if it also exists as a PRIMARY KEY in my reference table".

It is still up to you, the user, to choose if you want to INSERT a row in your foreign table or not.

---

If you need the foreign table to always have a row referencing to its correspondant in the primary table, chances are that you could convert that table to additional columns as they are attributes to the same entity. There are exceptions to this but going into detail will just add to your confusion.