trigger to enforce FK with nulls?
От | George Young |
---|---|
Тема | trigger to enforce FK with nulls? |
Дата | |
Msg-id | 20060412211118.05411612.gry@ll.mit.edu обсуждение исходный текст |
Ответы |
Re: trigger to enforce FK with nulls?
Re: trigger to enforce FK with nulls? |
Список | 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)
В списке pgsql-sql по дате отправления: