Path: dp-news.maxwell.syr.edu!spool.maxwell.syr.edu!drn.maxwell.syr.edu!news.maxwell.syr.edu!postnews.google.com!f14g2000cwb.googlegroups.com!not-for-mail
From: "Tony Andrews" <andrewst@onetel.com>
Newsgroups: comp.databases.theory
Subject: Re: Question about modeling
Date: 25 Jan 2005 08:18:36 -0800
Organization: http://groups.google.com
Lines: 31
Message-ID: <1106669916.668857.102480@f14g2000cwb.googlegroups.com>
References: <CZtJd.30725$Qb.29245@edtnps89>
NNTP-Posting-Host: 194.131.250.1
Mime-Version: 1.0
Content-Type: text/plain; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
X-Trace: posting.google.com 1106669936 27739 127.0.0.1 (25 Jan 2005 16:18:56 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Tue, 25 Jan 2005 16:18:56 +0000 (UTC)
User-Agent: G2/0.2
Complaints-To: groups-abuse@google.com
Injection-Info: f14g2000cwb.googlegroups.com; posting-host=194.131.250.1;
   posting-account=IJrbaQwAAACC57cNvnaNWPkLUkH3QWCJ
Xref: dp-news.maxwell.syr.edu comp.databases.theory:29591

Mathieu Pag=E9 wrote:
> Hi, I have this model:
>
> DEPARTMENTS	(dp_id, cl_id, dp_otherStuff)
> CLIENT		(cl_id, cl_otherStuff)
> CONTRACT	(cn_id, cn_details)
> DEPTS_CONTRACT_LINK	(dp_id, cn_id)
>
> Now I want to add this constraint : As I have said each contract can
be
> associated with one or more departments, but I want that all those
> departments belongs to the same client. In other words, departments
can
> share contract, but clients can not.
>
> How can I do this ?

What you really want here is a constraint like:

CHECK (NOT EXISTS
(SELECT dc.cn_id
FROM depts_contract_link dc
JOIN departments d ON d.dp_id =3D dc.dp_id
GROUP BY dc.cn_id
HAVING COUNT(DISTINCT d.cl_id) > 1
));

But I don't suppose Access supports anything so complex.  This is
probably something that you will unfortunately have to enforce via
application code.