Sql Implementation Method Choice
I am confused about constructing sql constraints.
Suppose i have two table named person and rent_car. person has two column named person_id, job. rent_car has also two column named person_id and renting_date.
- if person is doctor, then he/she only rent a car between 12.00 and 19.00.Other people can rent a car between 10.00 and 18.00.
- if person is doctor, then the id must include "dr".
Which method suits me? Trigger, check or assertion and why?
Triggers happen, declarative constraints are.
Declarative constraints describe a state in which the data are valid, and so they must be satisfied at all times. That means that the validity of a record with respect to a declarative constraint can change only if the data in the record change; the validity of the record must not change as a result of some event in some other part of the universe. This is why, on systems that enforce them, check constraints may not refer to system variables such as
CURRENT_USER which change over time, or to other records in the table or to records in other tables. A foreign key constraint's
ON DELETE and
ON UPDATE clauses prevent a change in the parent table from invalidating records in the child table.
A trigger, on the other hand, is a procedure which executes at discrete points in time. Any tests it performs need be satisfied only in the instant in which they are executed, and are tested only against the instantaneous values of their arguments. Thus, triggers are free to refer to dynamic system variables, other records, other tables or even other databases.
To your question, then. I would use a declarative constraint for your second case, as you are comparing the record to a constant string. For the rentals it would depend. If the database is recording rentals happening "right now," then the times are being compared to "right now" which can only be done in a trigger. If the table records reservations for future rentals, then the rental time is being compared to constants and a check constraint would be appropriate.
As I understand them, assertions are a tool for testing the correctness of your code, not of your data, and so are not an appropriate tool for implementing business rules.
Hope that helps.
- → I can't do a foreign key, constraint error
- → How to implement DbDongle::convertTimestamps as workaround of invalid timestamps with MySql strict
- → MySQL error "Foreign key constraint is incorrectly formed"
- → Eloquent Multitable query
- → "Laravel 5.1" add user and project with userId
- → Database backup with custom code in laravel 5 and get the data upto 10 rows from per table in database
- → Laravel 5.1 QueryException when trying to delete a project
- → Using Array in '->where()' for Laravel Query Building
- → Chaining "Count of Columns" of a Method to Single Query Builder
- → Laravel Eloquent Joining Strange query
- → convert time using mysql laravel 5
- → How to update a column after an expiration date in MySQL?
- → Foreign key constraint fails on existing key