Rule that enforces basic and fundamental information-based constraints. Relational integrity constraint is used to ensure accuracy and consistency of data in a relational database.
Examples: Here are two relational integrity constraints for an ordering system (Also see PowerPoint Chapter 6):
· Users cannot create an order for a nonexistent customer;
· An order cannot be shipped without an address.
To fully understand this term, it’s important to know three key concepts: relational database, information integrity, and integrity constraints.
· Relational database model is a type of database that stores information in the form of logically related two-dimensional tables. The term relational stems from the fact that each table in the database contains information related to a single subject and only that subject.
For example, a data set containing all the real estate transactions in a town can be grouped by the year the transaction occurred; or it can be grouped by the sale price of the transaction; or it can be grouped by the buyer's last name; and so on. Such a grouping uses the relational model (a technical term for this schema). Hence such a database is called a "relational database."
· Information Integrity is the trustworthiness and dependability of information. More specifically, it is the accuracy, consistency and reliability of the information content, processes and systems. Information integrity is also a prerequisite, because you need it for many other management decisions. If certain information cannot be trust and has a low level of integrity than a business has a low chance of success. You need information integrity to have a successful business.
· 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.
Therefore, relational Integrity constraints are rules which all instances of the relational Database must satisfy in order to correctly model the real world.
• A relational database schema (i.e. Database definition) consists of
– relation schemas (table definitions)
– integrity constraints
• Any operation that would violate a declared constraint will be disallowed.
The relationship between Integrity and Database design is very important, as many real-world constraints are imposed by a combination of
– good design of relations (such as via ER modeling), plus
– maintenance of key (uniqueness) constraints
Here is a real world rules: Employee Relation. In a company, an employee has only one name, is in one department, has one supervisor.
It is impossible to violate the rules so long as no two tuples in the employee relation have the same ID.
There are mainly five types of constraints:
· Domain constraints
· Key constraints
· Not Null constraints
· Referential integrity constraints
· Semantic integrity constraints
Key constraints specify attributes or combinations of attributes which must be unique. Primary keys must be unique to allow the key to be use to identify tuples. For example, employee must be unique in the Employee table. We may wish to specify that other (non-primary key) attributes should be unique.
Not Null constraints: no part of a primary key field can contain NULL, if it did it would not be distinguishable from any other NULL. Total participation constraints are encoded by insisting that the foreign key representing the relationship is NOT NULL. For example, Student Course can not be NULL.
Semantic integrity constraints express general restrictions on the data and changes to it. For example, salary should not exceed 200,000; salary cannot decrease; An employee’s salary should not be greater than that of the manager of the department.