Thread: trigger to enforce FK with nulls?

[PostgreSQL 8.1.3 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.0.1]

I'm starting to use lots of foreign key constraints to keep my
data clean.  In one case, however, I need to allow null values
for the key.  E.g.:

create table opset_steps(opset text, step text, step_num int);
create table steps(run text, step text, opset text, user text, step_num int);

The constraint on steps should be:   steps.(opset,step) must be found in opset_steps.(opset,step) UNLESS steps.opset is
null.

I started to put dummy rows in opset_steps with opset='NO-OPSET' for each 
step with no real opset, but this looks really kludgy.

Since foreign keys per se can't do this, I presume the way
is to use triggers, though I have not used triggers before.  Any
suggestions, caveats?

Opset_steps has only 4400 rows, and inserts to steps are not real
frequent, so I don't *think* performace should be a problem.  No
updates are ever made to steps.opset and steps.step, or to
opset_steps.(opset,step)
[though updates are often made to *other* fields of steps].


-- George Young

-- 
"Are the gods not just?"  "Oh no, child.
What would become of us if they were?" (CSL)

George Young wrote:

> Since foreign keys per se can't do this, I presume the way
> is to use triggers, though I have not used triggers before.

Says who?  Just don't specify NOT NULL on the referencing column.

-- 
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

On Wed, 12 Apr 2006, George Young wrote:

> [PostgreSQL 8.1.3 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.0.1]
>
> I'm starting to use lots of foreign key constraints to keep my
> data clean.  In one case, however, I need to allow null values
> for the key.  E.g.:
>
> create table opset_steps(opset text, step text, step_num int);
> create table steps(run text, step text, opset text, user text, step_num int);
>
> The constraint on steps should be:
>    steps.(opset,step) must be found in opset_steps.(opset,step) UNLESS steps.opset is null.

The default foreign key case should not error if either steps.opset or
steps.step is null.  If you're seeing something else, can you give a
complete test case?