Path: utzoo!attcan!utgpu!jarvis.csri.toronto.edu!mailrus!tut.cis.ohio-state.edu!ucbvax!mtxinu!rtech!squid!robf
From: robf@squid.rtech.com (Robert Fair)
Newsgroups: comp.databases
Subject: Re: Ingres and referential integrities
Keywords: Ingres referential integrities
Message-ID: <3225@rtech.rtech.com>
Date: 24 Jul 89 17:47:16 GMT
References: <3081@rti.UUCP>
Sender: news@rtech.rtech.com
Reply-To: robf@squid.UUCP (Robert Fair)
Distribution: comp
Organization: Relational Technology, Inc.
Lines: 60

Lynn writes:
>I'm curious as to why Ingres doesn't provide referential integrities.
>I'm aware that you can build in referential constraints through QBF
>and VIFRED but not as part of the data definition. Does anyone know
>why Ingres chose not to provide this part of the relational model?
>
>Thanks,
>Lynn

If you mean single-table constraints, INGRES has  had them for many
years, for example:

	CREATE INTEGRITY ON employee IS age>15 AND  age<75;

will enforce the integrity that all employees are between 15 and 75.

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;

would ensure the only updates to the "emp" table come through database
procedures, so providing proper referential integrity. Similar procedures
can be defined to insert & update data in tables. dbprocs can be called
from ESQL and ABF/4GL.

Robert Fair
Relational Technology 
Technical Support.