samiksc

 Profile Answers by samiksc 

• Jan 19th, 2006

 

Referential integrity is regarding two columns of two tables in a database. One of these columns serves as the parent column, while another is a sort of child column. The child column values form a subset of the parent column values.

For example deptno column in employees table must be the subset of values of deptno column in the departments table. Here departments table is the parent table and employess is the child table.

tej

• Apr 17th, 2006

 

how to cnnect to MySQL from VB  

Describe Referential Integrity ?
Answer:

A rule defined on a column (or set of columns) in one table that allows the insert or update of a row only if the value for the column or set of columns (the dependent value) matches a value in a column of a related table (the referenced value). It also specifies the type of data manipulation allowed on referenced data and the action to be performed on dependent data as a result of any action on referenced data.

lalita.p

• Sep 29th, 2006

 

Refrential integrity protect the relationship betn the tables during Insert,update and delete oprations.
User can anot able to insert any record into related table if no match record is found in primary table.

user can not delete any row from primary table if it result any orphand record in related table.

sivamurugan9182

 Profile Answers by sivamurugan9182 

• Dec 13th, 2007

 

Sorry i not here to give the answer.
But i just want to mention as i was a beginner in the learning of OracleDB,i was quite confused of
the answers given by the beloved forum members(not all).
Most of the answers given by them(not all) are quite confusing and not pertinent to the question asked.
Iam not finding fault as all have the right to give their views but please provide concretely as much as possible, or else comment by saying that ''it's your understanding and it may not be correct''.


Again a big sorry from this novice.
But please consider.

Regards
Siva.M

ddf_dba

 Profile Answers by ddf_dba 

• Dec 21st, 2007

 

Referential integrity is a relationship between two tables, one being the 'parent' and the other being the 'child'.  The 'child' table references values in the 'parent' table's primary key to ensure all data between the two tables is properly related.  Let's look at an example:

SQL> --
SQL> -- Create the EMP table
SQL> --
SQL> CREATE TABLE EMP
  2       (EMPNO NUMBER(4) NOT NULL,
  3        ENAME VARCHAR2(10),
  4        JOB VARCHAR2(9),
  5        MGR NUMBER(4),
  6        HIREDATE DATE,
  7        SAL NUMBER(7, 2),
  8        COMM NUMBER(7, 2),
  9        DEPTNO NUMBER(2),
 10        constraint emp_pk
 11        primary key(empno)
 12  );

Table created.

SQL>
SQL> INSERT ALL
  2  INTO EMP VALUES
  3        (7369, 'SMITH',  'CLERK',    7902,
  4        TO_DATE('17-DEC-1980', 'DD-MON-YYYY'),  800, NULL, 20)
  5  INTO EMP VALUES
  6        (7499, 'ALLEN',  'SALESMAN',  7698,
  7        TO_DATE('20-FEB-1981', 'DD-MON-YYYY'), 1600,  300, 30)
  8  INTO EMP VALUES
  9        (7521, 'WARD',   'SALESMAN',  7698,
 10        TO_DATE('22-FEB-1981', 'DD-MON-YYYY'), 1250,  500, 30)
 11  INTO EMP VALUES
 12        (7566, 'JONES',  'MANAGER',   7839,
 13        TO_DATE('2-APR-1981', 'DD-MON-YYYY'),  2975, NULL, 20)
 14  INTO EMP VALUES
 15        (7654, 'MARTIN', 'SALESMAN',  7698,
 16        TO_DATE('28-SEP-1981', 'DD-MON-YYYY'), 1250, 1400, 30)
 17  INTO EMP VALUES
 18        (7698, 'BLAKE',  'MANAGER',   7839,
 19        TO_DATE('1-MAY-1981', 'DD-MON-YYYY'),  2850, NULL, 30)
 20  INTO EMP VALUES
 21        (7782, 'CLARK',  'MANAGER',   7839,
 22        TO_DATE('9-JUN-1981', 'DD-MON-YYYY'),  2450, NULL, 10)
 23  INTO EMP VALUES
 24        (7788, 'SCOTT',  'ANALYST',   7566,
 25        TO_DATE('09-DEC-1982', 'DD-MON-YYYY'), 3000, NULL, 20)
 26  INTO EMP VALUES
 27        (7839, 'KING',   'PRESIDENT', NULL,
 28        TO_DATE('17-NOV-1981', 'DD-MON-YYYY'), 5000, NULL, 10)
 29  INTO EMP VALUES
 30        (7844, 'TURNER', 'SALESMAN',  7698,
 31        TO_DATE('8-SEP-1981', 'DD-MON-YYYY'),  1500,    0, 30)
 32  INTO EMP VALUES
 33        (7876, 'ADAMS',  'CLERK',    7788,
 34        TO_DATE('12-JAN-1983', 'DD-MON-YYYY'), 1100, NULL, 20)
 35  INTO EMP VALUES
 36        (7900, 'JAMES',  'CLERK',    7698,
 37        TO_DATE('3-DEC-1981', 'DD-MON-YYYY'),   950, NULL, 30)
 38  INTO EMP VALUES
 39        (7902, 'FORD',   'ANALYST',   7566,
 40        TO_DATE('3-DEC-1981', 'DD-MON-YYYY'),  3000, NULL, 20)
 41  INTO EMP VALUES
 42        (7934, 'MILLER', 'CLERK',    7782,
 43        TO_DATE('23-JAN-1982', 'DD-MON-YYYY'), 1300, NULL, 10)
 44  SELECT * FROM DUAL;

