Re: Referential Integrity Enforcement: Do With Constraints or Triggers?

From: Marc Blum <marc_at_marcblum.de>
Date: Sat, 27 Jul 2002 13:46:53 +0200
Message-ID: <gv15kuo551p7uv2qpcpseo2lb9104fbk6l@4ax.com>

On Fri, 26 Jul 2002 17:55:57 -0400, internetmaster <youlove_at_me.com> wrote:

>In an earlier thread I mentioned the following observation:
>
>http://groups.google.com/groups?dq=&hl=en&lr=&ie=UTF-8&oe=UTF-8&threadm=a20d28ee.0207260158.3ecabf94%40posting.google.com&prev=/groups%3Fhl%3Den%26lr%3D%26ie%3DUTF-8%26oe%3DUTF-8%26group%3Dcomp.databases.oracle.tools
>
>
>I was wrong about one thing. The database does have primary keys
>defined, but no foreign keys. I went browsing through the data
>dictionary and found that the database has a ton of Triggers. It appears
>they're using Triggers to enforce the referential integrity rather than
>using constraints.
>
>They have Insert triggers on the parent tables that handle cascade
>deletes and prevent illegal inserts on child tables where they key value
>on the parent table doesn't exist.
>
>My question is it less expensive to handle the RI this way? I can see
>where creating a ton of constraints would get to be cumbersome and
>perhaps Triggers would be easier from a maintenance perspective.
>
>For example, there is one parent Table (Project) that has about 15 child
>tables associated to it logically. The developers of this application
>created 2 triggers to handle the Insert and Delete anomalies whereas 30
>constraints would had to have been coded.
>
> From a performance perspective, what are the ramifications for this
>design decision? How expensive is a constraint compared to a Trigger?
>
>
>Thanks.

Just try it out on your own:

SQL> DROP TABLE ch;

Table dropped.

SQL> DROP TABLE p;

Table dropped.

SQL>
SQL> CREATE TABLE p
  2 AS
  3 SELECT * FROM dba_objects
  4 WHERE 1 = 2; Table created.

SQL>
SQL> ALTER TABLE p
  2 ADD CONSTRAINT pk
  3 PRIMARY KEY (object_id);

Table altered.

SQL>
SQL> CREATE TABLE ch
  2 AS
  3 SELECT * FROM dba_objects
  4 WHERE 1=2; Table created.

SQL>
SQL> CREATE TRIGGER trg

  2     BEFORE INSERT ON ch
  3     FOR EACH ROW
  4  DECLARE
  5     l_pcount PLS_INTEGER;
  6  BEGIN
  7     SELECT COUNT(*) 
  8       INTO l_pcount
  9       FROM p
 10      WHERE p.object_id = :NEW.object_id;
 11     --
 12     IF l_pcount = 0 THEN
 13        DBMS_OUTPUT.PUT_LINE('Parent missing: object_id:

'||:NEW.object_id||', count: '||l_pcount );

 14     ELSIF l_pcount = 1 THEN
 15        NULL;
 16     ELSIF l_pcount > 1 THEN
 17        DBMS_OUTPUT.PUT_LINE('To many Parent: object_id:

'||:NEW.object_id||', count: '||l_pcount );

 18     END IF;
 19     --

 20 END;
 21 /

Trigger created.

SQL> 
SQL> 
SQL> INSERT INTO p

  2 SELECT * FROM dba_objects;

2950 rows created.

SQL>
SQL> COMMIT; Commit complete.

SQL> 
SQL> SET TIMING ON
SQL> SET SERVEROUTPUT ON
SQL> 
SQL> INSERT INTO ch

  2 SELECT * FROM dba_objects;

2950 rows created.

Elapsed: 00:00:02.03

SQL> 
SQL> SET TIMING OFF
SQL> COMMIT;

Commit complete.

SQL>
SQL> DROP TRIGGER trg;

Trigger dropped.

SQL>
SQL> TRUNCATE TABLE ch;

Table truncated.

SQL>
SQL> ALTER TABLE ch
  2 ADD CONSTRAINT fk
  3 FOREIGN KEY (object_id)
  4 REFERENCES p;

Table altered.

SQL> 
SQL> SET TIMING ON
SQL> SET SERVEROUTPUT ON
SQL> 
SQL> INSERT INTO ch

  2 SELECT * FROM dba_objects;

2949 rows created.

Elapsed: 00:00:00.31

SQL> 
SQL> SET TIMING OFF
SQL> 
SQL> COMMIT;

Commit complete.

SQL> Marc Blum
mailto:marc_at_marcblum.de
http://www.marcblum.de Received on Sat Jul 27 2002 - 06:46:53 CDT