Unit 4: Integrity Constraints

4th semester
BIM Notes

 



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

DBMS Integrity Constraints

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:
DBMS Integrity Constraints

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:
DBMS Integrity Constraints

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:
DBMS Integrity Constraints



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:
DBMS Integrity Constraints

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 tex2html_wrap_inline926 .
    • There may be a tuple tex2html_wrap_inline928 in r that does not join with any tuple in s.
    • That is, there is no tuple tex2html_wrap_inline934 in s such that tex2html_wrap_inline938 .
    • We call this a dangling tuple.
    • Dangling tuples may or may not be acceptable.
  2. Suppose there is a tuple tex2html_wrap_inline940 in the account relation with the value tex2html_wrap_inline942 Lunartown”, but no matching tuple in the branch relation for the Lunartown branch.This is undesirable, as tex2html_wrap_inline940 should refer to a branch that exists.Now suppose there is a tuple tex2html_wrap_inline946 in the branch relation with tex2html_wrap_inline948 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 tex2html_wrap_inline950 and tex2html_wrap_inline952 be two relations with primary keys tex2html_wrap_inline954 and tex2html_wrap_inline956 respectively.
    • We say that a subset tex2html_wrap_inline958 of tex2html_wrap_inline960 is a foreign key referencing tex2html_wrap_inline954 in relation tex2html_wrap_inline964 if it is required that for every tuple tex2html_wrap_inline946 in tex2html_wrap_inline968 there must be a tuple tex2html_wrap_inline940 in tex2html_wrap_inline964 such that tex2html_wrap_inline974
    • We call these requirements referential integrity constraints.
    • Also known as subset dependencies, as we requiredisplaymath920

Referential Integrity in the E-R Model

  1. These constraints arise frequently. Every relation arising from a relationship set has referential integrity constraints.  figure156
    Figure : An n-ary relationship set

    • Figure  shows an n-ary relationship set R relating entity sets tex2html_wrap_inline978 .
    • Let tex2html_wrap_inline980 denote the primary key of tex2html_wrap_inline982 .
    • The attributes of the relation scheme for relationship set R include tex2html_wrap_inline986 .
    • Each tex2html_wrap_inline980 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 constraintdisplaymath920in the following operations:
    • Insert: if a tuple tex2html_wrap_inline946 is inserted into tex2html_wrap_inline968 , the system must ensure that there is a tuple tex2html_wrap_inline940 in tex2html_wrap_inline964 such that tex2html_wrap_inline1012 , i.e.displaymath995
    • Delete: if a tuple tex2html_wrap_inline940 is deleted from tex2html_wrap_inline964 , the system must compute the set of tuples in tex2html_wrap_inline968 that reference tex2html_wrap_inline940 :displaymath996If this set is not empty, either reject delete command, or delete also the tuples that reference tex2html_wrap_inline940 .
    • Update: two cases
      • updates to referencing relation: test similar to insert case must be made, ensuring if tex2html_wrap_inline1024 is new value of tuple,displaymath997
      • updates to referenced relation: test similar to delete if update modifies values for primary key, must computedisplaymath996to ensure that we are not removing a value referenced by tuples in tex2html_wrap_inline968 .

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.