14 rows created.

SQL>
SQL> --
SQL> -- Create the DEPT table
SQL> --
SQL> CREATE TABLE DEPT
  2       (DEPTNO NUMBER(2),
  3        DNAME VARCHAR2(14),
  4        LOC VARCHAR2(13),
  5        constraint dept_pk
  6        primary key(deptno)
  7  );

Table created.

SQL>
SQL> INSERT ALL
  2  INTO DEPT VALUES (10, 'ACCOUNTING', 'NEW YORK')
  3  INTO DEPT VALUES (20, 'RESEARCH',  'DALLAS')
  4  INTO DEPT VALUES (30, 'SALES',  'CHICAGO')
  5  INTO DEPT VALUES (40, 'OPERATIONS', 'BOSTON')
  6  SELECT * FROM DUAL;

4 rows created.

SQL>
SQL> COMMIT;

Commit complete.

SQL>
SQL> --
SQL> -- Add a foreign key to the EMP
SQL> -- table to prevent unreferenced
SQL> -- department numbers from being
SQL> -- used
SQL> --
SQL> ALTER TABLE EMP
  2  ADD CONSTRAINT EMP_DEPT_FK
  3  FOREIGN KEY(DEPTNO) REFERENCES DEPT;

Table altered.

The two tables, EMP and DEPT, are now 'related' using a foreign key as a referential integrity constraint.  This will prevent any records from being inserted into the EMP table if the DEPTNO value doesn't exist in the DEPT table.  Let's illustrate this with another example:

SQL>
SQL> --
SQL> -- Attempt to add a new employee
SQL> -- with a new department
SQL> --
SQL> INSERT
  2  INTO EMP VALUES
  3        (7969, 'SMYTHE',  'CLERK',     7902,
  4        TO_DATE('17-DEC-1980', 'DD-MON-YYYY'),  800, NULL, 50);
INSERT
*
ERROR at line 1:
ORA-02291: integrity constraint (BING.EMP_DEPT_FK) violated - parent key not
found

As you can see the parent key (DEPTNO=50) doesn't exist in the DEPT table.  Thus, the child record cannot be added to the EMP table.

Another benefit of referential integrity is that any parent record associated with child records cannot be deleted from the parent table.  To illustrate:

SQL>
SQL> --
SQL> -- Attempt to delete a department
SQL> -- associated with employees
SQL> --
SQL> DELETE FROM DEPT
  2  WHERE DEPTNO = 30;
DELETE FROM DEPT
*
ERROR at line 1:
ORA-02292: integrity constraint (BING.EMP_DEPT_FK) violated - child record
found

SQL>

Since DEPTNO 30 is associated with at least one employee in the EMP table dropping this department is not allowed.

A foreign key also does not need to be restricted to a single column; the reference in the child table is to the entire primary key of the parent.  Modifying our previous example by adding the DNAME column to the EMP table and including it in the primary key and foreign key definitions we see:

SQL> --
SQL> -- Create the EMP table
SQL> --
SQL> CREATE TABLE EMP
  2       (EMPNO NUMBER(4) NOT NULL,
  3        ENAME VARCHAR2(10),
  4        JOB VARCHAR2(9),
  5        MGR NUMBER(4),
  6        HIREDATE DATE,
  7        SAL NUMBER(7, 2),
  8        COMM NUMBER(7, 2),
  9        DEPTNO NUMBER(2),
 10        DNAME VARCHAR2(14),
 11        constraint emp_pk
 12        primary key(empno)
 13  );

Table created.

SQL>
SQL> INSERT ALL
  2  INTO EMP VALUES
  3        (7369, 'SMITH',  'CLERK',    7902,
  4        TO_DATE('17-DEC-1980', 'DD-MON-YYYY'),  800, NULL, 20,'RESEARCH')
  5  INTO EMP VALUES
  6        (7499, 'ALLEN',  'SALESMAN',  7698,
  7        TO_DATE('20-FEB-1981', 'DD-MON-YYYY'), 1600,  300, 30, 'SALES')
  8  INTO EMP VALUES
  9        (7521, 'WARD',   'SALESMAN',  7698,
 10        TO_DATE('22-FEB-1981', 'DD-MON-YYYY'), 1250,  500, 30, 'SALES')
 11  INTO EMP VALUES
 12        (7566, 'JONES',  'MANAGER',   7839,
 13        TO_DATE('2-APR-1981', 'DD-MON-YYYY'),  2975, NULL, 20,'RESEARCH')
 14  INTO EMP VALUES
 15        (7654, 'MARTIN', 'SALESMAN',  7698,
 16        TO_DATE('28-SEP-1981', 'DD-MON-YYYY'), 1250, 1400, 30, 'SALES')
 17  INTO EMP VALUES
 18        (7698, 'BLAKE',  'MANAGER',   7839,
 19        TO_DATE('1-MAY-1981', 'DD-MON-YYYY'),  2850, NULL, 30, 'SALES')
 20  INTO EMP VALUES
 21        (7782, 'CLARK',  'MANAGER',   7839,
 22        TO_DATE('9-JUN-1981', 'DD-MON-YYYY'),  2450, NULL, 10, 'ACCOUNTING')
 23  INTO EMP VALUES
 24        (7788, 'SCOTT',  'ANALYST',   7566,
 25        TO_DATE('09-DEC-1982', 'DD-MON-YYYY'), 3000, NULL, 20,'RESEARCH')
 26  INTO EMP VALUES
 27        (7839, 'KING',   'PRESIDENT', NULL,
 28        TO_DATE('17-NOV-1981', 'DD-MON-YYYY'), 5000, NULL, 10, 'ACCOUNTING')
 29  INTO EMP VALUES
 30        (7844, 'TURNER', 'SALESMAN',  7698,
 31        TO_DATE('8-SEP-1981', 'DD-MON-YYYY'),  1500,    0, 30, 'SALES')
 32  INTO EMP VALUES
 33        (7876, 'ADAMS',  'CLERK',    7788,
 34        TO_DATE('12-JAN-1983', 'DD-MON-YYYY'), 1100, NULL, 20,'RESEARCH')
 35  INTO EMP VALUES
 36        (7900, 'JAMES',  'CLERK',    7698,
 37        TO_DATE('3-DEC-1981', 'DD-MON-YYYY'),   950, NULL, 30, 'SALES')
 38  INTO EMP VALUES
 39        (7902, 'FORD',   'ANALYST',   7566,
 40        TO_DATE('3-DEC-1981', 'DD-MON-YYYY'),  3000, NULL, 20,'RESEARCH')
 41  INTO EMP VALUES
 42        (7934, 'MILLER', 'CLERK',    7782,
 43        TO_DATE('23-JAN-1982', 'DD-MON-YYYY'), 1300, NULL, 10, 'ACCOUNTING')
 44  SELECT * FROM DUAL;

14 rows created.

SQL>
SQL> --
SQL> -- Create the DEPT table
SQL> --
SQL> CREATE TABLE DEPT
  2       (DEPTNO NUMBER(2),
  3        DNAME VARCHAR2(14),
  4        LOC VARCHAR2(13),
  5        constraint dept_pk
  6        primary key(deptno, dname)
  7  );

Table created.

SQL>
SQL> INSERT ALL
  2  INTO DEPT VALUES (10, 'ACCOUNTING', 'NEW YORK')
  3  INTO DEPT VALUES (20, 'RESEARCH',  'DALLAS')
  4  INTO DEPT VALUES (30, 'SALES',  'CHICAGO')
  5  INTO DEPT VALUES (40, 'OPERATIONS', 'BOSTON')
  6  SELECT * FROM DUAL;

4 rows created.

SQL>
SQL> COMMIT;

Commit complete.

SQL>
SQL> --
SQL> -- Add a foreign key to the EMP
SQL> -- table to prevent unreferenced
SQL> -- department numbers from being
SQL> -- used
SQL> --
SQL> ALTER TABLE EMP
  2  ADD CONSTRAINT EMP_DEPT_FK
  3  FOREIGN KEY(DEPTNO,DNAME) REFERENCES DEPT;

Table altered.

SQL>
SQL> --
SQL> -- Attempt to add a new employee
SQL> -- with an existing department
SQL> -- but the wrong department name
SQL> --
SQL> INSERT
  2  INTO EMP VALUES
  3        (7969, 'SMYTHE',  'CLERK',     7902,
  4        TO_DATE('17-DEC-1980', 'DD-MON-YYYY'),  800, NULL, 40, 'MARKETING');
INSERT
*
ERROR at line 1:
ORA-02291: integrity constraint (BING.EMP_DEPT_FK) violated - parent key not
found

Notice that department 40 does exist in the DEPT table, but it's named 'OPERATIONS', not 'MARKETING', thus the insert failure.

SQL>

Referential integrity ensures the quality of the data in a database, since orphan records cannot exist either through erroneous inserts into the child table or through inadvertent deletes from the parent table.