Recursive relationship - preventing cross-index entries.

["Andrew Maclean" <andrew.amaclean@xxxxxxxxx>]

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 can easily prevent entries like (5,3) Ok see : id_ref_pair_idx below or Id==Ref entries like 2,2.

 

 

Here is a dump of the database if you want to experiment:

----------------------------------------------------------------------------------

--
-- PostgreSQL database dump
--

-- Started on 2007-06-08 13:42:30

SET client_encoding = 'UTF8';
SET standard_conforming_strings = off;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET escape_string_warning = off;

--
-- TOC entry 1619 (class 1262 OID 16821)
-- Dependencies: 1618
-- Name: Test; Type: COMMENT; Schema: -; Owner: postgres
--

COMMENT ON DATABASE "Test" IS 'Test database.';

--
-- TOC entry 1620 (class 0 OID 0)
-- Dependencies: 4
-- Name: SCHEMA public; Type: COMMENT; Schema: -; Owner: postgres
--

COMMENT ON SCHEMA public IS 'Standard public schema';

--
-- TOC entry 265 (class 2612 OID 16389)
-- Name: plperl; Type: PROCEDURAL LANGUAGE; Schema: -; Owner: postgres
--

CREATE PROCEDURAL LANGUAGE plperl;

--
-- TOC entry 264 (class 2612 OID 16386)
-- Name: plpgsql; Type: PROCEDURAL LANGUAGE; Schema: -; Owner: postgres
--

CREATE PROCEDURAL LANGUAGE plpgsql;

--
-- TOC entry 266 (class 2612 OID 16391)
-- Name: pltcl; Type: PROCEDURAL LANGUAGE; Schema: -; Owner: postgres
--

CREATE PROCEDURAL LANGUAGE pltcl;

SET search_path = public, pg_catalog;

SET default_tablespace = '';

SET default_with_oids = false;

--
-- TOC entry 1273 (class 1259 OID 16862)
-- Dependencies: 1606 4
-- Name: Adjustments; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
--

CREATE TABLE "Adjustments" (
    id integer NOT NULL,
    ref integer NOT NULL,
    CONSTRAINT "Check01" CHECK ((id <> ref))
);

ALTER TABLE public."Adjustments" OWNER TO postgres;

--
-- TOC entry 1622 (class 0 OID 0)
-- Dependencies: 1273
-- Name: TABLE "Adjustments"; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON TABLE "Adjustments" IS 'Used to correct for double counting on Payments';

--
-- TOC entry 1623 (class 0 OID 0)
-- Dependencies: 1273
-- Name: COLUMN "Adjustments".id; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN "Adjustments".id IS 'The Id that we have to correct the payment on.';

--
-- TOC entry 1624 (class 0 OID 0)
-- Dependencies: 1273
-- Name: COLUMN "Adjustments".ref; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN "Adjustments".ref IS 'The id that is used to correct the payment.';

--
-- TOC entry 1625 (class 0 OID 0)
-- Dependencies: 1273
-- Name: CONSTRAINT "Check01" ON "Adjustments"; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON CONSTRAINT "Check01" ON "Adjustments" IS 'An Id cannot be the same as a ref.';

--
-- TOC entry 1272 (class 1259 OID 16824)
-- Dependencies: 1605 4
-- Name: Payments; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
--

CREATE TABLE "Payments" (
    "Id" integer NOT NULL,
    "Payment" numeric(18,1) DEFAULT 0.0
);

ALTER TABLE public."Payments" OWNER TO postgres;

--
-- TOC entry 1271 (class 1259 OID 16822)
-- Dependencies: 1272 4
-- Name: Payments_Id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE "Payments_Id_seq"
    START WITH 1
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;

ALTER TABLE public."Payments_Id_seq" OWNER TO postgres;

--
-- TOC entry 1626 (class 0 OID 0)
-- Dependencies: 1271
-- Name: Payments_Id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE "Payments_Id_seq" OWNED BY "Payments"."Id";

--
-- TOC entry 1627 (class 0 OID 0)
-- Dependencies: 1271
-- Name: Payments_Id_seq; Type: SEQUENCE SET; Schema: public; Owner: postgres
--

SELECT pg_catalog.setval('"Payments_Id_seq"', 1, false);

--
-- TOC entry 1604 (class 2604 OID 16826)
-- Dependencies: 1272 1271 1272
-- Name: Id; Type: DEFAULT; Schema: public; Owner: postgres
--

ALTER TABLE "Payments" ALTER COLUMN "Id" SET DEFAULT nextval('"Payments_Id_seq"'::regclass);

--
-- TOC entry 1615 (class 0 OID 16862)
-- Dependencies: 1273
-- Data for Name: Adjustments; Type: TABLE DATA; Schema: public; Owner: postgres
--

COPY "Adjustments" (id, ref) FROM stdin;
1 2
3 4
1 6
3 5
\.

--
-- TOC entry 1614 (class 0 OID 16824)
-- Dependencies: 1272
-- Data for Name: Payments; Type: TABLE DATA; Schema: public; Owner: postgres
--

COPY "Payments" ("Id", "Payment") FROM stdin;
1 500.0
2 100.0
3 1000.0
4 50.0
5 750.0
6 50.0
7 800.0
8 1200.0
\.

--
-- TOC entry 1608 (class 2606 OID 16829)
-- Dependencies: 1272 1272
-- Name: Payments_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace:
--

ALTER TABLE ONLY "Payments"
    ADD CONSTRAINT "Payments_pkey" PRIMARY KEY ("Id");

--
-- TOC entry 1611 (class 2606 OID 16866)
-- Dependencies: 1273 1273 1273
-- Name: id_ref_pk; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace:
--

ALTER TABLE ONLY "Adjustments"
    ADD CONSTRAINT id_ref_pk PRIMARY KEY (id, ref);

--
-- TOC entry 1609 (class 1259 OID 16878)
-- Dependencies: 1273 1273
-- Name: id_ref_pair_idx; Type: INDEX; Schema: public; Owner: postgres; Tablespace:
--

CREATE UNIQUE INDEX id_ref_pair_idx ON "Adjustments" USING btree ((CASE WHEN (id > ref) THEN ARRAY[id, ref] ELSE ARRAY[ref, id] END)) WITH (fillfactor=100);

--
-- TOC entry 1612 (class 2606 OID 16867)
-- Dependencies: 1272 1607 1273
-- Name: id_Payments_id_fk; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY "Adjustments"
    ADD CONSTRAINT "id_Payments_id_fk" FOREIGN KEY (id) REFERENCES "Payments"("Id");

--
-- TOC entry 1613 (class 2606 OID 16872)
-- Dependencies: 1607 1273 1272
-- Name: ref_Payments_id_fk; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY "Adjustments"
    ADD CONSTRAINT "ref_Payments_id_fk" FOREIGN KEY (ref) REFERENCES "Payments"("Id");

--
-- TOC entry 1621 (class 0 OID 0)
-- Dependencies: 4
-- Name: public; Type: ACL; Schema: -; Owner: postgres
--

REVOKE ALL ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON SCHEMA public FROM postgres;
GRANT ALL ON SCHEMA public TO postgres;
GRANT ALL ON SCHEMA public TO PUBLIC;

-- Completed on 2007-06-08 13:42:30

--
-- PostgreSQL database dump complete
--

 

-- 

___________________________________________
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.cas.edu.au/
___________________________________________