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

Dear Mr. Kyte,
Thanx a lot.
I have ran the query and my foreign key is working fine. Wasim.

> <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
>

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Thu Sep 09 1999 - 07:27:40 CDT