-----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 [EMAIL PROTECTED] PGP Key: 0x14964AC8 200703261429 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -----BEGIN PGP SIGNATURE----- iD8DBQFGCBJ/vJuQZxSWSsgRA16BAJ4hkfcY4ui+yLUGWNerHZf0FvRbPACg++X5 e4tmrrJ1BFcxjM3PCXyKP6Y= =CDAM -----END PGP SIGNATURE----- ---------------------------(end of broadcast)--------------------------- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match