0

I have 2 tables being:

'bin' and 'missedbin' ,missed bin contains a foreign key or the primay key 'binID' from bin. I have set the foreign key to cascade on update & delete.

However, when a value is inserted into bin the foreign key is not updated within the missedbin table and remains null. Have I done something incorrectly?

EDIT: missedbin table:

missedbin bin table:

bin

I have 2 insert statements running in asp:

cmd.CommandText = "insert into mydb1.bin values(null,'" + binType + "','" + binColour + "','" + personIDdata + "')";

cmd.CommandText = "insert into mydb1.missedbin values (null, '" + personIDdata + "','" + dateFound + "', null)";

Richard1996
  • 95
  • 2
  • 12

1 Answers1

1

The foreign key does not work that way. You have to provide the correct binID when inserting into missedBin (always). You can use LAST_INSERT_ID(). Only if you later change bin.binID, then the binID in missedBin will change as well

INSERT INTO bin () VALUES () ...
INSERT INTO missedbin (binID, ...) VALUES (LAST_INSERT_ID(), ...)
Sebastian
  • 416
  • 1
  • 6
  • 19
  • Ah I see, I'm not sure I understand. What is the point of foreign keys if they don't have the same values of primary keys always? – Richard1996 Apr 03 '17 at 14:54
  • Well, how should Mysql know which rows shall be connected? You always have to set the initial keys yourself. The foreign key can only support you, when you UPDATE or DELETE a row – Sebastian Apr 03 '17 at 15:12