Re: Guarenteeing complex referencial integrity through custom triggers

-----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 DELETE RESTRICT,
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) REFERENCES printers(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(at)turnstep(dot)com
PGP Key: 0x14964AC8 200703261429
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----

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