Auto_increment

[mr_scooby]

I have a table that holds a users id and a cd number, what I would like is to be able to only increment the cd number based the users id not just incrementing it every time a new cd is created

table

CREATE TABLE IF NOT EXISTS `cdcreation` (
  `user_Id` int(3) NOT NULL,
  `cd_Number` int(2) NOT NULL,
  PRIMARY KEY (`user_Id`,`cd_Number`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

user id's start at 100, so if an id is present but no cd user 100 will get cd 1, if user 101 has no cd they get cd 1 as well, when user 100 makes next cd they get cd 2 and when user 102 gets 1st cd they get cd 1, so it increments based on user number and cd number.

Both cd number and user id will form the primary key, I would also like to be able to max the number of cd's able to be created to 5(this is not critical but a preference) I use AUTO_INCREMENT in other parts of my db but not sure how to implement it this way based on a user id.

any help much appreciated

[mr_scooby]

kk I never realized that by default it would do it, each time you add a new record by simply adding AUTO_INCREMENT to the cd number, would I be safe in assuming this happened because i used both columns as primary keys?

So just the matter of been able to cap the number of dvd's made.

[mr_scooby]

kk looks like i need to use a trigger to check before inserting a record, so far I have come up with this

CREATE TRIGGER cdNumberCheck BEFORE INSERT ON `cdCreation`
FOR EACH ROW
IF cd_Number >= 5 THEN
	/* need code to go here if above*/
END IF

this seems to be along the right lines, just I can't seem to find anything that will work as to throw a message that tells me limit reached.