My favourite feature in a database is constraints: Primary Key, Foreign Key, Unique Key, Not NULL, etc.
Why? Because I like to have good quality data in my databases! The only way to have a good quality data is to design the databases and use constraints whenever possible.
To avoid duplicate data I use Primary Key and Unique Key Constraints, to avoid orphan records in child tables I use Foreign Key Constraints and to avoid data nobody can understand (NULLs!) I use Not NULL constraints. I can also define CHECK constraints to define a condition for each row in the table, the data is not accepted to the table unless it meets the criteria defined in a constraint. A simple example of a CHECK Constraint would be a list of values for a column, for example each row must have either “Yes” or “No” as a value for the column. A CHECK constraint can only be defined for columns in one table, not columns in several tables nor all the data in a table. What we do not have yet is a “CHECK constraint for several tables at a time”, “CHECK constraint for a whole table” or “CHECK constraint for the whole database”. This kind of constraint is called an assertion. If you want to vote for this important feature to be implemented in Oracle database, just go and vote: https://community.oracle.com/ideas/13028 !
These are just examples of a great feature. The database will look after the data quality for me, while I do more interesting things…
John Flack said:
I believe that the number one cause of bad triggers is incorrect attempts to enforce assertions.
That might be true…