Path: utzoo!utgpu!jarvis.csri.toronto.edu!mailrus!ames!apple!voder!cullsj!gupta
From: gupta@cullsj.UUCP (Yogesh Gupta)
Newsgroups: comp.databases
Subject: Re: Ingres and referential integrities
Summary: Relational DBMSs can (and do) support referential integrity
	 efficiently!
Keywords: Ingres referential integrities
Message-ID: <651@cullsj.UUCP>
Date: 26 Jul 89 18:38:26 GMT
References: <3081@rti.UUCP> <3225@rtech.rtech.com> <9677@alice.UUCP>
Distribution: comp
Organization: Cullinet Software, San Jose, CA
Lines: 101

In article <9677@alice.UUCP>, debra@alice.UUCP (Paul De Bra) writes:
> [...]
> Then referential integrity (given EMPNO is a key in EMP and DNO a key in
> DEPT) would imply that EMP cannot contain a DNO which does not occur in
> DEPT.

Referential integrity:
If DNO is the primary key in DEPT and EDNO is the foreign key in the
EMP table that refers to DNO in DEPT, then EMP.EDNO can contain no non-NULL
value that does not exist in DEPT.DNO.

Note that the above only defines what a property of the database to maintain
consistency.  However, it says nothing about what action to take in case of
an attempted violation.  Let us say that you are trying to delete a row
from DEPT which has some rows in EMP referring to it.  The following actions
are possible:
	Disallow the delete (RESTRICT).
	Delete the rows from EMP as well (CASCADE).
	Set the EDNO value for those rows in EMP to NULL (SET NULL).
	Set the EDNO value for those rows in EMP to NULL some default value
	    (SET DEFAULT).

Any of these actions maintains consistency in the database.  It should be up
to the DBA to define these.

> There should be no need for procedures or other low-level tricks to
> enforce this constraint.
> 

I wholeheartedly agree with the above statement.  If you write procedures,
you have just eliminated ALL the flexibility of SQL.  Using Robert Fair's
example:
In article <3225@rtech.rtech.com>, robf@squid.rtech.com (Robert Fair) writes:

>Starting with release 6, multi-table update integrity can be enforced by
>using database procedures, typically linked updates are coded using
>dbprocs similar to:
>
>	CREATE PROCEDURE del_emp (emp_no=integer) AS
>	BEGIN
>		DELETE FROM emp
>		WHERE  emp_no= :emp_no;
>
>		IF iirowcount!=1 THEN
>			MESSAGE 'Error deleting employee';
>			ROLLBACK;
>			RETURN  0;
>		ENDIF;
>
>		DELETE FROM emp_info
>		WHERE  emp_no= :emp_no
>
>		IF iirowcount!=1 THEN
>			MESSAGE 'Error deleting emp_info';
>			ROLLBACK;
>			RETURN  0;
>		ENDIF;
>
>		COMMIT;
>		RETURN 1;
>	END;
>
>The trick here to ensure integrity is that permits can be granted to 
>execute procedures accessing base tables, even when there is no permission 
>on the base table itself, e.g:
>
>	GRANT SELECT ON emp TO PUBLIC;
>	GRANT SELECT ON emp_info TO PUBLIC;
>	GRANT EXECUTE ON PROCEDURE del_emp TO PUBLIC;

Note that at this point all the power of SQl is lost!  The only criterion
usable in the where clause is empno = :x!  For example, I can not say:

	DELETE from emp where performance = poor;

A much more flexible implementation would be defining the referential
integrity in the declarative form, say:

	create table emp_info (empno integer
			(refers to emp.empno on delete CASCADE), ...);

The above is just an example of syntax that would specify the action
to be taken, and not how.  Also, it would not matter how you deleted
a row from the emp table, the DBMS would guarantee the deletetion of
the appropriate row from emp_info.

> In a hierarchical database the link between the DNO's in EMP and in DEPT
> would come natural, and referential integrity is therefore easy.
> In a relational database there is no automatic link between the two
> DNO's which makes it harder to implement referential integrity.
>
> Paul.

This is an interesting myth.  Just to cite a counter example, Cullinet's
Enterprise:DB supports referntial integrity WITHOUT any physycal links.
And it performs very well (unfortunately, there are no referential
integrity benchmarks around).

Also, there is nothing that prevents a DBMS from internally maintaining
links, as long as those are NOT VISIBLE AND NOT NECESSARY for the
functionality, and only a performance enhancement.