I've done some searching on the internet but haven't be able to find an answer or solution. I'm wondering whether it is possible to apply logic within PhpMyAdmin to prevent certain users appearing in the aliases list on another table?

I have a "users" table and a "races" table. In the races table I have a column called "Steward" which is a foreign key (index) referencing the primary key of the user table. The problem is not all the users in the users table have the privilege of being a steward. Is there a way to stop the non-steward users appearing in the races table?

For further support, here's my users table:

Here's my races table:

In summary, I don't want the users who don't have the access level of "steward" in the users table appearing in the races "stewards" column of the races table.

CodePudding user response：

Most developers handle this kind of business rule in application code. That is, just write your code to check a user's access_level before inserting a row for that user in the races table.

If you need a database constraint to enforce that, you could do it this way:

1. Add an index on the user table for the pair of columns (user_id, access_level)
2. Add a column access_level to the races table that is always 2. For example, you could do this by defining a stored virtual column that is fixed to the value 2, or by using a CHECK constraint.
3. Make a foreign key on the pair of columns (race_steward, access_level) referencing the index you created in the user table. Since the access_level must match for the foreign key to be satisfied, and the value is forced to be 2 in the races table, then it can only reference users who are stewards.