relational key contraint problem

[paddy_fields]

Hi, I'm trying to implement foreign key constraints but have hit learning curve!

 

When a job is deleted from jobBoard I would like any associated rows in jobViews to also be deleted.

 

At present when I run a delete query on a row in jobBoard (that has associated records in the other table) it fails and shows the error below;

 

Cannot delete or update a parent row: a foreign key constraint fails (`recruitsmart`.`jobviews`, CONSTRAINT `fk_jobViews_jobBoard1` FOREIGN KEY (`jobBoard_id`) REFERENCES `jobBoard` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION)

 

 

In my head this should be the reasoning...

• the relationship should be one to many, jobBoard to jobViews. ie one job can have many views.
• the relationship should be un-identifying as a job view cannot exist without a job.

This is what I've based my relationship on but evidently it's wrong!

 

I've attached the ERD if anyone could point me in the right direction?

 

Also my syntax is below for the jobViews table;

 

CREATE TABLE `jobViews` (
  `viewDate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `jobBoard_id` int(11) unsigned NOT NULL,
  KEY `fk_jobViews_jobBoard1_idx` (`jobBoard_id`),
  CONSTRAINT `fk_jobViews_jobBoard1` FOREIGN KEY (`jobBoard_id`) REFERENCES `jobBoard` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Edited January 11, 2014 by paddyfields

[kicken]

This part of your create table: ON DELETE NO ACTION ON UPDATE NO ACTION specifies what happens when you try and delete or update the key in the associated table.

 

Possible values are:

NO ACTION / RESTRICT: Prevent the delete/update from occuring

SET NULL: Set the value to NULL

CASCADE: Perform the same operation on this row (ie, either update it to the new value, or delete the row)

SET DEFAULT: Set the value to the defined default value

 

So, what you want to do is specify

ON DELETE CASCADE ON UPDATE CASCADE

[paddy_fields]

Ah, I see.

 

I've just altered ON DELETE CASCADE ON UPDATE CASCADE on both of the related tables and it works like a charm.

 

Thank you