MySql conditional insert with dynamic values

Question

So, before I explain my question, here are a couple of split table definitions to help illustrate my problem:

-- Holds data about different memberships CREATE TABLE IF NOT EXISTS `Member_Types` ( `ID` INT UNSIGNED NOT NULL AUTO_INCREMENT, `Name` VARCHAR(20) NOT NULL, `Description` VARCHAR(255) NOT NULL, `Member_Limit` TINYINT(2) UNSIGNED NOT NULL DEFAULT '0', PRIMARY KEY( `ID` ) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; INSERT INTO `Member_Types` ( `ID`, `Name`, `Description`, `Member_Limit` ) VALUES ( 1, 'General Member', 'Description of this membership.', 00 ), ( 2, 'Extended Member', 'Description of this membership.', 00 ), ( 3, 'Premium Member', 'Description of this membership.', 00), ( 4, 'General Administrator', 'Description of this membership.', 05 ), ( 5, 'Master Administrator', 'Description of this membership.', 01 ); -- Stores the basic data about our site members CREATE TABLE IF NOT EXISTS `Member_Infos` ( `ID` BIGINT(8) UNSIGNED ZEROFILL NOT NULL AUTO_INCREMENT, `Username` VARCHAR(30) NOT NULL UNIQUE, `Password` CHAR(41) NOT NULL, `EmailAddr` VARCHAR(100) NOT NULL, `Type_ID` INT UNSIGNED NOT NULL, `Salt_ID` BIGINT(8) UNSIGNED ZEROFILL NOT NULL, PRIMARY KEY( `ID` ), FOREIGN KEY( `Type_ID` ) REFERENCES `Member_Types` ( `ID` ) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; 

The Member_Infos table contains a foreign key that binds Member_Infos.Type_ID = Member_Types.ID

The Member_Types table has a Member_Limit column that contains an integer that represents the maximum number of entries that the Member_Infos table can contain where its Type_ID is equal to Member_Types.ID

I managed to write a control constraint, but apparently mysql does not check the constraints. I would like to write a trigger that checks that the number of entries in the member_infos table is <= Member_Types.Member_Limit before insertion. for example: using the above data, record 4 has Member_Limit = 5. If you try to insert a new record in the Member_Infos table, it has Type_ID = 4 and the number of records in the Member_Infos table with type_ID <= 5 than the data is inserted, otherwise it is rejected. Any advice would be greatly appreciated.

Answer 1

Instead of a trigger, you can write your own simple query to check the "constraints" before inserting. Try:

 INSERT INTO member_infos SELECT 1, 'Timothy', 'secret', ' [e-mail] ', 5, 0 FROM dual WHERE (SELECT COUNT(*) FROM member_infos WHERE Type_ID = 5) < (SELECT Member_Limit FROM member_types WHERE ID = 5) 

I used the check in the case of Type_ID = 5. This ignores if the counting criteria is not met and inserts only if the number of elements in the member_info with type id = 5 is less than the limit set in the member_types table

Answer 2

To raise an error, use the SIGNAL statement in the trigger.

See http://dev.mysql.com/doc/refman/5.5/en/signal.html

Answer 3

In MySQL, a trigger is probably the right way to do this at the database level.

However, applying such a business rule is often considered to be something that you should do at the application level and not at the database. Business rules tend to change, and it is usually easier to change the level of an application than a database. It is also easier to write unit tests for application level code, and it is usually easier to debug the application level.

Answer 4

Sql Fiddle Demo Link

You can try adding the following insert statement

 INSERT INTO `member_infos` (`ID`, `Username`, `Password`, `EmailAddr`, `Type_ID`, `Salt_ID`) VALUES (2, 'ewsew', 'ew', 'ewq', 2, 2); 

at the end of all this code. You will not be allowed this insert because type_id=2 0 entries are allowed for member_ifos . Similary, you will get an error while pasting using type_id=5 after pasting five reocrds.

I added a trigger because you need validation against every new record, not some static value. So this is not the same question when dual might work like some SO questions like MySQL conditional insert because you need new.type_id, which is only available in triggers

Below is the full code

 CREATE TABLE IF NOT EXISTS `member_infos` ( `ID` bigint(8) unsigned zerofill NOT NULL AUTO_INCREMENT, `Username` varchar(30) NOT NULL, `Password` char(41) NOT NULL, `EmailAddr` varchar(100) NOT NULL, `Type_ID` int(10) unsigned NOT NULL, `Salt_ID` bigint(8) unsigned zerofill NOT NULL, PRIMARY KEY (`ID`), UNIQUE KEY `Username` (`Username`), KEY `Type_ID` (`Type_ID`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ; -- Triggers `member_infos` DROP TRIGGER IF EXISTS `validate_Member_infos_insert`; DELIMITER // CREATE TRIGGER `validate_Member_infos_insert` BEFORE INSERT ON `member_infos` FOR EACH ROW begin declare lim int;declare cnt int; select member_limit into lim from member_types where id=new.Type_id; select count(*) into cnt from Member_Infos where type_id=new.Type_id; if cnt>=lim then select `Member Limit exeeded for this type` into lim from member_infos; end if; end // DELIMITER ; CREATE TABLE IF NOT EXISTS `member_types` ( `ID` int(10) unsigned NOT NULL AUTO_INCREMENT, `Name` varchar(20) NOT NULL, `Description` varchar(255) NOT NULL, `Member_Limit` tinyint(2) unsigned NOT NULL DEFAULT '0', PRIMARY KEY (`ID`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ; INSERT INTO `member_types` (`ID`, `Name`, `Description`, `Member_Limit`) VALUES (1, 'General Member', 'Description of this membership.', 0), (2, 'Extended Member', 'Description of this membership.', 0), (3, 'Premium Member', 'Description of this membership.', 0), (4, 'General Administrato', 'Description of this membership.', 5), (5, 'Master Administrator', 'Description of this membership.', 1); INSERT INTO `member_infos` (`ID`, `Username`, `Password`, `EmailAddr`, `Type_ID`, `Salt_ID`) VALUES (1, 'ewsew', 'ew', 'ewq', 5, 2); 

Note. Some may improve / remove error message #1054 - Unknown column 'Member Limit exeeded for this type' in 'field list' , please. This exception is necessary because it blocks the insertion into the required condition. But the improvement will be appreciated. I could not figure it out.