Re: Guarenteeing complex referencial integrity through custom triggers - Mailing list pgsql-hackers

-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160


> As a real-world example where the constraint cannot be enforced in
> postgresql.
>
> "For every tuple t in cartridge_change, there must exists a tuple t' in
> printers with t.id = t'.id, and a tuple t'' in cartridge_types with
> t.color = t''.color and t'.printertype = t''.printertype"

While there may be unenforceable constraints, unless I am misreading your 
example, I don't see a problem with enforcing this one. To restate 
your problem, a company has a finite number of printers, and tracks when 
a printer cartridge is changed. Each printer is of a certain type, and 
each type has one or more types of cartridges that can go with it. Thus:

- -- Generic type of printer
CREATE TABLE printer ( id    SERIAL NOT NULL PRIMARY KEY, brand TEXT   NOT NULL, model TEXT   NOT NULL
);
INSERT INTO printer (brand,model) VALUES ('epson','1200');
INSERT INTO printer (brand,model) VALUES ('hp','laserjet99');

- -- A printer can hold one or more cartridges, distinguished by 'color'
CREATE TABLE cartridge_types ( ptype INT  NOT NULL,   CONSTRAINT cc_ptype FOREIGN KEY (ptype) REFERENCES printer(id) ON
DELETERESTRICT, color TEXT NOT NULL
 
);
CREATE UNIQUE INDEX ctype ON cartridge_types(ptype,color);
INSERT INTO cartridge_types VALUES (1,'black');
INSERT INTO cartridge_types VALUES (2,'black');
INSERT INTO cartridge_types VALUES (2,'color');

- -- Specific printers in the company
CREATE TABLE printers ( id        SERIAL  NOT NULL PRIMARY KEY, ptype     INTEGER NOT NULL,   CONSTRAINT ptype FOREIGN
KEY(ptype) REFERENCES printer(id), location  TEXT    NOT NULL
 
);
INSERT INTO printers(ptype,location) VALUES (1,'Room 234');
INSERT INTO printers(ptype,location) VALUES (2,'Break room #2');
INSERT INTO printers(ptype,location) VALUES (2,'NE corner of warehouse');

- -- Printers require lots of pesky hardware updates
CREATE TABLE cartridge_change ( printer_id INT  NOT NULL,   CONSTRAINT change_printer FOREIGN KEY (printer_id)
REFERENCESprinters(id), color      TEXT NOT NULL, whenchanged TIMESTAMPTZ NOT NULL DEFAULT now()
 
);
ALTER TABLE cartridge_change ADD CONSTRAINT cc FOREIGN KEY (printer_id, color) REFERENCES cartridge_types
(ptype,color);

INSERT INTO cartridge_change (printer_id, color) VALUES (1,'black');
INSERT INTO cartridge_change (printer_id, color) VALUES (1,'blue');

- -- Session 1:
- -- BEGIN;
- -- INSERT INTO cartridge_change (printer_id, color) VALUES (2,'color');

- -- Session 2:
- -- BEGIN;
- -- DELETE FROM cartridge_types WHERE ptype=2 AND color = 'color';
- -- <blocks>

- -- Session 1:
- -- COMMIT;

- -- Session 2:
- -- ERROR


- -- Session 1:
- -- BEGIN;
- -- DELETE FROM cartridge_types WHERE ptype=2 AND color = 'color';

- -- Session 2:
- -- BEGIN;
- -- INSERT INTO cartridge_change (printer_id, color) VALUES (2,'color');
- -- <blocks>

- -- Session 1:
- -- COMMIT;

- -- Session 2:
- -- ERROR


- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200703261429
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----

iD8DBQFGCBJ/vJuQZxSWSsgRA16BAJ4hkfcY4ui+yLUGWNerHZf0FvRbPACg++X5
e4tmrrJ1BFcxjM3PCXyKP6Y=
=CDAM
-----END PGP SIGNATURE-----