Link to home
Create AccountLog in
Avatar of rgb192
rgb192Flag for United States of America

asked on

mysql max 3 user_id for (row,col,event_id)

create table schedule(
schedule_id int auto_increment primary key,
row int,
col int,
event_id int,
checked int,
user_id int,
schedule_time datetime
)

alter table schedule add unique index (row,col,event_id);

INSERT INTO schedule (row,col,event_id,user_id) VALUES (18,19,4,34)
  ON DUPLICATE KEY UPDATE user_id=34


this works so I can only have one user_id for (row,col,event_id)


but the requirement changed

I need to have up to 3 max user_id for (row,col,event_id)


example

18,19,4,34
18,19,4,35
18,19,4,36

but say no to user_id 37
Avatar of johanntagle
johanntagle
Flag of Philippines image

in this case you need to remove your unique constraint and you cannot use ON DUPLICATE KEY UPDATE.

I suggest a before insert trigger that checks if there are already three rows and raises an exception if so, aborting the insert in the process.  Before I continue, please answer the following:

1.  Do you know about creating MySQL triggers?  If not I need you to at least go through the following tutorials:
http://www.sitepoint.com/how-to-create-mysql-triggers/
http://net.tutsplus.com/tutorials/databases/introduction-to-mysql-triggers/

2.  What MySQL version are you using?  Because the method for raising exceptions is different in the newer versions.
Avatar of rgb192

ASKER

I know about creating triggers

I am using shared hosting with godaddy

or shared hosting with hostgator



all I know is that it is mysql 5
Well I need you to know the exact mysql version for me to help you further.  FYI calling it a week now - not sure if I'll be able to log on during the weekend so I might not be able to get back to you before Monday.
Avatar of rgb192

ASKER

Mysql 5.5
Avatar of skullnobrains
skullnobrains

if you stick your user ids in the same field or separate ones, you can still get the same results.

assuming the ids are in a comma-separated column

INSERT INTO schedule (row,col,event_id,user_id) VALUES (18,19,4,34)
ON DUPLICATE KEY UPDATE
user_id=CONCAT(
  substring_index(user_id,',',-2)
  ,','
  ,VALUES(user_id))

this query will append a ',' and the new user_id to the right of the existing user id, only retaining the 2 last ones if there were more of them.

the use of the VALUES keywork will also let you insert several rows at a time

you can fairly easily build a more complex structure to do the same with separate fields
ASKER CERTIFIED SOLUTION
Avatar of johanntagle
johanntagle
Flag of Philippines image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
1) definitely no : VALUES(col_name) is the inserted value
2) definitely yes

the trigger above will not allow a new value to replace an old one. you'd need to delete the oldest row prior to the insert instead of rejecting the index

beware that creating a trigger in such a way is not enough to preserve data integrity. for example, an update may change the value for row or col into something that already exists which will allow a fourth matching row.

if you want it to work as expected, you'd also need to check that the user_id does not already exist. i assume that in such a case, you'd want to replace the existing data with the new data

i'm not really sure that i understand the needs, but the trigger solution seems a little complicated to setup, but workable, and mine will not be index-happy and will require the same clause for the remaining columns.

some feedback from the author would be welcome at this point.
Avatar of rgb192

ASKER

>>skullnobrains


so some rows will have a
-

can you please provide more details

will this have 3 rows foreach row,col,event_id
@skullnobrains:

the trigger above will not allow a new value to replace an old one. you'd need to delete the oldest row prior to the insert instead of rejecting the index

Well, the way I understand the original post the requirement is to deny the attempt to insert a fourth record.  You are right though, there has to be a corresponding update trigger to handle changes in the values of row, col and/or event_id.  And there has to be a check if the user_id already exists for the combination, which you should also incorporate to your solution.

Anyway, whether it's too complicated or not, what I've shown here is how you can use triggers to enforce a business rule.  Since the author said he understands triggers, I'll leave it to him to modify and/or add to my proposed solution if he chooses to go that path.
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of rgb192

ASKER

thanks for
a trigger and a select of the top 3 rows