CODEWITHSUNDEEP
mysqlsqlforeign-keysforeign-key-relationship
giograno
giograno

Reputation: 1809

Fail on foreign constraint key

I'm trying to create a foreign key between the field review_id in table Reviews and id in table Sentences. The structure of the tables is the following:

CREATE TABLE `Reviews` (
  `review_id` varchar(255) CHARACTER SET utf8 NOT NULL,
  `package_name` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
  `review_content` text CHARACTER SET utf8,
  `review_date` date DEFAULT NULL,
  `star_rating` int(11) DEFAULT NULL,
  `app_version_id` int(11) NOT NULL,
  PRIMARY KEY (`review_id`),
  KEY `app_version_id` (`app_version_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `Sentences` (
  `id` varchar(255) NOT NULL DEFAULT '',
  `review` text,
  `category` varchar(255) DEFAULT NULL,
  `topic` varchar(255) DEFAULT NULL,
  KEY `rev-id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

I'm facing with the following error and I don't understand what the problem could be.

ALTER TABLE `Reviews` ADD CONSTRAINT `rev-to-sents` FOREIGN KEY (`review_id`) REFERENCES `Sentences` (`id`)
Foreign key constraint fails for table `msr test db`.`#sql-75_d`:
,
  CONSTRAINT `rev-to-sents` FOREIGN KEY (`review_id`) REFERENCES `Sentences` (`id`)

Trying to add in child table, in index PRIMARY tuple: DATA TUPLE: 8 fields; 0: len 36; hex 37626532323335652d616663392d313165362d383935342d633462333031636466363237; asc 7be2235e-afc9-11e6-8954-c4b301cdf627;; ...

Upvotes: 0

Views: 63

Answers (2)

Chris Travers
Chris Travers

Reputation: 26464

The problem sounds like you are trying to add a foreign key with rows that don't match. If so your first task is to find the non-matching rows and determine the scope of the problem In other words you have cases where Reviewes.review_id does not match any value in Sentences.id

You need to find and fix your data first. This means:

SELECT review_id FROM Reviews
  LEFT JOIN Sentences ON review_id = Sentences.id
 WHERE Sentences.id IS NULL;

Since you have a NOT NULL constraint on Sentences.id, only when the join condition fails, will the id be null.

Upvotes: 2

Juanma Jusue
Juanma Jusue

Reputation: 1

Try this: 

ALTER TABLE Reviews
ADD CONSTRAINT rev-to-sents
ADD FOREIGN KEY (review_id)
REFERENCES Sentences (id)

Upvotes: 0

Related Questions