- 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.
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.
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.
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.
- 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.
- Domain types
- Attributes may have the same domain, e.g. cname and employee-name.
- It is not as clear whether bname and cname domains ought to be distinct.
- At the implementation level, they are both character strings.
- At the conceptual level, we do not expect customers to have the same names as branches, in general.
- 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.
- The check clause in SQL-92 permits domains to be restricted in powerful ways that most programming language type systems do not permit.
- 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.
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)
check(value not null)
create domain account-type char(10)
check(value in (“Checking”, “Saving”))
- 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.
- 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.
- 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
- 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.
- 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 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
- 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.
- An example illustrates several features mentioned so far:
create table customer(cname char(20) not null,
primary key (cname))
create table branch
(bname char(15) not null,
primary key (bname)
check (assets >= 0))
create table account
(account# char(10) not null,
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.