PHP mysql conditional unique constraint?

Question

How do I make a column will take in only x amount of similar string entries?

Example:

------------
| room_type|
|----------|
| premium  |
| premium  |
| premium  |
| premium  |
| premium  |

and if there's different type of entry it will only take in x amount of that as well.

Answer 1

This cannot be done with a constraints. You could write before triggers for this.

Insert trigger:

delimiter $$
create trigger my_insert_trigger before insert on my_table
for each row
begin
    if (select count(*) from my_table where room_type = new.room_type) > 5 then
        signal sqlstate '45000';
    end if;
end;
$$
delimiter ;

Update trigger:

delimiter $$
create trigger my_update_trigger before update on my_table
for each row
begin
    if (select count(*) from my_table where room_type = new.room_type) > 5 then
        signal sqlstate '45000';
    end if;
end;
$$
delimiter ;

Demo on DB Fiddle:

The trigger raises error:

Error: ER_SIGNAL_EXCEPTION: Unhandled user-defined exception condition