Q:

Example of user-defined integrity rule in database systems?

Hey everyone. I'm currently preparing for my exams and would like to know some examples of user-defined integrity rule in database systems. As far as I understand, it means that I can set 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 ID which is made up of integers and some chars then it won't accept it and return an error.
Could someone confirm and give me some opinion on that? Thank you very much in advance!

A:

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 (ok, not the freeware mob, but they do not provide ISO/IEC/ANSI SQL; and they are fraudulent 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.  

Anyone with a keyboard and a connection can publish anything these days.  Wikipedia is written by unqualified amateurs, and the "definitions" change every week, as slightly less unqualified amateurs edit them.  
Most of them are writing about what they believe, without any actual experience, testing or verification of their beliefs.

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.

