Should a well-designed db have its constraints set to default/restrict and if changes need to be made to the db, by any script, then that script should change the constraint as necessary?
Then having done its update or other work, should the script then re-establish the constraint, restrict?
I ask because if a constraint is always set to update etc, then some wrong data or even a deletion might bust the db data.
bazz
presumably you are talking only about foreign key constraints (there are other kinds)
i don’t see the difference between (a) having a foreign key constraint set to restrict, but allowing a script to disable the constraint, make changes, and then restore the constraint, and (b) having a foreign key constraint set to cascade, and allowing a script to make changes
both scenarios can mess up your data just as surely
Thanks rudy,
So, is it better to keep the FK constraint set to restrict and to make the script logic amend both the child and the parent records as necessary, instead of relying on the FK constraint to cascade the changes?
no 
That is a sure sign that the database and application logic is not properly established. Having application code turn on and off a foreign key… that is some type of madness. Additionally, having application code modify table structures after installation is a sign of bad things to come.
thanks oddz and rudy,
I don’t have any scripting established which would do that.
Likely, my question arose from over-thinking. I had a concern that if someone were to find a way to delete a table or some records, the on delete cascade constraint would remove data through numerous tables. I wanted to find a balance between utilising constraints and preventing such a deletion.
I do have the connections set up with the appropriate permissions. And I have the scripts (which allow user input), written only to allow sanitised data input. Indeed all params passed between scripts is sanistised or rejected. Those which don’t invite/support user-generated input are connected with read only privileges so messing with params being passed from page to page, won’t input anything.
I suppose I should only use a fk constraint which allows on update cascade given that, deleting could be performed on each table, from child upwards. Indeed, preventing deletion by archiving the ‘old’ data to another table might be better too.
Is my concern explained well enough? Does it sound like I have addressed or am at least aware of the issues to be secured and addressed?
bazz
if all user interaction with the database is through your application code, and you do not let users get at your database through, for example, the mysql command line or a front-end tool like phpmyadmin, then you have no reason to worry about “someone [finding] a way to delete a table or some records”
useful reassurance rudy. Thanks.
My sole aim on this subject is to prevent anyone from breaking in and damaging the db. Security isn’t perhaps a topic for this forum so I’ll go look for another that may be appropriate.
bazz
Actually security precautions can be taken at the database level to minimize the chances of such risks. People always talk about injection but the truth of the matter is under ideal circumstances the user should only have privileges to carry out tasks which they are allowed to. For example, it is highly unlikely that unless something is being installed schemas will need to change. So application side one should probably connect with a user who has restricted permissions to do so. Also, hypothetically it would be unlikely that an unauthenticated user would need to delete items (not always the case but hypothetically). Granted injection prevention is still a very important part there are things that can be done at a lower level to prevent the database from being compromised given an injection attack takes place. the details of which will always be specific to the business goals and model in question though.
thanks oddz.
I hope I have achieved the reality that the only changes would be made by authenticated access to the cms. (no login user has delete privileges now but they do have insert and update privileges and I think I have all the inputs sanitised).
But I wonder if someone may have malware in their pc and have their passwords compromised - what then?
Someone not authorised could access the db using genuine passwords and do damage by updating existing records with perhaps empty data or worse.
Of course that would be the users fault for having their details compromised but they aren’t necessarily computer whizzes so I need to be gentle. 
so it looks like securing passwords is where I need to be checking.
bazz
when I say securing pwds, I mean setting a monitoring service in place to check usage and also, another, to workaround using the email address to which new pwds may be sent in cases where they need to be updated.