Integrity Constraints

Integrity constraints, simply put, ensure that changes made to a database do not result in a loss of data. They act as a guard against possible damage to the database. There are a five different types of integrity constraints that I am aware of. The purpose of this node is to provide you with a brief description of each one and how it could be implemented in SQL. This node probably won't be a one-stop source of information. In fact it probably shouldn't be since the SQL here is in the SQL-92 standard and many database systems implement variants on the standard. If you have some SQL and relational database experience then it should provide you with enough information on integrity constraints to implement simple cases or, at the very least, make it easier for you to understand when consulting another source.

An alternative to using integrity constraints is to implement the constraints within the program that updates, inserts, or deletes the data. The advantage to using integrity constraints over this is that you are making the database server do the work. Your program will be smaller, more simple, and probably a little faster.

Domain Constraints

Domain Constraints specify or restrict the set of possible values for a particular column. One example of this is prohibiting NULL as a value for a column in the table.

Use a domain in the column definition of CREATE TABLE or ALTER TABLE ADD COLUMN statements in place of a data type to define the set of valid values entered in the column.

CREATE DOMAIN column-name datatype
CONSTRAINT constraint-name
CHECK predicate

column-name - Name of the column you want to specify a domain for.
datatype - Datatype of the column column-name.
constraint-name - Name you want to give to this domain constraint.
predicate - Boolean expression used to define the domain

EXAMPLES

We have a payroll database that holds information on employees. One piece of such information is the employee's wage. We never want to allow values less than minimum wage in the wage field so we create the constraint:

CREATE DOMAIN wage NUMERIC(5,2)
CONSTRAINT minimum-wage-test
CHECK(VALUE>= 6.50)

Many times you do not want to allow NULL values in a column. To restrict NULL values just make the predicate of the CHECK clause = (VALUE NOT NULL).

It is also possible to explicitly declare the values that can be included:

CHECK(VALUE IN("Primary", "Secondary"))

Referential Integrity

Referential Integrity Constraints ensure that a value appearing in one table for a given set of attributes also appears for a certain set of attributes in a different table. This is desirable if we ever want to perform a natural join on two tables. We need to make sure that we know how the database is going to handle the situation where we do not have a corresponding values that we are trying to match. For instance, if we have a tables:

R =

E|F
-+-
x|a
y|b
y|b
z|a

S =

E|G
-+-
y|a
x|b
y|a
y|b

Looking at the two tables you will notice that we do not have a value in table S, column E for all of the values in table R, column E. If we ever wanted to do a natural join on the two tables we would want to be able to specify how the database will handle this event. We can do this in three ways.

• The PRIMARY KEY clause of the CREATE TABLE statement.
• The UNIQUE KEY clause of the CREATE TABLE statement.
• The FOREIGN KEY clause of the CREATE TABLE statement.

Assertions

An assertion is a predicate that expresses a condition that must always be satisfied within the database system. Perhaps we always want an entry into a specific column to be less than the sum of several columns of various other tables.

Assertions are created in the following manner:

CREATE ASSERTION assertion name CHECK predicate

Triggers

A trigger is an SQL statement that gets executed as a result of an UPDATE, INSERT, or DELETE statement being issued against a table. One practical use for triggers is for saving deleted rows from a table in case a user deletes something they shouldn't have. You can create a trigger that will execute every time a user deletes a row from a specific table. The trigger can copy the deleted row to another table that holds the exact same information and perhaps some additional information such as when it was deleted and who deleted it. This way the row can be restored at any time if need be.

A trigger requires two pieces of information:

1. The condition under which the trigger will execute.
2. The actions that will be taken when the trigger executes.

Types of Triggers

BEFORE statement trigger Before executing the triggering statement, the trigger action is executed.
BEFORE row trigger Before modifying each row affected by the triggering statement and before checking appropriate integrity constraints, the trigger action is executed provided that the trigger restriction was not violated.
AFTER statement trigger After executing the triggering statement and applying any deferred integrity constraints, the trigger action is executed.
AFTER row trigger After modifying each row affected by the triggering statement and possibly applying appropriate integrity constraints, the trigger action is executed for the current row provided the trigger restriction was not violated. Unlike BEFORE row triggers, AFTER row triggers lock rows.

EXAMPLES

Say we want to implement the aforementioned example where we back up deleted rows in the event that they were removed in error. We have a table employee_info. We create another table, employee_info_deleted. If a row in employee_info gets deleted we want to add it to employee_info_deleted. We accomplish this by the following trigger:

CREATE TRIGGER employee_info_trigger
AFTER DELETE ON employee_info ei
IF ei.employee_id IS NOT NULL THEN
(INSERT INTO employee_info_deleted
VALUES(ei.emp_name, ei.emp_id, ei.emp_address, ei.emp_phone))

Functional Dependencies

A Functional Dependency is a constraint on the set of legal relations. The concept is somewhat difficult to clearly summarize so I will reiterate my suggestion for consulting outside sources before implementation.

Suppose we have a table schema for a hospital billing database:

patient-name
patient-ssn
insurance-company
balance

Our set of functional dependencies define how the columns relate to one another. For instance, the set of functional dependencies we would expect to hold for this schema would be:

patient-ssn -> patient-name
patient-ssn -> balance
patient-ssn -> insurance-company

We can not expect the dependancy:

patient-name -> patient-ssn

Because the patient name is not unique enough to determine who it is we want billing information for.

Closure of a Set

It is not enough to just consider the functional dependencies we want to hold true. We must consider all of the functional dependencies that will hold true for this to be an effective integrity constraint. Let us consider this more generic relation schema:

R =

A |B |C |D
--+--+--+--
a₁|b₁|c₁|d₁
a₂|b₂|c₂|d₂
a₃|b₃|c₃|d₃
a₄|b₄|c₄|d₄

We are given the set of functional dependencies:

A -> B
A -> C
B -> DC

BC is equivalent to BUC (B union C).

The functional dependency, A -> BC, also holds because it is logically implied by A -> B and B -> DC. Thus, even though A -> BC is not part of F, it will be part of all of the functional dependencies F+. The closure of F is the set of the functional dependencies implied by F. We call this F+. We can find F+ using the following set of axioms:

• Reflexivity Rule: If a is a set of attributes and b is a subset of a, then a -> b holds.
  
• Augmentation Rule: If a -> b holds and c is a set of attributes, then ca -> cb
  
• Transitivity Rule: If a -> b holds and b -> c holds, then a -> c holds
  

That's all the rules you need to compute F+, however I have provided additional rules that will allow you to more easily obtain F+.

• Union Rule: If a -> b holds and a -> c holds, then a -> bc holds.
  
• Decomposition Rule: If a -> bc holds, then a -> b holds and a -> c holds.
  
• Pseudotransitivity Rule: if a -> b holds and cb -> d, then ac -> d holds.
  

Functional Dependencies in SQL

SQL does not provide an easy way to specify functional dependencies, however you can create assertions that enforce functional dependencies or write queries that will test for functional dependencies.

Conclusion

One of the most beneficial aspects to integrity constraints is their speed and consistency. You could easily implement the constraints within the application but the machine that the application runs on will be doing more work than necessary and if you share tables between two or more applications you may not implement the constraints exactly the same in all applications. Again, this information is primarily provided as a reference or a starting point in integrity constraints and should not be considered a definitive source of information on the topic. Hopefully others more knowledgeable than myself will provide more insight and explanations on the topic.

SOURCES:
Oracle7 Server Concepts Manual, 1996 Oracle Corporation
Database System Concepts, Abraham Silberschatz, Henry F. Korth, S. Sudarshan