Skip site navigation (1) Skip section navigation (2)

Re: foreign key check

From: Michael Glaesemann <grzm(at)seespotcode(dot)net>
To: David Gardner <david(at)gardnerit(dot)net>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: foreign key check
Date: 2007-06-19 02:01:50
Message-ID: B144A713-3F8D-4B51-A94E-46C65565B525@seespotcode.net (view raw or flat)
Thread:
Lists: pgsql-novice
[Please don't top post as it makes the discussion more difficult to  
follow.]

On Jun 18, 2007, at 19:52 , David Gardner wrote:

> David Gardner wrote:
>> I have two tables t1, and t2 where t2.fid is a foreign key  
>> reference to t1.id. Except in this case only a subset of the  
>> values in t1 are valid, the values in t1 that are valid can be  
>> found by doing a:
>> SELECT t1.id JOIN t3 ON t3.id = t1.id
>> Is there some way to add this in as a check constraint? I could  
>> just write a pl/pgsql function to perform a check before  
>> inserting, but I am beginning to suspect that the reason I am  
>> running into this is because my schema isn't accurately  
>> representing the business logic of the application.

I suspect you're right in thinking there's something a little odd  
about your schema, though it's hard to say without knowing a bit more  
about what you're trying to model. It's much easier to go from model  
to database logic than database logic to model :)

> Woops I missed an important bit there. the select statement should  
> look like:
> SELECT t1.idTwo JOIN t3 ON t3.id = t1.id WHERE t3.val=${variable}

Do you mean something like this?

SELECT t1.id
FROM t1
JOIN t3 ON (t3.id = t1.id)
WHERE t3.val = ?

Since t1.id = t3.id, you can see that t2.fid could just as well be a  
foreign key to t3.id. Here's what I imagine your schema looks like:

CREATE TABLE t1 (id INTEGER PRIMARY KEY);
CREATE TABLE t3
(
     id INTEGER PRIMARY KEY
     , val TEXT NOT NULL
     , UNIQUE (id, val)
);

CREATE TABLE t2
(
     id INTEGER PRIMARY KEY
     fid INTEGER NOT NULL
         REFERENCES t3
);

Since you've got an additional constraint that t2.fid = t3.id when  
t3.val = V, you could do something like:

CREATE TABLE t2
(
     id INTEGER PRIMARY KEY
     , fid INTEGER NOT NULL
     , val TEXT NOT NULL
         CHECK (val = 'V')
     , FOREIGN KEY (fid, val)
        REFERENCES t3 (id, val)
);

The check constraint makes sure that t2.val always equals 'V' (or  
whatever else your given t3.val is), and the foreign key makes sure  
that (fid, val) occurs in t3 (id, val)

Note the UNIQUE (id, val) constraint on t3: this is required for the  
foreign key to work. If this doesn't match your business logic,  
that's even a better clue that you might have to revise your schema.

Hope this helps.

Michael Glaesemann
grzm seespotcode net



In response to

pgsql-novice by date

Next:From: John MeyerDate: 2007-06-19 03:02:07
Subject: Re: [GENERAL] [PERFORM] [ADMIN] Postgres VS Oracle
Previous:From: Tom LaneDate: 2007-06-19 01:39:08
Subject: Re: foreign key check

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group