Discuss Referential Integrity Constraints.

1.Domain constraints

• Domain constraints are the most elementary form of integrity constraint. The principle behind domains is similar to that behind variable types in programming languages. Strongly typed programming languages allow the compiler to restrict type of values assigned to the variable.
• Similarly, declaring an attribute to be of a particular domain acts as a constraint on the values that it can take, such as integer, character, date/time, etc.
• They are tested easily by the system whenever a new data item is entered into the database.
• It is possible for several attributes to have the same domain. For example, the attributes customer-name and employee-name might have the same domain: the set of all person names.

• The create domain clause can be used to define new domains. For example, the statements:

  create domain Dollars numeric(12,2)

  create domain Pounds numeric(12,2)

  define the domains Dollars and Pounds to be decimal numbers with a total of 12 digits, two of which are placed after the decimal point. An attempt to assign a value of type Dollars to a variable of type Pounds would result in a syntax error, although both are of the same numeric type.

• Drop domain and alter domain clauses are usedto drop or modify domains that have been already created.
• Checkclause permits the schema designer to specify a predicate that must be satisfied byany value assigned to a variable whose type is the domain. For instance, a checkclause can ensure that an hourly wage domain allows only values greater than aspecified value (say 4.00): create domainHourlyWage numeric(5,2) check(value >=4.00)

2.Referential Integrity Constraints

• Referential integrity is a database constraint that ensures that references between data are indeed valid and intact.
• Referential integrity is a fundamental principle which states that a database should not only store data, but should actively seek to ensure its quality.

• Referential Integrity prevents your table from having incorrect or incomplete relationship.

• Rules followed are as follows:

  • A record cannot be deleted from a primary table if matching records exist in a related table.
  • A primary key value cannot be changed in the primary table if that record has related records.
  • A value cannot be entered in the foreign key field of the related table that doesn't exist in the primary key of the primary table.
  • A Null value can be entered in the foreign key, specifying that the records are unrelated.

• Example

  In Employee and Department relationship the dept_id is a foreign key in Employee table than by using referential integrity constraints, creation of Employee can be avoided without department or non-existing department. Referential Integrity makes primary key foreign key relationship viable. Creating Employee and Department table with primary key, foreign key and referential Integrity constraints.

      CREATETABLE Department 
      (dept_id INT NOTNULL,
      dept_name VARCHAR(250),
      PRIMARYKEY (dept_id)) ENGINE=INNODB;
  
      CREATETABLE Employee 
      (emp_id INT NOTNULL,
      emp_name VARCHAR(250),
      dept_id INT,
      FOREIGNKEY (dept_id) REFERENCESDepartment(dept_id)
      ONDELETE CASCADE) ENGINE=INNODB;
  

  In the above Department and Employee table, dept_id is a foreign key in Employee table.

  On creation of foreign key, ON DELETE clause is specified which tells what needs to done when a record from parent table is deleted.

  CASCADE referential action allows to delete or update all matching rows from child table, after deleting a record in parent table. This way Referential Integrity preserves data integrity of relationship.