Thanks to you all for all your help and comments.
I finally ended up creating a trigger to check the constraints. This has the added benefit that more than one constraint can be checked in the one trigger.
As to whether it is the best model or not for what I want to do. This question is more difficult to answer, but after giving it a lot of thought, I think it is, mainly on the grounds of elegance and convenience. However when I construct a GUI all may change!
Thankyou all for your input.
On 6/20/07, Sean Davis <sdavis2@mail.nih.gov> wrote:Andrew Maclean wrote:
> I got no answer so I am trying again.
>
> In a nutshell, if I have a recrusive relationship as outlined below, how
> do I implement a rule for the adjustments table that prevents the entry
> of an Id into the Ref column if the id exists in the Id column and vice
> versa?
>
> If I have a payments table which holds an Id and a payment and I also
> have an adjustments table that holds a payment id and a reference id so
> that adjustments can be made to payments.
> So the payments table looks like this:
> Id Payment
> 1 500.0
> 2 100.0
> 3 1000.0
> 4 50.0
> 5 750.0
> 6 50.0
> 7 800.0
> 8 1200.0
>
> and the adjustments table looks like this:
> Id Ref
> 1 2
> 3 4
> 1 6
> 3 5
> The idea is that, if for example Id=1 is a credit dard payment, then
> entries 2 and 6 could be payments that are already included in the
> credit card payment so we need to adjust the total payment to take this
> into account.
>
> This means that the payment for Id=1 ($500) in the payments table needs
> to be reduced by $100 (ref=2) and $50 (ref=6) , similarly, the payment
> for Id=3 ($1000) needs to be reduced by $850). So the question is:
>
> How do I design the adjustments table to ensure that:
> a) For any value entered in the Id column a check should occur to
> ensure that it does not exist in the Ref column.
> b) For any value entered in the Ref column, a check should occur to
> ensure that it does not exist in the Id column.
>
> In other words, looking at the adjustments table, I should be
> prevented from entering 2,4,6,5 in the Id column and 1, 3 in the Ref
> column.
I th8ink you can put a trigger on the table that can check the
constraints.
http://www.postgresql.org/docs/8.2/static/triggers.html
However, I wonder whether it might not make more sense to go with an
account system, with an account balance and credits and debits to the
account. Is the system you are proposing really the best data model?
Sean
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly
--
___________________________________________
Andrew J. P. Maclean
Centre for Autonomous Systems
The Rose Street Building J04
The University of Sydney 2006 NSW
AUSTRALIA
Ph: +61 2 9351 3283
Fax: +61 2 9351 7474
URL:
http://www.acfr.usyd.edu.au/ ___________________________________________