Re: [SQL] trigger to enforce FK with nulls?
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? ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] trigger to enforce FK with nulls?
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 Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[SQL] 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) ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
