CODEWITHSUNDEEP
phpmysqlsqldatabaseforeign-keys
JasonDavis
JasonDavis

Reputation: 48933

What will this Foreign Key on my MySQL Database table do for me?

I am building a Project Management project with PHP and MySQL. I have decided to add a Forum type Discussion sections. I am planning for it to be just like your typical Forums but instead of different Forum topic/sections, that will be replaced by a Project ID, so each Project in my Project Management software will have it;s own Forum section.

So all I really need is a Topic post and then a Post reply. Each topic will have a Subject/title and then a body/description and any follow up posts/replies will be "Posts"

So I am building Topics and Posts MySQL database tables

My schema is below. Now I have many years working with these DB's but I do not have much experience with the more fancy features like FOREIGN KEYS

So I have this Foreign Key SQL...

ALTER TABLE posts
ADD FOREIGN KEY(parent_topic_id)  
REFERENCES topics(topic_id)  
ON DELETE CASCADE  
ON UPDATE CASCADE;

1) As mentioned, not much experience here with these so my question is Exactly what will this Foreign key do and prevent from happening? What would be the difference between me having it or not using it at all?

Will the ON DELETE CASCADE mean that I can delete a record from the topics table and it will automatically delete all record with a matching topic_id on my posts table? If so that is really nice and worth adding it.

I am not sure what the ON UPDATE CASCADE would do in this case though?

2) If my DB table is created from my PHP code like this...

$DiscussionPostsTableSql = "CREATE TABLE posts (
    ALL my COLUMN DEFINITIONS HERE....)";
$db->query($DiscussionTopicReplyTableSql);

then should I be able to create this Foreign Key in the same manner? Like this...

$foreignKeySql = "ALTER TABLE posts ADD FOREIGN KEY(parent_topic_id) REFERENCES topics(topic_id) ON DELETE CASCADE ON UPDATE CASCADE;)";
$db->query($foreignKeySql);

Or does this need to be added in some other fashion?

Topics Table 

CREATE TABLE topics (
  topic_id char(36) NOT NULL AUTO_INCREMENT,
  project_id char(36) NOT NULL,
  topic_subject VARCHAR(255) NOT NULL,
  topic_content TEXT default NULL,
  date_created DATETIME NOT NULL,
  date_last_post DATETIME NOT NULL,
  created_by_user_id char(36) NOT NULL,
  last_post_user_id char(36) NOT NULL,
  posts_count char(36) default NULL,
  PRIMARY KEY (topic_id)
) TYPE=INNODB;

Posts Table

CREATE TABLE posts (
  post_id char(36) NOT NULL AUTO_INCREMENT,
  parent_topic_id char(36) NOT NULL,
  post_content TEXT NOT NULL,
  date_created DATETIME NOT NULL,
  created_by_user_id char(36) NOT NULL,
  PRIMARY KEY (post_id)
) TYPE=INNODB;

Foreign Key 

// Add Foreign Key between Topic and Posts tables
ALTER TABLE posts
ADD FOREIGN KEY(parent_topic_id)  
REFERENCES topics(topic_id)  
ON DELETE CASCADE  
ON UPDATE CASCADE;

Upvotes: 1

Views: 92

Answers (2)

Sijeesh
Sijeesh

Reputation: 337

Foreign key relationship is part of normalization method.

Here if you are not using separate table for post and topics duplication will come like one topic will have multiple posts.

here you have separate tables for posts and topics and connection topics with posts by foreign key relationship.

In this way you can manage both the tables. Deletion of topics will delete all the posts associated with the topic.

Upvotes: 1

Mureinik
Mureinik

Reputation: 311393

This foreign key will prevent the system from creating posts with a parent_topic_id that doesn't point to any topic_id in the topics table, either by mistake or by malice.

ON DELETE CASCADE means that whenever you delete a topic, all the relevant posts will be deleted with it.

ON UPDATE CASCADE means that if you update a topic's ID, it will update the IDs of all the referencing posts.

As for creating the key - as you noted, you should create it in the same place/fashion you create the table itself.

Note: Various storage engines have various restrictions on foreign key support. InnoDB, which you are using, is a pretty safe bet, but you should review the documentation to get the full details.

Upvotes: 1

Related Questions