Обсуждение: Foreign Exclusion Constraints

Hi All,

I have tables:

CREATE TABLE chromosomes (
    id                   varchar(10) PRIMARY KEY,
    location           int4range
);

CREATE TABLE exons (
    chromosome   varchar(10) REFERENCES  chromosomes(id),
    strand             varchar(1),
    location           int4range
);

I'm trying to store exons that must fit within a chromosome, but the
chromosomes all have different lengths, so I want a check constraint
so that location is contained within CHR_RANGE where CHR_RANGE is
taken from the chromosomes table.

ie, it would be something like:

FOREIGN KEY (chromosome) REFERENCES chromosomes(chromosome) USING gist
(during WITH <@ )

Can I add that constraint without a trigger?

The archives seem to say no, but I thought I'd ask...

http://www.postgresql.org/message-id/1288033876.6278.6.camel@vanquo.pezone.net
http://www.postgresql.org/message-id/CACECd8i4P4iNqUii4Lqsw0qSthawhh3gE=AtmBDSJN25qrx15g@mail.gmail.com
http://www.postgresql.org/message-id/1678334.8llTyI05Te@hek506

Thanks,
Nathan Boley

On Wed, Feb 20, 2013 at 2:34 PM, Nathan Boley <npboley@gmail.com> wrote:
> I'm trying to store exons that must fit within a chromosome, but the
> chromosomes all have different lengths, so I want a check constraint
> so that location is contained within CHR_RANGE where CHR_RANGE is
> taken from the chromosomes table.
>
> Can I add that constraint without a trigger?

No, you can't.

There is nothing wrong in doing it with triggers BTW.

>
> The archives seem to say no, but I thought I'd ask...
>
> http://www.postgresql.org/message-id/1288033876.6278.6.camel@vanquo.pezone.net
> http://www.postgresql.org/message-id/CACECd8i4P4iNqUii4Lqsw0qSthawhh3gE=AtmBDSJN25qrx15g@mail.gmail.com
> http://www.postgresql.org/message-id/1678334.8llTyI05Te@hek506
>
> Thanks,
> Nathan Boley
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general



--
Sergey Konoplev
Database and Software Architect
http://www.linkedin.com/in/grayhemp

Phones:
USA +1 415 867 9984
Russia, Moscow +7 901 903 0499
Russia, Krasnodar +7 988 888 1979

Skype: gray-hemp
Jabber: gray.ru@gmail.com