0

i am relatively new in phpmysql.i need some help...

i am using wamp server in windows7.server version is 2.2.22 and php version is 5.3.13.Name of my database is db_attendance1. I have two table namely users and leave.In users table i have following column

empID(int)
username(varchar)
password(varchar)
mac(varchar)
ip(varchar)

and in leave table i have following column

lid(int)
empname(varchar)
username(varchar)
nod(int)
sdate(date)
edate(date)
reason(varchar)
action(varchar)

Now i want to use empID in leave table.how can i use it.i have to say that i already apply a code but its not working.Its giving the following error..

1005 - Can't create table 'db_attendance1.leave' (errno: 150) (Details...)

and in details its showing the following..

InnoDB Documentation

Supports transactions, row-level locking, and foreign keys

[ Variables | Buffer Pool | InnoDB Status ]

and the code

create table `leave`(
lid INT NOT NULL AUTO_INCREMENT,
empID INT,
empname VARCHAR(255) NOT NULL,
username VARCHAR(255) NOT NULL,
nod INT NOT NULL,
sdate DATE,
edate DATE,
reason VARCHAR(255) NOT NULL,
PRIMARY KEY(lid),
FOREIGN KEY(empID) REFERENCES users(empID)
);

so whats wrong...please help me..

Community
  • 1
  • 1
Tanvir
  • 61
  • 1
  • 1
  • 3
  • 1
    users > empID is PRIMARY KEY ? – JOE LEE Apr 22 '13 at 04:56
  • The type of the field in a foreign key must be the same as the type of the column they're referencing. Make sure that the properties of the two fields you are trying to link with a constraint are exactly the same. Often, the 'unsigned' property on an ID column will catch you out. – hpaknia Apr 22 '13 at 05:00
  • yes empID is the primary key of users.. – Tanvir Apr 22 '13 at 05:00

4 Answers4

0
create table users(

empID INT,
PRIMARY KEY(empID) /*<<< ADD THIS */
);
create table `leave`(
lid INT NOT NULL AUTO_INCREMENT,
empID INT,
empname VARCHAR(255) NOT NULL,
username VARCHAR(255) NOT NULL,
nod INT NOT NULL,
sdate DATE,
edate DATE,
reason VARCHAR(255) NOT NULL,
PRIMARY KEY(lid),
FOREIGN KEY(empID) REFERENCES users(empID)
);
JOE LEE
  • 1,058
  • 1
  • 6
  • 6
0

use INDEX(empID) before your foreign key statement

Amir
  • 4,089
  • 4
  • 16
  • 28
0

Try this to fix your error :

create table `leave`(
`lid` bigint(20) NOT NULL AUTO_INCREMENT,
`empID` INT(11),
`empname` VARCHAR(255) NOT NULL,
`username` VARCHAR(255) NOT NULL,
`nod` INT(11) NOT NULL,
`sdate` DATE,
`edate` DATE,
`reason` VARCHAR(255) NOT NULL,
PRIMARY KEY(`lid`),
FOREIGN KEY(`empID`) REFERENCES users(`empID`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=311 ;
Yoesoff
  • 368
  • 5
  • 11
0

errno: 150 is a foreign key constraint error. You can get more details by immediately issuing the query SHOW ENGINE INNODB STATUS on the same connection after your failed CREATE TABLE.

Things to watch out for:

  1. Does the parent table and attribute exist? (users.empid must exist FIRST)
  2. Do the parent and child attributes have the same exact datatype? (signed INT in this case)
  3. Is there an index (normal, unique, or primary key) on the attribute in both the parent and child tables?
Francis Avila
  • 31,233
  • 6
  • 58
  • 96