I created two tables try to create foreign key for the second in first, it doesn't work, but I am able to get when I create it from second why?

Question

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))

Answer 1

You say that you're getting a

Cannot add or update a child row: a foreign key constraint fails

error when you have an FK from A.refid to B.refid:

ALTER TABLE A
ADD FOREIGN KEY (refId)
REFERENCES B(refId)

Foreign keys are used to maintain referential integrity so if you have an FK from A.refid to B.refid, then you can't insert refid=X into A unless refid=X is already in B. The error message is telling you that you're trying to insert (or update) a refid value in A that is not in B, for example:

insert into A (refid) values (1);
insert into B (refid) values (1);

If you reverse the FK to point from B.refid to A.refid then the above INSERT sequence will work. Or, you could reverse the INSERTs:

insert into B (refid) values (1);
insert into A (refid) values (1);

so that the foreign key will not be violated.