Postgres Pro
Downloads
Home   >   mailing lists

Thread: can a foreign key allow null

can a foreign key allow null

From
"Keith Worthington"
Date:
Hi All,

I have two tables.

MYDB=# \d myschema.tbl_part
                Table "myschema.tbl_part"
        Column        |          Type          | Modifiers
----------------------+------------------------+-----------
 id                   | character varying(20)  | not null
 description          | character varying(30)  | not null
 class                | smallint               | not null
 inactive             | boolean                | not null
 sales_description    | character varying(160) |
 purchase_description | character varying(160) |
 last_unit_cost       | real                   | not null
 costing_method       | smallint               | not null
 sales_gl_account     | character varying(15)  |
 inventory_gl_account | character varying(15)  |
 cogs_gl_account      | character varying(15)  |
 type                 | character varying(8)   |
 unit_of_measure      | character varying(6)   |
 weight               | real                   |
 reorder_point        | real                   |
 reorder_quantity     | real                   |
Indexes: tbl_part_pkey primary key btree (id)

MYDB=# \d myschema.tbl_gl_account
        Table "myschema.tbl_gl_account"
   Column    |         Type          | Modifiers
-------------+-----------------------+-----------
 number      | character varying(15) | not null
 description | character varying(30) |
 type        | smallint              | not null
 inactive    | boolean               | not null
Indexes: tbl_gl_account_pkey primary key btree (number)

MYDB=#

Is it possible to create a foreign key such that only null and values from
myschema.tbl_gl_account.number are allowed in myschema.tbl_part.sales_gl_account?

Kind Regards,
Keith

______________________________________________
99main Internet Services http://www.99main.com

Re: can a foreign key allow null

From
Steven Klassen
Date:
* Keith Worthington <keithw@narrowpathinc.com> [2004-10-19 15:01:40 -0400]:

>                 Table "myschema.tbl_part"
>         Column        |          Type          | Modifiers
> ----------------------+------------------------+-----------
>  sales_gl_account     | character varying(15)  |
>
>         Table "myschema.tbl_gl_account"
>    Column    |         Type          | Modifiers
> -------------+-----------------------+-----------
>  number      | character varying(15) | not null
>
> Is it possible to create a foreign key such that only null and
> values from myschema.tbl_gl_account.number are allowed in
> myschema.tbl_part.sales_gl_account?

How is this any different than a regular foreign key constraint?

ALTER TABLE tbl_part ADD CONSTRAINT account_number_exists FOREIGN KEY
(sales_gl_account) REFERENCES tbl_gl_account(number);

HTH,

--
Steven Klassen - Lead Programmer
Command Prompt, Inc. - http://www.commandprompt.com/
PostgreSQL Replication & Support Services, (503) 667-4564