constraint advice please.

[IBazz]

Hi,

In one of my (many-to-many), tables, I have set up two constraints. I need to make sure that if a business is deleted from the Db, that the record is deleted. I need to make sure that if a administator (login-person) is removed from the system, that the record is deleted.

Currently I cannot delete or update a parent record, without MySQL throwing an error #1217.

I think I should have the constraint set up to ON DELETE CASCADE (as highlighted below) and I can do that but, am I correct that that is how it should be done?

bazz

CREATE TABLE IF NOT EXISTS login_businesses
( login_id INT NOT NULL
, business_id INT NOT NULL
, PRIMARY KEY (login_id,business_id)
, KEY secondary_ix (business_id,login_id)
, KEY login_id_ix (login_id)
, CONSTRAINT loginbusinesses_login_fk 
    FOREIGN KEY (login_id) 
      REFERENCES login (login_id) ON DELETE CASCADE
, CONSTRAINT login_businessdetails_fk 
    FOREIGN KEY (business_id) 
      REFERENCES business_details (business_id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

bazz

[r937]

if login_businesses is the many-to-many table between login and business_details, then you're defining the FKs correctly

[IBazz]

Yes it is as you describe. However, I am finding that when I try to delete business_details that i get the following error:

#1217 - Cannot delete or update a parent row: a foreign key constraint fails

No other table has an FK to business_details.

Edit:


Maybe I shouldn't use both constraints? Perhaps I should only have the constraint for 'business_details' so that even if an admin is 'removed' ( set to inactive), all their records will remain for the purposes of amendment tracking?

bazz

[r937]

could you please do another SHOW CREATE TABLE, on both login_businesses and business_details

[IBazz]

yup

CREATE TABLE IF NOT EXISTS business_details 
( business_id int NOT NULL auto_increment
, business_type_id int NOT NULL
, tic varchar (64) NOT NULL
, grp_name varchar(64) NOT NULL
, premises varchar(64) NOT NULL
, business varchar(64) NOT NULL
, active binary(1) NOT NULL default '0'
, meta_keywords text NOT NULL
, meta_content text NOT NULL
, page_title varchar(255) NOT NULL
, PRIMARY KEY  (business_id)
, KEY business_type_ix (business_type_id)
, KEY tic_ix (tic)
, CONSTRAINT businessdetails_businesstype_fk 
    FOREIGN KEY (business_type_id) 
      REFERENCES business_type (business_type_id)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS login_businesses
( login_id INT NOT NULL
, business_id INT NOT NULL
, PRIMARY KEY (login_id,business_id)
, KEY secondary_ix (business_id,login_id)
, KEY login_id_ix (login_id)
, CONSTRAINT loginbusinesses_login_fk 
    FOREIGN KEY (login_id) 
      REFERENCES login (login_id) 
, CONSTRAINT login_businessdetails_fk 
    FOREIGN KEY (business_id) 
      REFERENCES business_details (business_id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

bazz

[r937]

those look okay, no idea why you're getting the error

however, they look like scripts -- are you sure you actually ran the SHOW commands?

[IBazz]

they aren't SHOW commands. I am trying to get them for you in either phpMyAdmin or heidisql. no success yet.

CREATE TABLE `business_details` (
  `business_id` int(11) NOT NULL auto_increment,
  `business_type_id` int(11) NOT NULL,
  `tic` varchar(64) NOT NULL,
  `grp_name` varchar(64) NOT NULL,
  `premises` varchar(64) NOT NULL,
  `business` varchar(64) NOT NULL,
  `active` binary(1) NOT NULL default '0',
  `meta_keywords` text NOT NULL,
  `meta_content` text NOT NULL,
  `page_title` varchar(255) NOT NULL,
  PRIMARY KEY  (`business_id`),
  KEY `business_type_ix` (`business_type_id`),
  KEY `tic_ix` (`tic`),
  CONSTRAINT `businessdetails_businesstype_fk` FOREIGN KEY (`business_type_id`) REFERENCES `business_type` (`business_type_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

CREATE TABLE `login_businesses` (
  `login_id` int(11) NOT NULL,
  `business_id` int(11) NOT NULL,
  PRIMARY KEY  (`login_id`,`business_id`),
  KEY `secondary_ix` (`business_id`,`login_id`),
  KEY `login_id_ix` (`login_id`),
  CONSTRAINT `loginbusinesses_login_fk` FOREIGN KEY (`login_id`) REFERENCES `login` (`login_id`),
  CONSTRAINT `login_businessdetails_fk` FOREIGN KEY (`business_id`) REFERENCES `business_details` (`business_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1

not sure why it has added int(11) to the first two columns. they should just be 'INT'

Are backticks necessary?

bazz

[r937]

go to the SQL window and run this query:

SHOW CREATE TABLE login_businesses

pretty straightforward, eh?

[IBazz]

maybe we overlapped then? I posted the results above.

bazz

[r937]

that looks fine

try deleting a business again