MySql conditional Insert with dynamic values

Question

So before I explain my question, Here are a couple stripped down table definitions to help illustrate my issue:

-- 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 column Member_Limit which contains an integer that represents the maximum number of records that the Member_Infos table can contain where its Type_ID is equal to Member_Types.ID

I was able to write a check constraint but apparently mysql doesn't enfore check constraints. I would like to write a trigger that checks that the count of records in the member_infos table is <= the Member_Types.Member_Limit before inserting. for example: using the data above, record 4 has a Member_Limit = 5. If a new record is attempted to be inserted into the Member_Infos table that has a Type_ID = 4 and a count 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

Sql Fiddle Demo Link

You can try by adding following insert statement

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

at end of this whole code. You would not be allowed this insertion because type_id=2 is allowed 0 records for member_ifos. Similary you will get error on insertion with type_id=5 after inserting five reocrds.

I have added a trigger because you needed validation against each new entry and not some static value. So it not the same question where dual can work like some questions on SO like MySQL Conditional Insert becasue you needed new.type_id which is available only in triggers

Following is complete 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 one might improve/remove the error message #1054 - Unknown column 'Member Limit exeeded for this type' in 'field list' please. This exception is needed because it blocks insertion on asker's required condition. But improvement will be appreciated. I could not sort it out.

Answer 2

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

However, enforcing this kind of business rule is often considered something you should do in the application layer, rather than in the database. Business rules have a habit of changing, and it's usually easier to modify the application layer than the database. It's also easier to write unit tests for application layer code, and it's usually easier to debug the application layer.

Answer 3

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

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

Answer 4

Instead of a trigger you could write your own plain query to check the "constraints" before insert. Try:

INSERT INTO member_infos
SELECT      1, 'Timothy', 'secret', 'me@myself.com', 5, 0
FROM        dual
WHERE       (SELECT COUNT(*) FROM member_infos WHERE Type_ID = 5) 
            < 
            (SELECT Member_Limit FROM member_types WHERE ID = 5)

I have used to check in case of Type_ID = 5. This ignores if count criterion is not met and inserts only if count of member of entries in member_info with type id = 5 is less than the limit set in your member_types table