Advanced guide to database constraints

Last month we covered the five fundamental database constraints: primary keys, unique constraints, foreign keys, not null, and check constraints.

In this session we looked at more advanced use cases for database constraints, including how to

Ensure there's always a child row in parent-child relationships
Use foreign keys to ensure there are no gaps or overlaps in start/end dates
Check the total across child rows equal the total on a parent column

Highlights include:

04:50 - Deferrable constraints
08:10 - Cyclic foreign keys
11:50 - Deferrable foreign keys demo
23:00 - Constraints with materialized views
28:10 - Materialized view constraint demo
39:10 - Enforce consecutive start/end dates
43:40 - Supertype/subtype relationships
46:50 - Advanced foreign key rules demo
59:30 - Recap of advanced constraint uses

[jmcmnagtzaam]

Great idea to use MV with fast refresh on commit to enforce complex Business Rules. Never thought of that. Thx

[csolanov]

Another way of thinking about the salary range example, would be model the jobs history as incluiding only the start date and omit the end date, so the range would be derived from the current record and the next begin date would be implicitly the end date of the previous job. When creating the first new employee, just insert with the begin date, because there is not next record then this mean the job is current.

[csolanov]

One possible use of deferable constraints would be to update the primary key of a master table, temporary broke child tables consistency, then update child table references, and then finally commit. For example, update the department id. Tipical solution is create a clone of the master record with the new id, then redirect the childs references and finally delete the original master record. I thinks deferable would be a easier alternative maybe.

[csolanov]

Another example of one to many and one to one would be when you have a company catalog and the catalog id is parte of the key for all the derived tables, like the transaction codes for that specific company, but then, you have a "default" transaction code. If you store this value inside the company catalog, in efect, you have to first create the company master with the field for default transaction code in null, create the transaction code in the dependent table and finally update the master record to asign the field with the transaction code just created. Another alternative would be include a field in the transaction codes table to mark a particular code as "default", but then you must enforce that there would be only one "default" code in the table for that company id.

[csolanov]

Maybe a dissadvange of using constrants over mv is the ammount of work done until commit, if the commit fail, then could get a significant rollback activity, in this scenary with large number of records changed, one would preffer getting the error as soon as possible, so the rollback work would be as lower as possible also.