I'd just like to see what's the best practice when interfacing with a database. When inserting/modifying data in a database, is it best practice to do logic checking prior to interfacing with the DB (for instance in php), or just let MySQL do the checking for instance using foreign key constraints, NOT NULL, and so on?

Example: I could check ahead of time that a primary key exists in one table before inserting in another table, or just attempt an insert and let the foreign key constraint error if it doesn't exist.

Best practice?

  • Don't waste time and resources querying to see if a record with a given key exists. Always just try to insert it and handle the error. IMHO. – Colin 't Hart Feb 4 '19 at 11:00


check data at the client

Check the data on the client side (eg in JavaScript) for the stupid simple checks. eg NOT NULL, attribute < someValue, strings are in the correct format (regex), etc. Feel free to make some REST calls for the validation.

Also, use good GUI designs. The use of Drop Downs help limit what PK values the end users can use.

check data at the middle tier

Check the data at the middle tier because: Not everyone uses web browsers. Not everyone will send "clean data". https://xkcd.com/327/

enforce data rules inside the DB

On of the primary purpose of a database is to store valid data.

Always enforce constraints within the database. There could be more than one application accessing the database. You want those rules to apply to all applications.

For an application assertion, use a function/procedure.

Not the answer you're looking for? Browse other questions tagged or ask your own question.