Re: Design question : Nullable FK & FK To Unique Key

From: DA Morgan <damorgan_at_psoug.org>
Date: Fri, 10 Nov 2006 09:36:49 -0800
Message-ID: <1163180209.231996@bubbleator.drizzle.com>

afatkulin_at_gmail.com wrote:

>> It seems everyone is understanding something different from what the
>> OP posted. My take was the following:
>>
>> CREATE TABLE order (
>> order_id   NUMBER
>> invoice_id NUMBER);
>>
>> CREATE TABLE invoice (
>> invoice_id  NUMBER);
>>
>> Since an order can be part of one and only one invoice (assuming no
>> back orders) then one could create an order today and invoice it,
>> filling in the invoice number, tomorrow.
>> --
>> Daniel A. Morgan
>> University of Washington
>> damorgan_at_x.washington.edu
>> (replace x with u to respond)
>> Puget Sound Oracle Users Group
>> www.psoug.org

>
> So this one (with wrong understanding) is you :)
>
> OP said:
> "OR they can just input Invoice without order."
>
> "FK from Invoice to Orders"
>
> so opposite is true:
>
> CREATE TABLE order (
> order_id NUMBER primary key
> );
>
> CREATE TABLE invoice (
> invoice_id number primary key,
> order_id referencing order (order_id)
> );
>
> int your case - there is no such thing as "FK from Invoice to Orders".

My apology for assuming you understood Oracle and how it works. Here is a demo to clarify my intention.

SQL> CREATE TABLE orders (

   2 order_id NUMBER,
   3 invoice_id NUMBER);

Table created.

SQL> CREATE TABLE invoice (

   2 invoice_id NUMBER);

Table created.

SQL> ALTER TABLE orders

   2 ADD CONSTRAINT pk_orders
   3 PRIMARY KEY (order_id);

Table altered.

SQL> ALTER TABLE invoice

   2 ADD CONSTRAINT pk_invoice
   3 PRIMARY KEY (invoice_id);

Table altered.

SQL> ALTER TABLE orders

   2 ADD CONSTRAINT fk_orders_invoice_id    3 FOREIGN KEY (invoice_id)
   4 REFERENCING invoice(invoice_id);

Table altered.

SQL> INSERT INTO orders

   2 (order_id, invoice_id)
   3 VALUES
   4 (1, NULL); 1 row created.

SQL> INSERT INTO invoice

   2 (invoice_id)
   3 VALUES
   4 (99);

1 row created.

SQL> insert into orders

   2 (order_id, invoice_id)
   3 VALUES
   4 (2, 99);

1 row created.

SQL> update orders

   2 set invoice_id = 99
   3 where order_id = 1;

1 row updated.

SQL> As you can see orders can be created independent of an invoice. Invoices can be created independent of orders. and Orders can be updated, later, to reflect an invoice.

I hope this helps.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Received on Fri Nov 10 2006 - 11:36:49 CST