[SQL] Restrict FOREIGN KEY to a part of the referenced table

[Matthias Nagel]

Hello,

is there any best practice method how to create a foreign key that only allows 
values from those rows in the referenced table that fulfill an additional 
condition?

First I present two pseudo solutions to clarify what I would like to do. They 
are no real solutions, because they are neither SQL standard nor postgresql 
compliant. The third solution actually works, but I do not like it for reason I 
will explain later:

CREATE TABLE parent (
  id SERIAL,
  discriminator INT NOT NULL,
  attribute1 VARCHAR,
  ...
);


Pseudo solution 1 (with a hard-coded value):

CREATE TABLE child (
  id SERIAL NOT NULL,
  parent_id INT NOT NULL,
  attribute2 VARCHAR,
  ...,
  FOREIGN KEY ( parent_id, 42 ) REFERENCES parent ( id, discriminator )
);


Pseudo solution 2 (with a nested SELECT statement):

CREATE TABLE child (
  id SERIAL NOT NULL,
  parent_id INT NOT NULL,
  attribute2 VARCHAR,
  ...,
  FOREIGN KEY ( parent_id ) REFERENCES ( SELECT * FROM parent WHERE 
discriminator = 42 ) ( id )
);


Working solution:

CREATE TABLE child (
  id SERIAL NOT NULL,
  parent_id INT NOT NULL,
  parent_discriminator INT NOT NULL DEFAULT 42,
  attribute2 VARCHAR,
  ...,
  FOREIGN KEY ( parent_id, parent_discriminator ) REFERENCES parent ( id, 
discriminator ),
  CHECK ( parent_discriminator = 42 )
);


The third solution work, but I do not like it, because it adds an extra column 
to the table that always contains a constant value for the sole purpose to be 
able to use this column in the FOREIGN KEY clause. On the one hand this is a 
waste of memory and on the other hand it is not immediately obvious to an 
outside person what the purpose of this extra column and CHECK clause is. I am 
convinced that any administrator who follows me might get into problems to 
understand what this is supposed to be. I would like to have a more 
self-explanatory solution like 1 or 2.

I wonder if there is something better.

Best regards, Matthias

--
Matthias Nagel
Willy-Andreas-Allee 1, Zimmer 506
76131 Karlsruhe

Telefon: +49-721-8695-1506
Mobil: +49-151-15998774
e-Mail: matthias.h.na...@gmail.com
ICQ: 499797758
Skype: nagmat84



-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

[SQL] Slow update with ST_Contians()

[androclos]

UPDATE tbl
SET city=s.city_name
FROM shp AS s
WHERE
ST_CONTAINS(s.city_geom,geom);

With the code above i can add exact city to a GPS point. It runs about 45-50
min on 50 million rows. There are about 4000 cities in the "city" table that
have to be checked.

I have another shape file with 19 counties in a given country(only 1
country). It takes it about 1,5 hour to add counties to points.

i have a third shape file with 52 EU countries. It runs almost 25 hours with
the same sql query.

Every table has index by geom, like:

CREATE INDEX idx_txt_geom ON txt USING GIST(geom);

Q: Why is it so slow when it has to check only a few polygons ?




--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Slow-update-with-ST-Contians-tp5751814.html
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Advice for index design

[Greg Sabino Mullane]

-BEGIN PGP SIGNED MESSAGE-
Hash: RIPEMD160

Jorge Maldonado wrote:

...
> What is a good index design approach? Maybe, setting simple separate 
> indexes (one for each field) would work fine if I need to
> retrieve data in different combinatios, but I am not sure.

Yes, just create separate indexes and you will be fine, especially given 
the very small size of the table. If you find your queries going slow, you 
could start investigating compound indexes (or in this case, partial 
indexes).

- -- 
Greg Sabino Mullane g...@turnstep.com
End Point Corporation http://www.endpoint.com/
PGP Key: 0x14964AC8 201304111933
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-BEGIN PGP SIGNATURE-

iEYEAREDAAYFAlFnSPYACgkQvJuQZxSWSshm6wCggdl2FyowAbca93hYKXGgcXoE
iN0AniOL8OS3teTgk6thxkJjUGqEf15k
=1Eb6
-END PGP SIGNATURE-




-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql