Re: Oracle does not support Foreign keys????

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Wed, 08 Sep 1999 09:28:13 -0400
Message-ID: <zGHWN6RiPHWkT72dCzDSVHeTezzC@4ax.com>

A copy of this was sent to wasim_ahmed_at_my-deja.com (if that email address didn't require changing) On Wed, 08 Sep 1999 05:23:07 GMT, you wrote:

>I am using oracle 8.0.4. I have two tables named WORK_ORDER (child
>table) and
>SCHEDULE_TS_RESOURCE (parent table) which are described below.
>

[snip]

tkyte_at_8.0> create table work_order (

  2  WORK_ORDER_ID                   VARCHAR2(10)NOT NULL,
  3  ACCOUNT_NO                       VARCHAR2(10)NOT NULL,
  4  CREATED_BY                       VARCHAR2(20)NOT NULL,
  5  INITIATION_DATE                 DATE NOT NULL,
  6  ORDER_TYPE                       CHAR(3)NOT NULL,
  7  STATE_ID                         CHAR(3)NOT NULL,
  8  STATUS_ID                       CHAR(3)NOT NULL,
  9  ASSIGNED_TO                              VARCHAR2(10),
 10  DUE_DATE                                DATE,
 11  COMPLETION_DATE                          DATE,
 12  COMPLETED_BY                            VARCHAR2(30),
 13  DESCRIPTION                              VARCHAR2(200),
 14  EFFECTIVE_BILL_DATE                      DATE,
 15  MISSED_DUE_DATE_REASON_ID                CHAR(3),
 16  TIME_SLOT_NAME                          VARCHAR2(30),
 17  MODIFIED_BY                              VARCHAR2(20),
 18  UNITS                                    NUMBER(4) );

Table created.

tkyte_at_8.0> 
tkyte_at_8.0> 
tkyte_at_8.0> create table schedule_ts_resource (
  2  SCHEDULE_DATE                  DATE not null,
  3  TIME_SLOT_NAME                 VARCHAR2(30) not null,
  4  RESOURCE_ID                    VARCHAR2(10)  not null,

  5 primary key( schedule_date, time_slot_name, resource_id ) );

Table created.

tkyte_at_8.0> 
tkyte_at_8.0> 
tkyte_at_8.0> alter table work_order add constraint work_order_fk

  2 foreign key (DUE_DATE, TIME_SLOT_NAME, ASSIGNED_TO)   3 references schedule_ts_resource (SCHEDULE_DATE, TIME_SLOT_NAME, RESOURCE_ID)
  4 /

Table altered.

tkyte_at_8.0> insert into work_order
  2 ( work_order_id, account_no, created_by, initiation_date,   3 order_type, state_id, status_id, due_date, time_slot_name, assigned_to )   4 values
  5 ( 1, 1, 1, sysdate, 1, 1, 1, sysdate, 1, 1 ); insert into work_order

            *
ERROR at line 1:
ORA-02291: integrity constraint (TKYTE.WORK_ORDER_FK) violated - parent key not found

I cannot reproduce with all non-null columns. What might be getting you though is the fact that:

<quote concepts manual>
Nulls and Foreign Keys

The relational model permits foreign keys to be a value of the referenced primary or unique key, or null. There are several possible interpretations of this basic rule of the relational model when composite (multicolumn) foreign keys are involved.

The ANSI/ISO SQL92 (entry–level) standard permits a composite foreign key to contain any value in its non–null columns if any other column is null, even if those non–null values are not found in the referenced key. By using other constraints (for example, NOT NULL and CHECK constraints), you can alter the treatment of partially null foreign keys from this default treatment. </quote>

In english -- if any of the foreign key columns are NULL -- then the key does not have to exist in the referenced table. For example:

tkyte_at_8.0> insert into work_order
  2 ( work_order_id, account_no, created_by, initiation_date,   3 order_type, state_id, status_id, due_date, time_slot_name, assigned_to )   4 values
  5 ( 1, 1, 1, sysdate, 1, 1, 1, null, 1, 1 );

1 row created.

works since the DUE_DATE column is null. Perhaps the correct implementation of the table would be to add a TABLE check constraint on work_order that is:

check (( due_date is null AND time_slot_name is null AND assigned_to is NULL ) or

      ( due_date is not null AND time_slot_name is not null AND assigned_to is not NULL ))

if we do that, then the entire foreign key must be NULL or NOT NULL. this would prevent the NULLS from letting a foriegn key exist that has no parent.

Try running:

  1 select DUE_DATE, TIME_SLOT_NAME, ASSIGNED_TO   2 from work_order a
  3 where NOT EXISTS ( select null

  4                        from schedule_ts_resource b
  5                       where a.due_date = b.schedule_date
  6                         and a.time_slot_name = b.time_slot_name
  7                         and a.assigned_to = b.resource_id )

  8 and due_date is not null
  9 and time_slot_name is not null
 10* and assigned_to is not null
tkyte_at_8.0> /

no rows selected

if that returns no rows -- the fkey is being done correctly.

>
>
>I have tried to drop and recreate the constraints (although there is
>data present
>in work_order table which is not present in
>schedule_ts_resource table) , the constraint is created.
>Any help or comments are appreciated.
>regards,
>Wasim.
>
>
>Sent via Deja.com http://www.deja.com/
>Share what you know. Learn what you don't.

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Wed Sep 08 1999 - 08:28:13 CDT