Re: foreign key check

[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