Received: from maia.hub.org (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 85BA2633DF8 for ; Wed, 3 Mar 2010 11:02:41 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 34513-05 for ; Wed, 3 Mar 2010 15:02:30 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from sargon.lncsa.com (mail.lncsa.com [213.215.28.11]) by mail.postgresql.org (Postfix) with ESMTP id 1C56A632FDD for ; Wed, 3 Mar 2010 11:02:31 -0400 (AST) Received: from localhost (localhost.localdomain [127.0.0.1]) by sargon.lncsa.com (Postfix) with ESMTP id C16BF300E01C for ; Wed, 3 Mar 2010 16:02:28 +0100 (CET) X-Virus-Scanned: Debian amavisd-new at lncsa.com Received: from sargon.lncsa.com ([127.0.0.1]) by localhost (sargon.lncsa.com [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 79g6Mudebc1e for ; Wed, 3 Mar 2010 16:02:28 +0100 (CET) Received: from zenon.apartia.fr (zenon.apartia.fr [10.0.3.1]) (using TLSv1 with cipher DHE-RSA-AES256-SHA (256/256 bits)) (Client CN "zenon.apartia.fr", Issuer "ca.apartia.fr" (verified OK)) by sargon.lncsa.com (Postfix) with ESMTP id A2817300E35F for ; Wed, 3 Mar 2010 16:02:28 +0100 (CET) Received: from galba.apartia.fr (galba.apartia.fr [10.0.3.119]) by zenon.apartia.fr (Postfix) with ESMTP id 4B15C58A0DA2B for ; Wed, 3 Mar 2010 16:02:28 +0100 (CET) Received: by galba.apartia.fr (Postfix, from userid 1000) id EF3C41D42C; Wed, 3 Mar 2010 16:02:27 +0100 (CET) Date: Wed, 3 Mar 2010 16:02:27 +0100 From: Louis-David Mitterrand To: pgsql-sql@postgresql.org Subject: check constraint on multiple tables? Message-ID: <20100303150227.GA18661@apartia.fr> Mail-Followup-To: pgsql-sql@postgresql.org MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline User-Agent: Mutt/1.5.20 (2009-06-14) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-0.74 tagged_above=-10 required=5 tests=BAYES_20=-0.74 X-Spam-Level: X-Archive-Number: 201003/12 X-Sequence-Number: 33719 Hi, I've got this chain of tables: ship --> (id_ship) --> cabin_type --> (id_cabin_type) --> cabin_category --> (id_cabin_category) --> cabin The 'cabin' table has (cabin_number, id_cabin_category ref. cabin_category) How can I guarantee unicity of cabin_number per ship? For now I added a unique(cabin_number,id_cabin_category) but this does not guarantee unicity for (cabin_number,ship.id_ship). What is the best solution? Adding an id_ship to 'cabin'? Or check'ing with a join down to 'ship'? (if possible). Thanks,