MySQL how to set a column to reference columns from another table

Question

I want to define the value of columnA in tableA to have the count of all rows in tableB where tableB.columnB

I have two tables:

articles table
columns: articleId,numComments
comments table
columns: articleId,content

I want numComments column in table "articles" to contain a count of all rows in table comments where articles.articleId=comments.articleId

Do I need to set any one of them as "innodb"?

Answer 1

Do I need to set any one of them as "innodb"?

Yes! InnoDB is the only mysql-engine supporting foreign keys. You than have to do the following:

ALTER TABLE comments ADD CONSTRAINT
        FOREIGN KEY (articleId)
        REFERENCES articles(articleId);

or if you are just creating the table:

CREATE TABLE comments
    (articleId INT,
     content INT,
        FOREIGN KEY (articleId)
        REFERENCES articles(articleId);
        ON DELETE CASCADE // You probably want the comments to be deleted,
                          // whenever the article is deleted
    ) ENGINE=INNODB;

See: http://dev.mysql.com/doc/refman/5.1/en/innodb-foreign-key-constraints.html

And for the numComments-column: Better do a

SELECT count(articleId) FROM comments WHERE articleId=selectedArticleId;

Answer 2

You can define a calculated column with a view based off of your two tables, but this IMHO is not a good design choice for what it appears you are trying to accomplish. If this is or will become a large table with many articles and even more comments, then counting rows in a comment table will quickly become a performance problem.

I would probably create an article_comments_count table, but you could just have the numComments column in your articles table defined as a number. Then, create a stored procedure that inserts the record in the comments table and updates the numComments column in the articles table accordingly. Restrict access to the comments table so that all inserts, updates, and deletes have to go through stored_procs so the numComments value can be updated. This method of updating also allows you to later add other functionality without having to change your application code. In your scenario, I could see adding a some sort of white/black list that would be checked before a comment is inserted.