2

I am relatively new in php and mysql.The problem that i am facing while i inserting value in my leave table.My leave table containing following column..

1.lid(INT primary key)

2.empname(varchar)

3.username(varchar)

4.nod(INT)

5.sdate(DATE)

6.edate(DATE)

7.reason(varchar)

8.action(varchar)

9.empID (INT FOREIGN KEY)

here empID is the foreign key references from users table. The problem that im facing while inserting values into the leave table.ERROR is given below

Cannot add or update a child row: a foreign key constraint fails (db_attendance1.leave, CONSTRAINT leave_ibfk_1 FOREIGN KEY (empID) REFERENCES users (empID))

here i just send the query and here it is..

    mysql_query("INSERT INTO `leave` 
           (`empname`, `username`,
            `nod`, `sdate`, `edate`,
            `reason`,`action`) 
            VALUES ('$empname', '$username', 
            '$nod', '$sdate', 
            '$edate', '$reason','');",
            $dbCon) or die(mysql_error());
Tanvir
  • 61
  • 1
  • 1
  • 3

3 Answers3

9

You can put

SET FOREIGN_KEY_CHECKS=0;

and run your query. Once you are done again set it back to 1 by

SET FOREIGN_KEY_CHECKS=1;
Pranav Singh
  • 17,079
  • 30
  • 77
  • 104
Digant
  • 398
  • 3
  • 10
  • This might work but could also result in some invalid data making it into your tables. Only turn off foreign keys when you're sure that the error is spurious. – Durandal Jan 28 '14 at 05:14
8

A foreign key constraint means that you one table doesn't accept inserts, updates or deletes that would 'break' the foreign key. This means, you can't update a EmpID if the new EmpID doesn't exist in the users. You can't add a new EmpID if it doesn't exist in the users table, etcetera. So to solve this issue, you need to make sure that the EmpID you're trying to add to table 'leave', first exists in table 'users'. Foreign keys can be a real powerful item, but can be a real pain too. Since the DB you're working on had foreign key constraints, I suggest you read on them a bit: http://en.wikipedia.org/wiki/Foreign_key

Borniet
  • 3,544
  • 4
  • 24
  • 33
0

Borniet, you helped me solve my similar problem.

@OP - All I had to do to fix this was create a corresponding row in the table so that the foreign key would exist. E.g. Table 1 has column Name Table 2 has column friends_name, a foreign key tied to Name in table 1. I got this error because I was trying to insert a row into table 2, where the friends_name referenced a non existing Name in table 1. So I created the name and we're off to the races :).

gabrielg
  • 19
  • 3