Home‎ > ‎Database‎ > ‎

Integrity constraints

Being able to control and maintain who, what, when and where to edit your site is key.  Having people being able to change your website or data table can be hazardous to your site.  It is all about the quality of information in which you put on your site.  This is where integrity constraints come into play.  Integrity constraints are sets of rules that can help maintain the quality of information that is put up.  Integrity constraints are mostly used when trying to promote accuracy and consistency of data that is found in a relational database.  This is very important to companies because information can be considered as an asset to certain organizations and it must be protected.


An example from Oracle and their integrity constraints this shows what you can do and when you can do it.  When a specific column in a table is not specified to be not null, it is still possible to insert a null in any column.  The only time you are not allowed to insert a null or non null is when it is specified in the default.


When dealing with integrity constraints there are a few sub-constraints which all tie back into the big picture of integrity constraints. There is referential integrity, entity integrity and domain integrity.  Referential integrity is the consistency of two coupled columns.  When a primary key or a foreign key contain important information from other tables, they should not be tampered with.  If this information were to be changed or deleted they would not be following referential integrity.  Next is entity integrity, which means that every table is required to have a unique and a non-null primary key.  This usually occurs when a table has duplicated a particular row.  When a primary key is not null this just makes sure that the value can be identify all rows in a table.  The last of the three is domain integrity.  Domain integrity helps ensure the length and type of data, the null value, the allowable value and the default value. When dealing with domain integrity, it ensures that the identity and purpose of the tables are clearly identified.  Also, all fields must be consistent through the whole database; it should also clearly identify what modifications, comparisons and operations are allowed. 


As far as integrity constraints, when there is a violation dealing with the primary key, foreign key, check or unique constraint such as an insert, update or deletion of statements, this is when an integrity constraint violation occurs.  According to Sybooks Online, they show a table which shows integrity constraint violations.  These are some of the examples of integrity constraint violations.  When you duplicate a row then try to insert it into a table that has a unique constraint or index this message will pop up, “attempt to insert duplicate key row in object object_name with unique index index_name.”  Another violation that may occur is when you update or delete information that is on a primary key table, this would be considered a violation of foreign key constraint.  If you were to violate this constraint this message would appear, “dependent foreign key constraint key constraint violation in a referential integrity constraint.  Dbname = database name, table name = table_name, constraint name = constraint_name.”  If you ever encounter messages of this sort, most likely you have violated an integrity constraint and will need to go back a trace your steps to find the problem. 


All in all when dealing with integrity constraints once the rules are laid out your are unable to go back and add or change any of the keys or information on the tables.  If you were to add, delete or change anything on the tables, integrity violation message will occur.  To avoid any confusion or issues try not to tamper with set information or standards.   


To learn more follow this link: Integrity Constraints