How to add a constraint so to prevent data manipulation in MySQL

Question

There is a test tomorrow and a question got me thinking,

"Sanjay is not able to reduce the salary of employee. Which constraint has he used while creating table?"

I couldn't find any answer over the net. I think the answer may be "CHECK" but I am not sure how to use it.

question is more is he able to edit anything at all? (if not he has no update permissions on the table contents) — Thomas, Nov 26 '15 at 14:14
This is the whole question. Actually in my course its just that we create our own table and edit the table ourselves. That means we have all the permissions and only thing to check us are the constraints. — Prakhar Londhe, Nov 26 '15 at 14:19
hmmm http://stackoverflow.com/questions/21743131/how-to-create-a-check-constraint-in-mysql-that-requires-negative-values according to that answer I'm not sure that check will help.....as it is parsed but ignored — Thomas, Nov 26 '15 at 14:22
A trigger COULD help though as detailed in these questions: http://stackoverflow.com/questions/13513084/mysql-deny-update-on-row http://stackoverflow.com/questions/15928303/detecting-if-old-value-is-not-equal-to-new-value-and-old-value-was-null — Thomas, Nov 26 '15 at 14:23

score 0 · Answer 1 · edited Jun 20 '20 at 09:12

0

This is a tricky question, since we don't know if he's able to raise the salary or if he's able to update the table at all.

As far as I know it's not possible to allow a user to increase a value, but deny him to decrease it.

Sanjay probably has no permission to update the column.

OR

the is a trigger that is not setting the new salary if it's less than then new salary. this is not very secure since the user is obviously allow to alter the table, so he could alter the trigger.

edited Jun 20 '20 at 09:12

Community

1
1

answered Nov 26 '15 at 14:22

Philipp Sander

10,139
6
45
78

This is the whole question. Actually in my course its just that we create our own table and edit the table ourselves. That means we have all the permissions and only thing to check us are the constraints. – Prakhar Londhe Nov 26 '15 at 14:24

score 0 · Accepted Answer · answered Nov 26 '15 at 14:29

0

I think the answer will be CHECK because the question only says that prevents him from REDUCING the salary, so we can add a CHECK constraint that prevents from entering a salary value for the row which is lower than the entered salary.

answered Nov 26 '15 at 14:29

Prakhar Londhe

1,431
1
12
26

How to add a constraint so to prevent data manipulation in MySQL

2 Answers2

OR