Member Avatar for keval007

I am using this insert statement -

INSERT INTO `crispwer_unipro`.`users` (
`user_id` ,
`user_name` ,
`password` ,
`email` ,
`activationkey` ,
`status` ,
`fname` ,
`lname` ,
`dob` ,
`gender` ,
`course_id` ,
`college_id` ,
`city_id` 
)
VALUES (
'2', 'k', 'k', 'kk@gmail.com', '', 'activated', 'keval', 'kothari', '2009-12-27', 'M', '1', '1', '0'
), (
NULL , '', '', '', '', '', '', '', '', '', '', '', ''
)

which give me this error -

#1452 - Cannot add or update a child row: a foreign key constraint fails (`crispwer_unipro/users`, CONSTRAINT `users_ibfk_2` FOREIGN KEY (`course_id`) REFERENCES `course` (`course_id`))

My table structure is as follow-

users table:

CREATE TABLE `users` (
  `user_id` int(100) NOT NULL auto_increment,
  `user_name` varchar(20) NOT NULL,
  `password` varchar(10) NOT NULL,
  `email` varchar(30) NOT NULL,
  `activationkey` varchar(50) NOT NULL,
  `status` varchar(10) NOT NULL,
  `fname` varchar(10) NOT NULL,
  `lname` varchar(10) NOT NULL,
  `dob` date NOT NULL,
  `gender` varchar(1) NOT NULL,
  `course_id` int(100) NOT NULL,
  `college_id` int(100) NOT NULL,
  `city_id` int(100) NOT NULL,
  PRIMARY KEY  (`user_id`),
  UNIQUE KEY `username` (`user_name`)
) ENGINE=InnoDB

College table:

CREATE TABLE `colleges` (
  `college_id` int(100) NOT NULL,
  `college_name` varchar(50) NOT NULL,
  `state_id` int(100) NOT NULL,
  `city_id` int(100) NOT NULL,
  `uni_id` int(100) NOT NULL,
  `add1` varchar(100) NOT NULL,
  `add2` varchar(100) NOT NULL,
  `photo_path` varchar(100) default NULL,
  `contact_no` int(100) default NULL,
  PRIMARY KEY  (`college_id`),
  UNIQUE KEY `College Name` (`college_name`)
) ENGINE=InnoDB

course table:

CREATE TABLE `course` (
  `course_id` int(50) NOT NULL,
  `course_name` varchar(100) NOT NULL,
  PRIMARY KEY  (`course_id`),
  UNIQUE KEY `course_name` (`course_name`)
) ENGINE=InnoDB

in all three table

i create the follwing foreign key constraints

users.college_is->college.college_id

users.course_id->course.course_id

No In college table and course table there is some data
i add same id in both field (users.college_id,users.course_id) but
it gives following errors


#1452 - Cannot add or update a child row: a foreign key constraint fails (`crispwer_unipro/users`, CONSTRAINT `users_ibfk_2` FOREIGN KEY (`course_id`) REFERENCES `course` (`course_id`))

please help me to solve this problem

<EMAIL SNIPPED>

Edited by peter_budo because: Keep It Organized - For easy readability, always wrap programming code within posts in [code] (code blocks)
  • 2 Contributors
  • 1 Reply
  • 119 Views
  • 7 Hours Discussion Span
  • Latest Post Latest Post by pritaeas
Member Avatar for pritaeas

`college_id` int(100) NOT NULL

The second value row in your insert tries to add all blank values, which is not allowed. You should remove it, so the insert looks like this:

INSERT INTO `crispwer_unipro`.`users` (
`user_id` ,
`user_name` ,
`password` ,
`email` ,
`activationkey` ,
`status` ,
`fname` ,
`lname` ,
`dob` ,
`gender` ,
`course_id` ,
`college_id` ,
`city_id` 
)
VALUES (
'2', 'k', 'k', 'kk@gmail.com', '', 'activated', 'keval', 'kothari', '2009-12-27', 'M', '1', '1', '0'
)
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.