What are some examples of user-defined integrity rules in a database system?
It means setting up certain conditions for the columns and when data is inserted it needs to fulfill these conditions.
For example: If I set up a rule that an ID needs to consist of 5 integers ONLY then when I insert a row with an ID which is made up of integers and some chars then it won't accept it and will return an error.
Yes, that's the whole idea of a database, as opposed to an uncontrolled data storage location.
They are not user-defined, they are Data Modeller or Database Administrator or Database Owner (all specific terms) defined. If you left it up to the users or the developers, they would read the rubbish available free or the internet, and implement a monster.
The idea of a database, for over 30 years, is that the entire definition for it is inside the database. Over that period, there have been many Standards (from international standards bodies, not from single vendors, or sellers of software), that have incrementally identified various standards to elevate the integrity of databases. The SQL vendors have implemented them (admittedly, not the freeware database systems, but they do not provide ISO/IEC/ANSI SQL; technically they are in error in using the standard term "SQL" in those products).
In standard SQL, we have the following, as Declarations (ie. not code outside the database, not triggers):
Referential Integrity. The parent row referred to by the child must exist before the child row can be inserted. That happens to be the Relation definition, so there is no doubling up.
Data Integrity. Various forms:
First ensure the correct Datatype is used. Consistently. That eliminates putting character values in integer columns; 10-digit values in 4-digit columns, etc.
RULES. These ensure that all rules for the column (such as your example) are observed.
CHECK Constraints. These allow column values to be checked against other columns, before insertion is allowed. They can be simple or complex.
(with triggers, you can implement any further constraints that the above Declarations do not supply) .
Transactional Integrity. This ensures that the integrity between rows in different tables is maintained, and the data can be relied upon.
I repeat, if it does not have these basics implemented (on every column), it is not a database, it is merely a storage location, for data that has no integrity.
You can confirm this easily, by checking some Standard SQL manuals, or the SQL Standard itself (both available free on the internet).
Any information contrary to the above, found on the internet, is not worth responding to.
It is a waste of time to argue with Standards; they have a purpose; they were created by minds greater than ours; and they have long (30 years!) been accepted (demanded) by larger organisations and governments. Small organisations can save time by following these standards, instead of holding the belief that they are somehow "different". We are dealing with physical science and technology in this industry, not magical thinking.
Business Rules in the Application Layer
Well, if you put the rules in the server, they are guaranteed by the server. I would trust that any day, over the faultless execution of any application layer, particularly one written by people who do not understand databases, or their purpose.
Databases are supposed to completely independent of the apps (note the plural) that use them. It would be silly to place those rules in five apps, instead of in one place, in the database. What happens when the rules change, can anyone guarantee that all apps will be upgraded correctly, and in unison ?
Remember also that data is a corporate, not a departmental asset. The corporation implements standards, so that they can avoid the time wasted by departmental administrators having silly arguments that were resolved 30 years ago, outside their department.
Again, people posting what they think, as if they were the first person to go through this thought process, without any qualifications (which would mean they are aware of standards) or understanding or actual experience (which would change their beliefs real fast).
Therefore: all business rules that pertain to data, in any way shape or form, should be placed with the data, in the IEC/ISO/ANSI Standard catalogue, in the database.
Any business logic (app and usage dependent) can happily be placed anywhere the business decides. That way, the integrity of the corporate asset is preserved, and the damage is limited to the department that entertains magical thinking.
These are CHECK constraints in Oracle, you define them like this:
alter table mytable add constraint mytable_id_chk check (length(id) = 5);
MySQL does not as yet honor Check constraints. It parses them in a Create Table statement but does not honor them. Thus, in MySQL, you must implement data integrity rules of the sort you mentioned in a Trigger.
Check constraints are the proper SQL tool to implement data integrity checks of the sort you mentioned and most of the larger vendors (DB2, SQL Server, Postgres, and yes Oracle), implement and honor Check constraints. However, some types of constraints such as ones that might require a query to another table or complex logic, can only be implemented in Triggers.
