Thread: 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