CODEWITHSUNDEEP
mysqlforeign-keys
GrumpyCrouton
GrumpyCrouton

Reputation: 8621

Foreign key Integrity constraint violation: 1452

I've recently started trying to use foreign keys to make database management easier on myself. I'm having a terrible time trying to figure out how they actually work, and most of the time I can get it working between tables without issue. But I'm currently having an issue with 2 of my tables, and I can't figure it out.

I'm getting an error:

SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (REDACTED.rc_logs, CONSTRAINT rc_logs_ibfk_1 FOREIGN KEY (user_id) REFERENCES rc_teammates (uid) ON DELETE CASCADE ON UPDATE CASCADE) [/home5/redacted/public_html/redacted/rc/public/assets/php/connection.php:25]

but my tables seem to be set up properly, and I'm really confused about why it's not working. Here is my table structures:

rc_teammates

CREATE TABLE `rc_teammates` (
  `uid` int(11) NOT NULL,
  `name` text NOT NULL,
  `primary_line` int(11) NOT NULL,
  `hireStatus` text NOT NULL,
  `created_on` date NOT NULL,
  `active` tinyint(1) NOT NULL DEFAULT '1'
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

ALTER TABLE `rc_teammates`
  ADD PRIMARY KEY (`uid`), ADD UNIQUE KEY `uid` (`uid`), ADD KEY `primary_line` (`primary_line`), ADD KEY `primary_line_2` (`primary_line`);

ALTER TABLE `rc_teammates`
  MODIFY `uid` int(11) NOT NULL AUTO_INCREMENT;

ALTER TABLE `rc_teammates`
ADD CONSTRAINT `rc_teammates_ibfk_1` FOREIGN KEY (`primary_line`) REFERENCES `rc_lines` (`uid`) ON DELETE CASCADE ON UPDATE CASCADE;

rc_logs

CREATE TABLE IF NOT EXISTS `rc_logs` (
  `uid` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `line` int(11) NOT NULL,
  `date` date NOT NULL,
  `type` varchar(15) NOT NULL,
  `timein` time NOT NULL,
  `timeout` time NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=53 DEFAULT CHARSET=latin1;

ALTER TABLE `rc_logs`
  ADD PRIMARY KEY (`uid`), ADD KEY `user_id` (`user_id`), ADD KEY `line` (`line`), ADD KEY `user_id_2` (`user_id`);

ALTER TABLE `rc_logs`
  MODIFY `uid` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=53;
ALTER TABLE `rc_logs`
ADD CONSTRAINT `rc_logs_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `rc_teammates` (`uid`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `rc_logs_ibfk_2` FOREIGN KEY (`line`) REFERENCES `rc_lines` (`uid`) ON DELETE CASCADE ON UPDATE CASCADE;

I've tried to look up the error, and I've had this issue before but I do not remember how I solved it. What's worse is, this was working earlier, until I emptied the rc_teammates table to start fresh.

I really cannot figure this out, and would love any pointers. Thanks!

Upvotes: 0

Views: 1395

Answers (1)

Thomas G
Thomas G

Reputation: 10226

As you said you have "emptied" (TRUNCATE?) the table rc_teammates. And you try to insert a record in rc_logs, and this record has a user_id that doesn't exists in rc_teammates, thus violation of the following constraint:

ADD CONSTRAINT `rc_logs_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `rc_teammates` (`uid`) ON DELETE CASCADE ON UPDATE CASCADE

Just add a record in rc_teammates, having a uid equal to the user_id of the record you are trying to insert in rc_logs, and retry.

Also, about this :

ALTER TABLE `rc_teammates`
  ADD PRIMARY KEY (`uid`), ADD UNIQUE KEY `uid` (`uid`),

ALTER TABLE `rc_teammates`
  MODIFY `uid` int(11) NOT NULL AUTO_INCREMENT;

When you set a column as PRIMARY KEY, it is de facto : UNIQUE, NOT NULL and INDEXED. You don't need to specify all this, PRIMARY KEY is enough. This is valid for your other table as well.

Upvotes: 1

Related Questions