1

I have 2 tables : tbl_usr_info which has a UID which is a primary key and auto-increment and tbl_login_info which has a LoginID (primary) and the UID along with some other information like timestamps etc.

I'm trying to reference UID from tbl_login_info with UID in tbl_usr_info by running this sql statement 

CONSTRAINT `uid-info/login` FOREIGN KEY (`UID`) REFERENCES `tbl_usr_info` (`UID`)

but I'm getting this error:

Cannot add or update a child row: a foreign key constraint fails (CONSTRAINT uid-info/login FOREIGN KEY (UID) REFERENCES tbl_usr_info (UID))

tbl_usr_info table

CREATE TABLE `tbl_usr_info` (
  `UID` int(50) NOT NULL AUTO_INCREMENT,
  `username` varchar(50) NOT NULL DEFAULT '',
  `password` varchar(50) NOT NULL DEFAULT '',
  `email` varchar(50) NOT NULL DEFAULT '',
  PRIMARY KEY (`UID`),
  UNIQUE KEY `username` (`username`),
  UNIQUE KEY `email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tbl_usr_login table

CREATE TABLE `tbl_usr_login` (
      `LoginID` int(11) NOT NULL AUTO_INCREMENT,
      `UID` int(50) NOT NULL,
      `ip_address` varchar(55) DEFAULT NULL,
      `device` varchar(100) DEFAULT NULL,
      `time_stamp` datetime DEFAULT NULL,
      PRIMARY KEY (`LoginID`)
    )ENGINE=InnoDB DEFAULT CHARSET=latin1;

Is it the order in which I'm referencing it that's wrong?

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
El Dj
  • 385
  • 1
  • 7
  • 22

2 Answers2

1

I tested your foreign key constraint and it works without error for me. But my tables were empty.

One of the most common types of failures for a foreign key constraint is that when you create the constraint, the child table contains some values that are not present in the parent table. The foreign key constraint cannot be satisfied in that case, so creation of the constraint fails.

You can check for unmatched UID values:

SELECT COUNT(*)
FROM tbl_usr_login AS l
LEFT OUTER JOIN tbl_usr_info AS i
  ON l.UID = i.UID
WHERE i.UID is NULL;

P.S.: This is tangential to your question, but I notice you're using INT(50). The argument to INT doesn't mean what you think it means. INT(50) does NOT mean you can store 50 digits. See my answer to Types in MySQL: BigInt(20) vs Int(20)

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • I had some UID which was `0` and there was no user with `UID = 0` in my tbl_usr_info. That was the issue. Thanks a lot – El Dj Nov 27 '17 at 18:23
0

To enable foreign key, child and parent column definitions must match along with some other conditions.

In your problem case, following steps should resolve it:

  1. create user table.
  2. create login info table.
  3. add index/key on the UID column in login table.
  4. now, add referential constraint on it.

Refer to:
Mysql: Using foreign key constraints:

https://dev.mysql.com/doc/refman/5.6/en/create-table-foreign-keys.html

Ravinder Reddy
  • 23,692
  • 6
  • 52
  • 82