---

---

Integrity Constraints

• Integrity constraints are a set of rules. It is used to maintain the quality of information.
• Integrity constraints ensure that the data insertion, updating, and other processes have to be performed in such a way that data integrity is not affected.
• Thus, integrity constraint is used to guard against accidental damage to the database.

Types of Integrity Constraint

1. Domain constraints

• Domain constraints can be defined as the definition of a valid set of values for an attribute.
• The data type of domain includes string, character, integer, time, date, currency, etc. The value of the attribute must be available in the corresponding domain.

Example:

2. Entity integrity constraints

• The entity integrity constraint states that primary key value can’t be null.
• This is because the primary key value is used to identify individual rows in relation and if the primary key has a null value, then we can’t identify those rows.
• A table can contain a null value other than the primary key field.

Example:

3. Referential Integrity Constraints

• A referential integrity constraint is specified between two tables.
• In the Referential integrity constraints, if a foreign key in Table 1 refers to the Primary Key of Table 2, then every value of the Foreign Key in Table 1 must be null or be available in Table 2.

Example:

---

---

4. Key constraints

• Keys are the entity set that is used to identify an entity within its entity set uniquely.
• An entity set can have multiple keys, but out of which one key will be the primary key. A primary key can contain a unique and null value in the relational table.

Example:

Domain Constraints

1. A domain of possible values should be associated with every attribute. These domain constraints are the most basic form of integrity constraint.They are easy to test for when data is entered.
2. Domain types
   1. Attributes may have the same domain, e.g. cname and employee-name.
   2. It is not as clear whether bname and cname domains ought to be distinct.
   3. At the implementation level, they are both character strings.
   4. At the conceptual level, we do not expect customers to have the same names as branches, in general.
   5. Strong typing of domains allows us to test for values inserted, and whether queries make sense. Newer systems, particularly object-oriented database systems, offer a rich set of domain types that can be extended easily.
3. The check clause in SQL-92 permits domains to be restricted in powerful ways that most programming language type systems do not permit.
   1. The check clause permits schema designer to specify a predicate that must be satisfied by any value assigned to a variable whose type is the domain.
   2. Examples:

create domain hourly-wage numeric(5,2)
                          constraint wage-value-test check(value >= 4.00)
Note that constraint wage-value-test” is optional (to give a name to the test to signal which constraint is violated).

create domain account-number char(10)
                           constraint account-number-null-test
               check(value not null)

create domain account-type char(10)
                      constraint account-type-test
      check(value in (Checking”, Saving”))

---

---

Referential Integrity:

Basic Concepts

1. Dangling tuples.
   • Consider a pair of relations r(R) and s(S), and the natural join  .
   • There may be a tuple  in r that does not join with any tuple in s.
   • That is, there is no tuple  in s such that  .
   • We call this a dangling tuple.
   • Dangling tuples may or may not be acceptable.
2. Suppose there is a tuple  in the account relation with the value  Lunartown”, but no matching tuple in the branch relation for the Lunartown branch.This is undesirable, as  should refer to a branch that exists.Now suppose there is a tuple  in the branch relation with  Mokan”, but no matching tuple in the account relation for the Mokan branch.This means that a branch exists for which no accounts exist. This is possible, for example, when a branch is being opened. We want to allow this situation.
3. Note the distinction between these two situations: bname is the primary key of branch, while it is not for account.In account, bname is a foreign key, being the primary key of another relation.
   • Let  and  be two relations with primary keys  and  respectively.
   • We say that a subset  of  is a foreign key referencing  in relation  if it is required that for every tuple  in  there must be a tuple  in  such that 
   • We call these requirements referential integrity constraints.
   • Also known as subset dependencies, as we require

Referential Integrity in the E-R Model

1. These constraints arise frequently. Every relation arising from a relationship set has referential integrity constraints. 
   Figure : An n-ary relationship set
   • Figure  shows an n-ary relationship set R relating entity sets  .
   • Let  denote the primary key of  .
   • The attributes of the relation scheme for relationship set R include  .
   • Each  in the scheme for R is a foreign key that leads to a referential integrity constraint.
2. Relation schemes for weak entity sets must include the primary key of the strong entity set on which they are existence dependent. This is a foreign key, which leads to another referential integrity constraint.

---

---

Database Modification

1. Database modifications can cause violations of referential integrity. To preserve the referential integrity constraintin the following operations:
   • Insert: if a tuple  is inserted into  , the system must ensure that there is a tuple  in  such that  , i.e.
   • Delete: if a tuple  is deleted from  , the system must compute the set of tuples in  that reference  :If this set is not empty, either reject delete command, or delete also the tuples that reference  .
   • Update: two cases
     • updates to referencing relation: test similar to insert case must be made, ensuring if  is new value of tuple,
     • updates to referenced relation: test similar to delete if update modifies values for primary key, must computeto ensure that we are not removing a value referenced by tuples in  .

Referential Integrity in SQL

1. An addition to the original standard allows specification of primary and candidate keys and foreign keys as part of the create table command:
   • primary key clause includes a list of attributes forming the primary key.
   • unique key clause includes a list of attributes forming a candidate key.
   • foreign key clause includes a list of attributes forming the foreign key, and the name of the relation referenced by the foreign key.
2. An example illustrates several features mentioned so far:

create table customer(cname char(20) not null,
 street char(30),
city char(30),
primary key (cname))
create table branch
(bname char(15) not null,
bcity char(30),
assets integer,
primary key (bname)
check (assets >= 0))

create table account
(account# char(10) not null,
(bname char(15),
balance integer,
primary key (account#)
foreign key (bname) references branch,
check (balance >= 0))
create table depositor
(cname char(20) not null,
account# char(10) not null,
primary key (cname, account#)
foreign key (cname) references customer,
foreign key (account#) references account)
3. Notes on foreign keys:

• • A short form to declare a single column is a foreign key. bname char(15) references branch
  • When a referential integrity constraint is violated, the normal procedure is to reject the action. But a foreign key clause in SQL-92 can specify steps to be taken to change the tuples in the referenced relation to restore the constraint.

Example. create table account
…
foreign key (bname) references branch
                     on delete cascade
                   on insert cascade,
…
If a delete of a tuple in branch results in the preceding referential integrity constraints being violated, the delete is not rejected, but the delete cascade” to the account relation, deleting the tuple that refers to the branch that was deleted. Update will be cascaded to the new value of the branch!
SQL-92 also allows the foreign key clause to specify actions other than cascade, such as setting the refencing field to null, or to a default value, if the constraint is violated.
If there is a chain of foreign key dependencies across multiple relations, a deletion or update at one end of the chain can propagate across the entire chain.If a cascading update or delete causes a constraint violation that cannot be handled by a further cascading operation, the system aborts the transaction and all the changes caused by the transaction and its cascading actions are undone.
4. Given and complexity and arbitrary nature of the way constraints in SQL behave with null values, it is the best to ensure that all columns of unique and foreign key specifications are declared to be nonnull.

---

---