Is foreign key required in my database tables?

I have following two mysql tables for the basic discussion forum. The first table contains the topics, while the second contains the replies to the specific topic. I want to know about the foreign key concept in this database design. Do I need to add foreign key here? How it will be useful and how can I add in following tables. Thanks.

--
-- Table structure for table `topics`
--

CREATE开发者_JS百科 TABLE IF NOT EXISTS `topics` (
  `topic_id` int(11) NOT NULL AUTO_INCREMENT,
  `topic_title` varchar(255) NOT NULL,
  `topic_content` text NOT NULL,
  `topic_author_id` int(11) NOT NULL,
  `topic_date` int(10) NOT NULL,
  PRIMARY KEY (`topic_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

--
-- Table structure for table `replies`
--

CREATE TABLE IF NOT EXISTS `replies` (
  `reply_id` int(11) NOT NULL AUTO_INCREMENT,
  `reply_topic_id` int(11) NOT NULL,
  `reply_content` text NOT NULL,
  `reply_author_id` int(11) NOT NULL,
  `reply_date` int(10) NOT NULL,
  PRIMARY KEY (`reply_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

---

A foreign key isn't technically required - but from a data quality standpoint, it's highly recommended.

A foreign key establishes a relationship between two tables - it defines and ensures that :

• you don't have any child rows (in replies) that reference a parent row (in topics) that doesn't exist ("zombie data")

• it makes sure you don't accidentally delete a parent row as long as there are still child rows around

and in general, it ensures the quality and integrity of your data - a highly recommended property in your database!

---

In general MySQL doesn't require you to insert foreign keys. Moreover, as long as you use the MyISAM storage engine you are not able to benefit from defining foreign keys. They accept the foreign key syntax but internally they just ignore those statements, see foreign key entry in MySQL documentation for details.

Instead you might consider defining additional indexes for the foreign key columns what might increase your query performance. You can define an index together with your table definition

CREATE TABLE IF NOT EXISTS `replies` (
  `reply_id` int(11) NOT NULL AUTO_INCREMENT,
  `reply_topic_id` int(11) NOT NULL,
  `reply_content` text NOT NULL,
  `reply_author_id` int(11) NOT NULL,
  `reply_date` int(10) NOT NULL,
  PRIMARY KEY (`reply_id`),
  INDEX (`reply_topic_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

See mysql create table syntax for details.

As an alternative you might consider changing your storage engine to InnoDB and make use of foreign keys which will add indexes to the needed columns automatically. But you will only benefit if you really need other features beside performance optimization like foreign key constraints.

As a rule of thumb one can stick to the suggestions given in the mysql manual. If you don't need the additional benefits like constraints the MyISAM storage engine will deliver the better performance. So, if you don't want the database to ensure referential integrity, instead doing this on your own within your application then MyISAM with proper indexes will be the faster and better performing solution. If you want your database to ensure referential integrity, then switch to InnoDB storage engine and define foreign keys with proper constraints.

---

The replies table should have a column that contains the topic_id of the topic in topics table to which a particular reply in the replies table is meant for.

e.g.

`topic_id` int(11) FOREIGN KEY REFERENCES `topics`(`topic_id`)