the first table 'A' consists of refid(primary key), name, gender, age, status the second table 'b' consists of refid(primary key) and statename, when I write the query in table A to refer B it doesn't works, but it worked when I try it from the table B 1)why I am not able to access from table A?
2) I want to know what is the difference between referencing it from A and referencing it from B?
ALTER TABLE A
ADD FOREIGN KEY (refId)
REFERENCES B(refId)
// it doesn't work
ALTER TABLE B
ADD FOREIGN KEY (refId)
REFERENCES A(refId)
// it works
By "not working", I mean that I get an error from the foreign key:
#1452 - Cannot add or update a child row: a foreign key constraint fails
(testdrive.<result 2 when explaining filename '#sql-4b8_8d'>, CONSTRAINT
#sql-4b8_8d_ibfk_1 FOREIGN KEY (refid) REFERENCES reserve (refid))
You say that you're getting a
error when you have an FK from
A.refid
toB.refid
:Foreign keys are used to maintain referential integrity so if you have an FK from
A.refid
toB.refid
, then you can't insertrefid=X
intoA
unlessrefid=X
is already inB
. The error message is telling you that you're trying to insert (or update) arefid
value inA
that is not inB
, for example:If you reverse the FK to point from
B.refid
toA.refid
then the above INSERT sequence will work. Or, you could reverse the INSERTs:so that the foreign key will not be violated.