0

I am wondering whether there exists some way to insert data into database (Postgres in my case but the question is meant more generally towards SQL databases) ignoring any restrictions given by the scheme of the database. Some specific keyword to say "just put these data there I am completly aware of what they are don't modify them anyhow".

I have a trigger that restricts updates of dates in ceratin tables + I force default values via trigger BEFORE INSERT and I am wondering if it is a good idea to restrict it database-side because I won't be able to "superuser" modify it later or if I should rely the data integrity on the app without the option to rely that the date in the database hasen't been messed with.

This is just a school project and there will be only one app connecting to that database so I don't need that hardcore data integrity but I was interested in the problem of how to solve it.

Thanks

Edit: I dont mean to mess with the core data integrity such as two rows with same primary keys

Péťa Poliak
  • 393
  • 1
  • 3
  • 11
  • Why would you define integrity constraints if you think you need them and then ignore them? Either you need them, then you shouldn't ignore them. Or you don't need them (for some strange reasons) then don't create them. –  May 27 '16 at 09:09
  • 1
    You can add user check to your constraint triggers. – Ihor Romanchenko May 27 '16 at 09:17
  • 1
    @a_horse_with_no_name The question may be about setting up business rules that are different for different roles. Example: simple user should not be able to modify `creation_date` of a record. User `replication` should be able to insert record to DB as they are in other system (ignoring simple user limits). – Ihor Romanchenko May 27 '16 at 09:23
  • Those are security restrictions, not integrity constraints, e.g. through grants or row level security. –  May 27 '16 at 09:24
  • @IgorRomanchenko Yes that is a good idea thank you. – Péťa Poliak May 27 '16 at 10:38
  • @a_horse_with_no_name You are right that it seems more like security restrictions but it would mess with the data integrity as changing the date of origin of certain thing would make the data not reliable. Using what Igor suggested would work that the restrictions wouldent be used for some database manager account that would be able to change it if necessary – Péťa Poliak May 27 '16 at 10:38
  • In case of a trigger, you can disable it (without deleting it), do your data modification and enabled it again(see http://stackoverflow.com/questions/3942258/how-do-i-temporarily-disable-triggers-in-postgresql). The above is generally used in special circumstaces, e.g. bulk import data. On the other had if you want your trigger not to fire when "superuser" is making a change, then follow @Igor Romanchenko advice. – Alex May 27 '16 at 10:39

0 Answers0