i want to create composite foreign key but it gives my sql error 1005

Question

Table complaint_record with composite primary key

CREATE TABLE `complaint_record` (
  `complaint_id` int(11) NOT NULL AUTO_INCREMENT,
  `cat_id` int(10) unsigned NOT NULL,
  `store_id` int(10) unsigned NOT NULL,
  `user_id` int(10) unsigned DEFAULT NULL,
  `complaint_data` varchar(300) NOT NULL,
  `status` varchar(45) DEFAULT NULL,
  `RCA` varchar(100) DEFAULT NULL,
  `priority` int(11) DEFAULT NULL,
  `assigned_to` varchar(45) DEFAULT NULL,
  `date_submission` datetime DEFAULT NULL,
  PRIMARY KEY (`complaint_id`,`store_id`,`cat_id`),
  KEY `cat_id` (`cat_id`),
  KEY `user_id` (`user_id`),
  KEY `store_id` (`user_id`),
  KEY `store_id_idx` (`store_id`),
  CONSTRAINT `cat_id` FOREIGN KEY (`cat_id`) REFERENCES `complaint_categories` (`cat_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `store_id` FOREIGN KEY (`store_id`) REFERENCES `store_record` (`store_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `user_id` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
)

Complaint_comment table in it I want to create composite foreign key

CREATE TABLE `complaint_comment` (
  `user_id` int(10) unsigned NOT NULL,
  `complaint_id` int(11) unsigned NOT NULL,
  `store_id` int(10) unsigned NOT NULL,
  `cat_id` int(10) unsigned NOT NULL,
  `commented_on` datetime NOT NULL,
  `comment` longtext NOT NULL,
  KEY `user_id_comment` (`user_id`),
  CONSTRAINT `user_id_comment` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
)

I am creating foreign key

ALTER TABLE `kabliwala_db`.`complaint_comment` ADD CONSTRAINT `complaint_key` FOREIGN KEY `complaint_key` (`complaint_id`, `store_id`, `cat_id`)
    REFERENCES `complaint_record` (`complaint_id`, `store_id`, `cat_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION;

Please add some more information to the question. Where are you getting the error, what have you tried etc. — Daerst, Feb 13 '15 at 10:05
Have you tried understanding the meaning of that error code? Also, you might want to spend some more effort in formatting your SQL. — Kai, Feb 13 '15 at 10:06
Welcome to StackOverflow: if you post code, XML or data samples, **please** highlight those lines in the text editor and click on the "code samples" button ( `{ }` ) on the editor toolbar to nicely format and syntax highlight it! — marc_s, Feb 13 '15 at 10:10
I think I've found an answer for you: http://stackoverflow.com/questions/17812616/mysql-error-1005-cant-create-table-errno-150 (wasn't too hard) — Kai, Feb 13 '15 at 10:13
error: Cannot find an index in the referenced table where the referenced columns appear as the first columns, or column types in the table and the referenced table do not match for constraint. Note that the internal storage type of ENUM and SET changed in tables created with >= InnoDB-4.1.12, and such columns in old tables cannot be referenced by such columns in new tables. — dhyanandra singh, Feb 13 '15 at 10:14
user714965: i tried but it is not working. through error Cannot find an index in the referenced table where the referenced columns appear as the first columns, or column types in the table and the referenced table do not match for constraint — dhyanandra singh, Feb 13 '15 at 10:22

dhyanandra singh · Accepted Answer · 2015-07-27T12:07:56.700

-1

in complaint_comment table complaint_id is unsigned and int but in complaint_record table complaint_id is only int

i.e type of both the fields are different. so can't create an foreign key with different type.

edited Jul 27 '15 at 12:07

answered Feb 13 '15 at 10:50

dhyanandra singh

1,071
2
18
38

i want to create composite foreign key but it gives my sql error 1005

1 Answers1