Ad

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.

Constrains:

  • 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?

Ad

Answer

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_TIME or 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.

Ad
source: stackoverflow.com
Ad