SQL Server - What is Referential Integrity? - Asked By aman on 05-Nov-11 01:49 AM

hi all,

i want to know what actually Referential Integrity refers to?

Please give some example so that i can understand it in much better manner.

thanks,
Suchit shah replied to aman on 05-Nov-11 02:11 AM

Referential integrity is a property of data which, when satisfied, requires every value of one attribute (column) of a relation (table) to exist as a value of another attribute in a different (or the same) relation (table).
 
For referential integrity to hold in a relational database, any field in a table that is declared a foreign key can contain only values from a parent table's primary key or a candidate key. For instance, deleting a record that contains a value referred to by a foreign key in another table would break referential integrity. Some relational database management systems (RDBMS) can enforce referential integrity, normally either by deleting the foreign key rows as well to maintain integrity, or by returning an error and not performing the delete. Which method is used may be determined by a referential integrity constraint defined in a data dictionary.

Benefits of Referential Integrity
 
 Improved data quality
 
An obvious benefit is the boost to the quality of data that is stored in a database. There can still be errors, but at least data references are genuine and intact.
 
 Faster development
 
Referential integrity is declared. This is much more productive (one or two orders of magnitude) than writing custom programming code.
 
 Fewer bugs
 
The declarations of referential integrity are more concise than the equivalent programming code. In essence, such declarations reuse the tried and tested general-purpose code in a database engine, rather than redeveloping the same logic on a case-by-case basis.
 
 Consistency across applications
 
Referential integrity ensures the quality of data references across the multiple application programs that may access a database. You will note that the definitions from the Web are expressed in terms of relational databases. However, the principle of referential integrity applies more broadly. Referential integrity applies to both relational and OO databases, as well as programming languages and modeling.


1.We may not add a record to the Employee Salary table unless the foreign key for that record  points to an existing
employee in the Employee table.

2.If a record in the Employee table is deleted, all corresponding records in the Employee Salary table must be deleted using a cascading delete.  This was the example we had given earlier.

3.If the primary key for a record in the Employee table changes, all corresponding records in the Employee Salary table must be modified using what's called a cascading update.


Suchit shah replied to aman on 05-Nov-11 02:12 AM

Referential Integrity (RI) is the means by which primary/foreign key relationships are enforced in a database. By specifying RI rules you can have the database guarantee, for example, that every sales representative is assigned to a valid office. Through the use of RI constraints, many business rules can be enforced by the database server, instead of your application.

The terms "primary key" and "foreign key" are used throughout this documentation.

  • Primary Key - A unique identifier for a table. A column or column combination with the property that, at any given time, no two rows of the table contain the same value in that column or column combination.

  • Foreign Key - A foreign key is a column or combination of columns whose values match the primary key of some other table. A foreign key does not have to be unique; in fact, foreign keys are often in a many-to-one relationship to a primary key. Foreign key values should be copies of the primary key values; no value in the foreign key except NULL should ever exist unless the same value exists in the primary key. A foreign key may be NULL; if any part of a composite foreign key is NULL, the entire foreign key is NULL.

Referential Integrity rules are stored in an Advantage Data Dictionary.
Note Referential Integrity is only supported with the Advantage proprietary tables.

Example

Lets look at a simple example using two tables: SALES_REPS and OFFICES. The following SQL statement is syntactically correct, and with the current state of our example database this statement would execute and add a new sales rep, "Doug Henry", who works in office number 45:

INSERT INTO SALES_REPS (EMPL_NUM, NAME, REP_OFFICE)

VALUES (69, ‘Doug Henry’, 45)

No validity checking has been enforced, and even if office number 45 does not exist in the OFFICES table, Doug Henry will still exist in our database.

To remedy this situation, we’ll define one RI rule linking the OFFICES.OFFICE field (as the primary key) to the SALES_REPS.REP_OFFICE field (as the foreign key). With this rule in place, the previous SQL statement would not execute without returning an error. Before adding Doug to the SALES_REPS table, the Advantage Database Server will first ensure that all foreign keys in this new row reference existing primary keys in their parent tables. Because office number 45 does not exist, the INSERT operation will fail. The application developer does not write any code to enforce this rule. The database server does all the work; the developer can simply catch this error, notify the user of the violation, and request correct data.

Riley K replied to aman on 05-Nov-11 02:22 AM


Referential integrity maintains the defined relationships between tables when records are entered or deleted.In SQL Server,referential integrity is based on relationships between foreign keys and primary keys/Unique keys(through FOREIGN KEY and CHECK constraints). 


dipa ahuja replied to aman on 05-Nov-11 04:11 AM

Referential integrity Referential integrity in a relational database is consistency between coupled tables. Referential integrity is usually enforced by the combination of a primary key or candidate key (alternate key) and a foreign key.

For referential integrity to hold, any field in a table that is declared a foreign key can contain only values from a parent table's primary key or a candidate key.

For instance, deleting a record that contains a value referred to by a foreign key in another table would break referential integrity.

A relational database management system (RDBMS) enforces referential integrity, normally either by deleting the foreign key rows as well to maintain integrity, or by returning an error and not performing the delete.

Which method is used would be determined by the referential integrity constraint, as defined in the data dictionary.

 

Sri K replied to aman on 05-Nov-11 05:26 AM

A feature provided by http://www.webopedia.com/TERM/R/RDBMS.html that prevents users or applications from entering inconsistent data. Most RDBMS's have various referential integrity rules that you can apply when you create a relationship between two http://www.webopedia.com/TERM/T/table.html.

For example, suppose Table B has a http://www.webopedia.com/TERM/K/key.html that points to a field in Table A. Referential integrity would prevent you from adding a http://www.webopedia.com/TERM/R/record.html to Table B that cannot be linked to Table A. In addition, the referential integrity rules might also specify that whenever you delete a record from Table A, any records in Table B that are linked to the deleted record will also be deleted. This is called cascading delete. Finally, the referential integrity rules could specify that whenever you modify the value of a linked http://www.webopedia.com/TERM/F/field.html in Table A, all records in Table B that are linked to it will also be modified accordingly. This is called cascading update.

Anoop S replied to aman on 05-Nov-11 06:32 AM
Referential integrity is a database concept that ensures that relationships between tables remain consistent. When one table has a foreign key to another table, the concept of referential integrity states that you may not add a record to the table that contains the foreign key unless there is a corresponding record in the linked table. It also includes the techniques known as cascading update and cascading delete, which ensure that changes made to the linked table are reflected in the primary table.

Consider the situation where we have two tables: Employees and Managers. The Employees table has a foreign key attribute entitled ManagedBy which points to the record for that employee’s manager in the Managers table. Referential integrity enforces the following three rules:

   1. We may not add a record to the Employees table unless the ManagedBy attribute poi
   2. nts to a valid record in the Managers table. If the primary key for a record in the Managers table changes, all corresponding records in the Employees table must be modified using a cascading update.
   3. If a record in the Managers table is deleted, all corresponding records in the Employees table must be deleted using a cascading delete.