>
> On Mon, Oct 25, 2010 at 15:11, Peter Eisentraut <pete...@gmx.net> wrote:
>
>> Example #4: PK is period, FK is timestamp. FK must be contained in some
>> PK period.
>>
>> CREATE TABLE pk (a period PRIMARY KEY, ...);
>>
>> CREATE TABLE fk (x timestamp REFERENCES pk (a), ...);
>>
>> As above, we can probably arrange the operator knowledge to make these
>> checks. But I think additionally, you'd need an exclusion constraint on
>> the PK side to ensure nonoverlapping arrays/periods so that on
>> update/delete restrict as well as cascading deletes work.
>>
>
> Additional interesting examples involve IP network containment using
>> inet/cidr or ip4/ip4r. There, you'd probably need additional syntax to
>> tell the system explicitly which operators to use.
>>
>
> There are a large number of use-cases for this type of foreign key with
> geometry ( PostGIS ) types as well. Point references Area or Line, Area
> references Area, etc.
>
You may be interested in an experiment I did late last year, where I did a
bit of playing around in the system catalogs to create this kind of
relationship. Turns out that the RI infrastructure stores the oid of the
equality operators it uses in pg_constraint, and after creating a normal
foreign key constraint it can be updated to change these operators. Of
course, Bad Things can probably happen if you violate assumptions that the
RI code depends on. I suspect that the most important one is that a child
row must reference exactly one parent row.
For establishing a point-contained-in-box relationship, the parent-eq-parent
operator is BOX && BOX and the child-eq-parent operator is POINT <@ BOX.
The parent-eq-child operator is BOX @> POINT, which is the commutator of
child-eq-parent. Declaring an exclusion constraint on the parent column
using the && operator guarantees that the <@ operator can only match a
single parent row.
If we're able to teach Postgres about these operator relationships -- that
is, && combined with <@ satisfies the restriction that a child row can only
reference one parent row -- extending the RI creation code to support this
kind of a relationship looks to be fairly straightforward.
(I've attached a small .sql script which demonstrates this for the POINT <@
BOX case.)
-Andrew
-- relational integrity hacking: attempting to create non-equality FK
-- relationships.
-- parent relation has (non-overlapping) BOX keys, child relation has POINT
-- keys.
BEGIN;
DROP SCHEMA IF EXISTS rihack CASCADE;
CREATE SCHEMA rihack;
SET search_path TO rihack, public;
CREATE DOMAIN INTBOX AS BOX CHECK (
(VALUE[0])[0] = ROUND((VALUE[0])[0]) AND
(VALUE[0])[1] = ROUND((VALUE[0])[1]) AND
(VALUE[1])[0] = ROUND((VALUE[1])[0]) AND
(VALUE[1])[1] = ROUND((VALUE[1])[1])
);
CREATE DOMAIN INTPOINT AS POINT CHECK (
VALUE[0] = ROUND(VALUE[0]) AND VALUE[1] = ROUND(VALUE[1])
);
CREATE TABLE boxes (
id SERIAL PRIMARY KEY,
region INTBOX NOT NULL,
name TEXT NOT NULL,
CONSTRAINT boxes_cannot_overlap EXCLUDE USING gist (region WITH &&)
);
CREATE TABLE points (
region_id INTEGER REFERENCES boxes,
poi INTPOINT NOT NULL,
score INTEGER NOT NULL
);
-- Now, what we want to do is define an FK relationship:
-- FOREIGN KEY points.poi REFERENCES boxes.region WITH <@
-- We're going to hijack the existing constraint on the points table:
-- FOREIGN KEY points.region_id REFERENCES boxes.id WITH =
-- oids of operators:
-- POINT <@ BOX 511
-- BOX @> POINT 433 [commutator of op 511]
-- BOX ~= BOX 499
-- POINT ~= POINT 510
UPDATE pg_constraint SET conkey='{2}',
confkey='{2}',
conpfeqop='{433}',
conppeqop='{499}',
conffeqop='{510}'
WHERE conname='points_region_id_fkey';
-- these should all work:
INSERT INTO boxes (region, name) VALUES (box(point(2,2), point(5,5)), 'A');
INSERT INTO boxes (region, name) VALUES (box(point(7,7), point(10,10)), 'A');
INSERT INTO points (region_id, poi, score) VALUES (1, point(3,3), 10);
INSERT INTO points (region_id, poi, score) VALUES (1, point(4,4), 5);
INSERT INTO points (region_id, poi, score) VALUES (2, point(8,8), 15);
COMMIT;
-- this should fail:
INSERT INTO points (region_id, poi, score) VALUES (1, point(1,1), 15);
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
