CODEWITHSUNDEEP
mysqlsql
Kirill
Kirill

Reputation: 217

mysql one to many relation is not working

Having some trouble adding a relation between tables

CREATE TABLE IF NOT EXISTS `employees` (
`emp_id` int(11) NOT NULL auto_increment,
`emp_fname` char(50) NOT NULL,
`emp_sname` char(50) NOT NULL,
`emp_patr` char(50) NOT NULL,
`emp_birth` datetime NOT NULL default CURRENT_TIMESTAMP,
`isInCharge` boolean default NULL,
`depart_id` int(11) NOT NULL,
PRIMARY KEY (`emp_id`)
) ENGINE=InnoDB DEFAULT CHARSET=cp1251;

CREATE TABLE IF NOT EXISTS `department` (
`depart_id` int(11) NOT NULL auto_increment,
`depart_name` char(50) NOT NULL,
PRIMARY KEY (`depart_id`),
FOREIGN KEY (depart_id) REFERENCES employees (depart_id) on delete 
cascade on update cascade
) ENGINE=InnoDB DEFAULT CHARSET=cp1251;

Cannot add foreign key constraint

What is going wrong? I need to make possible for many employees to be in one department.

Also, how can I generate a random birth date from 1950 to 1990?

Upvotes: 1

Views: 203

Answers (2)

chaitanya vardhan
chaitanya vardhan

Reputation: 41

It should be the other way around right? dept_Id in Employee shall be foriegn key referencing the primary key Dept_ID of Department.

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269683

Your foreign key is in the wrong place. It belongs in the employees table not the department table. And it should be:

FOREIGN KEY (depart_id) REFERENCES department(depart_id) on delete cascade on update cascade

This should be easy to remember. You define the primary key in the table where the column is unique. You define the foreign key in all tables that refer to that primary key.

Upvotes: 3

Related Questions