CODEWITHSUNDEEP
mysqlforeign-keysinnodb
meder omuraliev
meder omuraliev

Reputation: 186662

Is it common to explicitly define *every* foreign key when using InnoDB in MySQL?

I've been using MyISAM and not defining explicit foreign key relationships until recently when I decided to start worrying about referential integrity and such.

I'm working on a database that stores statistics of fighting events, fighters, and so I think this meets the criteria for using InnoDB and explicitly defining foreign keys.

I have a fighters table which has multiple columns which are foreign keys. I'm wondering if it's recommended to always, explicitly define foreign key relationships no matter how many foreign keys there are referenced in a table?

In particular, for this table I have:

CREATE TABLE `fights` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `event_id` int(10) unsigned DEFAULT NULL,
  `winner_id` int(10) unsigned DEFAULT NULL,
  `referee_id` int(10) unsigned DEFAULT NULL,
  `championship_match` enum('1','0') DEFAULT NULL,
  `weight_class` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

I plan to make referee_id, winner_id, event_id all foreign keys to their respective tables' id columns. Is this the way to go? Or should I restrict the amount of explicitly defined foreign keys for performance reasons at the cost of relational integrity?

Upvotes: 2

Views: 212

Answers (1)

Mike Lewis
Mike Lewis

Reputation: 64167

Never, ever give up correct data, ever. Please keep your referential integrity.

Upvotes: 6

Related Questions