CODEWITHSUNDEEP
mysqlforeign-keysdatabase-integrity
Jason Swett
Jason Swett

Reputation: 45164

Foreign key constraint should be complaining but somehow it's not

I have, among others, three tables: account, address and account_address. The account_address table has an account_id and an address_id. It's your standard many-to-many relationship.

I have a perplexing situation where I have an account_address record that points to an account that doesn't exist. Since I have a foreign key on account_address.account_id pointing to account, this shouldn't be able to happen, right?

Now let me prove that this should-be-impossible thing is happening. First I'll show you my table definition:

CREATE TABLE `account_address` (    
  `id` bigint(20) NOT NULL AUTO_INCREMENT,    
  `account_id` bigint(20) NOT NULL,    
  `address_id` bigint(20) NOT NULL,   
  `created_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',    
  `updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',    
  PRIMARY KEY (`id`),    
  KEY `fk_account_address_account_id` (`account_id`),    
  KEY `fk_account_address_address_id` (`address_id`),   
  KEY `index_account_address_account_id` (`account_id`) USING BTREE,    
  KEY `index_account_address_address_id` (`address_id`) USING BTREE,    
  CONSTRAINT `fk_account_address_account_id` FOREIGN KEY (`account_id`) REFERENCES `account` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,    
  CONSTRAINT `fk_account_address_address_id` FOREIGN KEY (`address_id`) REFERENCES `address` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION    
) ENGINE=InnoDB AUTO_INCREMENT=697173 DEFAULT CHARSET=latin1

See? FOREIGN KEY (account_id) REFERENCES account (id).

Now here are a few queries that show that constraint failing:

select aa.account_id
from account_address aa
where aa.address_id = 15116

That query gives me the following result:

15116
37033
62325
71857
93774
119066

So apparently address 15116 is attached to six different accounts (one account, interestingly having the same id as the address). But check this out:

select * from account where id in (15116, 37033, 62325, 71857, 93774, 119066)

NO results! Shouldn't my DBMS have told me at some point that I have a foreign key constraint failing?!

I see only two possibilities:

  1. I'm misinterpreting what I'm seeing
  2. My DBMS is misbehaving in a fundamental way

I sure hope #1 is the case but I don't know what I could possibly be misinterpreting. It's a mystery to me of the highest order. Any thoughts would be greatly appreciated.

Upvotes: 1

Views: 221

Answers (2)

Marc B
Marc B

Reputation: 360792

MySQL does have a server variable to disable foreign key checks - set foreign_key_checks=0, which is used in cases like importing a dump file where a table may have an FK pointing at a table "later" in the dump that hasn't been loaded yet. Normally this would kill the import, even though the data's fine. Disabling the FK checks allows the import to proceed.

It's possible that your missing records were deleted during a time when the key check was disabled. To test if the keys are working correctly now, add a couple related records and delete one, which should fail due to the "no action" setting on the FK. If it proceeds, then either you're not on InnoDB (maybe it's disabled and mysql's silently transforming to MyISAM), the key checks are turned off (check that server variable), or something's really screwy with your server.

Upvotes: 0

Nanne
Nanne

Reputation: 64429

A constraint will stop any actions to do something 'evil', but will not retroactivly make sure everything is allright. You can, as many import scripts do because of the order in which stuff happens, set the checking of these constraints to 0.

So if for some reason the information is incorrect, this situation can occur. Then your DBMS is not misbehaving, and you're also not misinterpreting.

So i'd go for option 3: Some import or insert is misbehaving, possibly using "set foreign_key_checks = 0". Or it is old data.

(from the manual:

mysql> SET foreign_key_checks = 0;
mysql> SOURCE dump_file_name;
mysql> SET foreign_key_checks = 1;

)

Upvotes: 1

Related Questions