Date: Wed, 5 Nov 2003 22:40:41 +0100
To: pgsql-sql@postgresql.org
Subject: avoid circular references
Message-ID: <20031105214041.GC13937@mail.librelogiciel.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
User-Agent: Mutt/1.5.4i
From: Jerome Alet <alet@librelogiciel.com>

Hi there,

I've got a table 'objtable' of objects which primary id is 'idobj' of type
serial.

objtable :

        idobj      objname
        1          nameone
        2          nametwo 
        3          namethree
        ...
        
        
Each object can contain one or many other objects from the same
table, so I guess this relationship can be expressed with something
like :

  CREATE TABLE contains (id SERIAL PRIMARY KEY NOT NULL,
    containerid INTEGER REFERENCES objtable(idobj),
      contentid INTEGER REFERENCES objtable(idobj) );
      
What I would like is to be able to avoid circular references :      

        - an object can't contain itself.
        
        - an object can't contain one of its containers.

So I'd like to know how to create additionnal integrity constraints 
to solve this problem, and especially what syntax I should use. 

I thought about creating a rule but maybe the best is a trigger
(I'm not sure I really understand the difference) if I see
what happens when I create new tables. But what can I do in
my trigger to have PostgreSQL understand there's an integrity
violation ?

Thanks in advance for any help on this.

Jerome Alet

-- 
"A non-free program is a predatory social system that keeps people 
in a state of domination and division, and uses the spoils to 
dominate more." - RMS