| From: | George Young <gry(at)ll(dot)mit(dot)edu> |
|---|---|
| To: | pgsql-sql(at)postgresql(dot)org |
| Subject: | trigger to enforce FK with nulls? |
| Date: | 2006-04-13 01:11:18 |
| Message-ID: | 20060412211118.05411612.gry@ll.mit.edu |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-sql |
[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)
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Alvaro Herrera | 2006-04-13 01:22:22 | Re: trigger to enforce FK with nulls? |
| Previous Message | kevin.kempter | 2006-04-12 19:33:28 | Re: SQL help (Informix outer to EnterpriseDB outer) |