0

These are the create statement for the two tables that i am facing the issue with: First Table:

 CREATE TABLE `hrm__companyteam` (
 `id_team` int(11) NOT NULL AUTO_INCREMENT,
 `id_department` int(11) NOT NULL,
 `team_name` varchar(255) NOT NULL DEFAULT '',
 `notes` mediumtext NOT NULL,
 PRIMARY KEY (`id_team`),
 KEY `id_company` (`id_department`),
 CONSTRAINT `hrm__companyTeam_ibfk_1` FOREIGN KEY (`id_department`) REFERENCES `hrm__companydepartment` (`id_department`) ON DELETE CASCADE,
 CONSTRAINT `hrm__companyteam_ibfk_1` FOREIGN KEY (`id_department`) REFERENCES `hrm__companydepartment` (`id_department`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8

Second Table

CREATE TABLE `hrm__companyjobtitle` (
 `id_job_title` int(11) NOT NULL AUTO_INCREMENT,
 `id_team` int(11) NOT NULL DEFAULT '0',
 `job_title_name` varchar(255) NOT NULL DEFAULT '',
 `notes` mediumtext NOT NULL,
 PRIMARY KEY (`id_job_title`),
 KEY `id_division` (`id_team`),
 CONSTRAINT `hrm__companyJobTitle_ibfk_1` FOREIGN KEY (`id_team`) REFERENCES `hrm__companyteam` (`id_team`) ON DELETE CASCADE,
 CONSTRAINT `hrm__companyjobtitle_ibfk_1` FOREIGN KEY (`id_team`) REFERENCES `hrm__companyteam` (`id_team`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8

When i try to execute the following query:

INSERT INTO `hr_db`.`hrm__companyjobtitle` (

`id_job_title` ,
`id_team` ,
`job_title_name` ,
`notes`
)
VALUES (
'1',  '1',  'IT',  ''
)

I get this error:

1452 - Cannot add or update a child row: a foreign key constraint fails (hr_db.hrm__companyjobtitle, CONSTRAINT hrm__companyJobTitle_ibfk_1 FOREIGN KEY (id_team) REFERENCES hrm__companyteam (id_team) ON DELETE CASCADE)

Please help... What am i doing wrong. Also please let me know if you need any further details.

Community
  • 1
  • 1
Hammad Anwer
  • 147
  • 2
  • 3
  • 13
  • 1
    the error says it right there , you need to insert data first in hrm__companyteam , and can then only insert data in hrm__companytitle as the constraint specifies that hrm__companytitle.id_team must have a matching value in hrm__companyteam.id_team – Satya Nov 27 '13 at 09:52
  • The Data is in there in the hrm_company team table already. I added 2 rows earlier. But still the same result – Hammad Anwer Nov 27 '13 at 09:56
  • show your table data please – Satya Nov 27 '13 at 10:00
  • INSERT INTO `hrm__companyteam` (`id_team`, `id_department`, `team_name`, `notes`) VALUES (1, 1, 'Sales', ''), (2, 2, 'Team no 1', ''); – Hammad Anwer Nov 27 '13 at 10:07
  • THis is the data i inserted which is in the table now – Hammad Anwer Nov 27 '13 at 10:08

1 Answers1

1

I used these statements : 

CREATE TABLE `hrm__companyteam` (
    `id_team` INT(11) NOT NULL AUTO_INCREMENT,
    `id_department` INT(11) NOT NULL,
    `team_name` VARCHAR(255) NOT NULL DEFAULT '',
    `notes` MEDIUMTEXT NOT NULL,
    PRIMARY KEY (`id_team`),
    INDEX `id_company` (`id_department`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=4;


CREATE TABLE `hrm__companyjobtitle` (
    `id_job_title` INT(11) NOT NULL AUTO_INCREMENT,
    `id_team` INT(11) NOT NULL DEFAULT '0',
    `job_title_name` VARCHAR(255) NOT NULL DEFAULT '',
    `notes` MEDIUMTEXT NOT NULL,
    PRIMARY KEY (`id_job_title`),
    INDEX `id_division` (`id_team`),
    CONSTRAINT `hrm__companyJobTitle_ibfk_1` FOREIGN KEY (`id_team`) REFERENCES `hrm__companyteam` (`id_team`) ON DELETE CASCADE
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=4;


INSERT INTO hrm__companyteam (id_team, id_department, team_name, notes) VALUES (1, 1, 'Sales', ''), (2, 2, 'Team no 1', '');

INSERT INTO hrm__companyjobtitle (id_job_title , id_team , job_title_name , notes ) VALUES ( '1', '1', 'IT', '' );

AND ALL of them ran successfully without any error

Satya
  • 8,693
  • 5
  • 34
  • 55
  • I am still getting the error. Can it be because of relation with another table like the table team has relation wit another table? – Hammad Anwer Nov 27 '13 at 11:04
  • yes it could be that your companydepartment table data does not has the data – Satya Nov 27 '13 at 11:09
  • Data is there in all the related tables but stil this error shows up – Hammad Anwer Nov 27 '13 at 11:13
  • Yeah all of it is realted should i send you the whole database query so you can check? – Hammad Anwer Nov 27 '13 at 11:25
  • send please I will check , send all 3 table create code , all 3 table insert code – Satya Nov 27 '13 at 11:26
  • Any luck with the above @satya? – Hammad Anwer Nov 27 '13 at 11:41
  • Yes I think I found the error. Your SQL Script had same constraint being added twice on the same column, hence the error. I am uploading the correct SQL script, running on my system, on dropbox and pasting the link in a short while. Here goes the link https://www.dropbox.com/s/52k6kmellt5rxgl/hr_db_2.sql – Satya Nov 27 '13 at 11:47
  • i'm getting this error while importing big database. is there any way to import without error? – romal tandel Jul 05 '18 at 07:06