sql query errors foreign key constraints giving errors

Question

Here is SQL for studentfeedback database

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Database: `studentfeedback`
--

-- --------------------------------------------------------

--
-- Table structure for table `feedback`
--

CREATE TABLE IF NOT EXISTS `feedback` (
  `Fed_No` int(3) NOT NULL,
  `Roll_No` int(3) NOT NULL,
  `Sub_Name` varchar(100) NOT NULL,
  `Feedback` varchar(500) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `feedback`
--

INSERT INTO `feedback` (`Fed_No`, `Roll_No`, `Sub_Name`, `Feedback`) VALUES
(1, 1, 'DBMS', 'Subject is Good'),
(2, 1, 'English', 'Subject is Good'),
(3, 2, 'DBMS', 'Subject is Good'),
(4, 2, 'OS', 'Subject is Good'),
(5, 1, 'Algorithms', 'Hate, bruv.'),
(6, 1, 'TOC', 'Great Professor');

-- --------------------------------------------------------

--
-- Table structure for table `student`
--

CREATE TABLE IF NOT EXISTS `student` (
  `Roll_No` int(3) NOT NULL,
  `Name` varchar(100) NOT NULL,
  `Pass` varchar(100) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `student`
--

INSERT INTO `student` (`Roll_No`, `Name`, `Pass`) VALUES
(1, 'agnish', ''),
(2, 'ABC', '');

-- --------------------------------------------------------

--
-- Table structure for table `subject`
--

CREATE TABLE IF NOT EXISTS `subject` (
  `Sub_No` int(3) NOT NULL,
  `Sub_Name` varchar(100) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `subject`
--

INSERT INTO `subject` (`Sub_No`, `Sub_Name`) VALUES
(13, 'Linux'),
(7, 'DBMS'),
(5, 'English'),
(9, 'Political Science'),
(4, 'Hindi'),
(12, 'Networks'),
(11, 'Advanced DBMS'),
(6, 'Maths'),
(10, 'Electrical Engineering'),
(14, 'Algorithms'),
(8, 'Calculus');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `feedback`
--
ALTER TABLE `feedback`
  ADD PRIMARY KEY (`Fed_No`),
  ADD KEY `Roll_No` (`Roll_No`),
  ADD KEY `Sub_Name` (`Sub_Name`);

--
-- Indexes for table `student`
--
ALTER TABLE `student`
  ADD PRIMARY KEY (`Roll_No`);

--
-- Indexes for table `subject`
--
ALTER TABLE `subject`
  ADD PRIMARY KEY (`Sub_No`),
  ADD UNIQUE KEY `Sub_Name` (`Sub_Name`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `feedback`
--
ALTER TABLE `feedback`
  MODIFY `Fed_No` int(3) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=7;
--
-- AUTO_INCREMENT for table `student`
--
ALTER TABLE `student`
  MODIFY `Roll_No` int(3) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=3;
--
-- AUTO_INCREMENT for table `subject`
--
ALTER TABLE `subject`
  MODIFY `Sub_No` int(3) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=15;
--
-- Constraints for dumped tables
--

--
-- Constraints for table `feedback`
--
ALTER TABLE `feedback`
  ADD CONSTRAINT `feedback_ibfk_1` FOREIGN KEY (`Roll_No`) REFERENCES `student` (`Roll_No`),
  ADD CONSTRAINT `feedback_ibfk_2` FOREIGN KEY (`Sub_Name`) REFERENCES `subject` (`Sub_Name`);

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

I created a studentfeedback database and executed above code, I am getting error in following lines

SQL query:


--
-- Constraints for dumped tables
--

--
-- Constraints for table `feedback`
--
ALTER TABLE `feedback`
  ADD CONSTRAINT `feedback_ibfk_1` FOREIGN KEY (`Roll_No`) REFERENCES `student` (`Roll_No`),
  ADD CONSTRAINT `feedback_ibfk_2` FOREIGN KEY (`Sub_Name`) REFERENCES `subject` (`Sub_Name`)
MySQL said: Documentation

#1452 - Cannot add or update a child row: a foreign key constraint fails (`studentfeedback`.`#sql-460_70`, CONSTRAINT `feedback_ibfk_2` FOREIGN KEY (`Sub_Name`) REFERENCES `subject` (`Sub_Name`))

I am not able to understand this error why is this coming, what is wrong with the above implementation of MySQL statements,please let me know what or why is this error coming I am not able to understand what changes should I make.

how can you mark question duplicate some one else has a different database some one else has a different database the errors come due to mis understanding of code or logic. People learn new things and fall that is part of process. — ss321c, Oct 11 '18 at 04:09

score 1 · Accepted Answer · answered Oct 10 '18 at 20:29

1

You cannot add the foreign key feedback_ibfk_2 because the table feedback includes two subjects OS and TOC that don't really exist in the table subjects.

Fix the data and you'll be able to create the foreign, by creating those two rows in the subjects table.

answered Oct 10 '18 at 20:29

The Impaler

45,731
9
39
76

Precisely!!!!!!! – nicolasl Oct 10 '18 at 20:31
yes thanks I solved that part of problem, I got another error after solving the error you mentioned `SQL query: -- -- Dumping data for table `feedback` -- INSERT INTO `feedback` (`Fed_No`, `Roll_No`, `Sub_Name`, `Feedback`) VALUES (1, 1, 'DBMS', 'Subject is Good'), (2, 1, 'English', 'Subject is Good'), (1, 2, 'DBMS', 'Subject is Good'), (4, 2, 'OS', 'Subject is Good'), (5, 1, 'Algorithms', 'Hate, bruv.'), (6, 1, 'TOC', 'Great Professor') MySQL said: Documentation #1062 - Duplicate entry '1' for key 'PRIMARY'` – ss321c Oct 10 '18 at 20:42
I am trying to identify feedback by feedback id so feedback id and roll number combination can be a primary key – ss321c Oct 10 '18 at 20:43
You are repeating the value `1` (first column) between the first and third row. It's the primary key and must have different values. – The Impaler Oct 10 '18 at 20:44

score 1 · Answer 2 · answered Oct 10 '18 at 20:30

1

It means that you have data in feedback table that does not exists in the subject table. Check the existing data.

answered Oct 10 '18 at 20:30

nicolasl

421
3
16

sql query errors foreign key constraints giving errors

2 Answers2