Enforcing Enterprise Constraints on MySql Database Relations

If I have the following relations

Articles
articleId(PK)
title
content
date

And say my enterprise constraint is:

There may be only 10 article开发者_运维问答s in this table at anyone time

What is the best way to implement this enterprise constraint on the mysql database? Would it purely be handled by code? Should I change anything in my database to enforce such constraints?

I would use a trigger.

You can do this like so:

DELIMITER $$

CREATE TRIGGER bi_articles_each BEFORE INSERT ON articles FOR EACH ROW
BEGIN
  DECLARE number_of_articles INTEGER;
  SELECT count(*) INTO number_of_articles FROM articles;
  IF number_of_articles > 10 THEN
    //select from a non_existing query to force an error and prevent the insert
    SELECT error 
    FROM `There may be only 10 articles in the article table at anyone time`;
  END IF;

END $$

DELIMITER ;

See:
http://dev.mysql.com/doc/refman/5.0/en/triggers.html
http://www.databasedesign-resource.com/mysql-triggers.html

The error generation is a bit clunky, but otherwise it works like a charm. I use it all the time.

The good thing about the trigger is that it is guaranteed to work, regardless of the insertion vector you use, and it allows you to keep (using) standard SQL for inserts, deletes and updates, which can be very handy.

I would create a stored procedure for inserting new articles which would be used exclusively for inserting articles. There I would check for any custom constraints you might have.

If a custom constraint is violated then you can signal the client what constraint is violated (via return variables for example) and skip with inserting article.