Re: Question about modeling
From: Mathieu Pagé <informatique_remove_this__at_csfinc.com>
Date: Tue, 25 Jan 2005 16:55:36 GMT
Message-ID: <cSuJd.34528$Qb.28662_at_edtnps89>
>
> be
>
>
> can
>
>
>
> 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 = 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.
>
Date: Tue, 25 Jan 2005 16:55:36 GMT
Message-ID: <cSuJd.34528$Qb.28662_at_edtnps89>
Tony Andrews a écrit :
> Mathieu Pagé 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 = 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.
>
Hi Tony, thank you for your quick answer.
I also first tought that access would not support such a constraint, but it seems it do, I did not test it, since I have no data in my DB, but it did not raise an error.
So... problem (probably) solved !
Again, thank you. Received on Tue Jan 25 2005 - 17:55:36 CET