Skip to Main Content
  • Questions

  • Mandatory Relationships between tables

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Saeed.

Asked: August 29, 2004 - 1:12 pm UTC

Last updated: May 13, 2005 - 8:57 am UTC

Version: 8.1.7

Viewed 1000+ times

You Asked

Hi Tom,

I have two table Order and Orderline, the relationship between them is mandatory one to many i.e. One Order must have atleast one Order Line. How can we inforce this relationship using the traditional PK and FK constraints?

Thanks

and Tom said...

personally -- i could definite see an order without any lineitems - i started an order (got the address and everything) but could not complete it yet. but anyway, one approach is (and this would work for the mythical 1:1 mandatory as well)


ops$tkyte@ORA9IR2> create table p
2 ( id number primary key,
3 cid number not null deferrable initially deferred
4 )
5 /

Table created.

ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create table c
2 ( id number primary key,
3 pid references p
4 )
5 /

Table created.

ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> alter table p add constraint p_fk_c foreign key(cid) references c(id)
2 deferrable initially deferred;

Table altered.

ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create sequence p_seq;

Sequence created.

ops$tkyte@ORA9IR2> create sequence c_seq;

Sequence created.

ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> insert into p values ( p_seq.nextval, c_seq.nextval );

1 row created.

ops$tkyte@ORA9IR2> insert into c values ( c_seq.currval, p_seq.currval );

1 row created.

ops$tkyte@ORA9IR2> insert into c values ( c_seq.nextval, p_seq.currval );

1 row created.

ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> commit;

Commit complete.

ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> insert into p values ( p_seq.nextval, null );

1 row created.

ops$tkyte@ORA9IR2> commit;
commit
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-02290: check constraint (OPS$TKYTE.SYS_C006418) violated



You cannot complete a transaction whereby:

a) cid in p is null or
b) cid in p is not in c




Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

deferrable twice?

Vlado, May 12, 2005 - 5:48 pm UTC

In your example when creating table p, you define the cid "column" as deferrable. Later then you define the actual constraint as deferrable.
What is the purpose of the first "deferrable" on the column itself?

I tried your example without the first "deferrable", and it worked...



Tom Kyte
May 13, 2005 - 8:57 am UTC 

there is a not null constraint that is deferrable
there is a foreign key constraint that is deferrable

there are two separate constraints on p.cid

and if you comment out the not null deferrable bit:

ops$tkyte@ORA9IR2> create table p
  2  ( id number primary key,
  3    cid number not null -- deferrable initially deferred
  4  )
  5  /
 
Table created.


you get a different end result:

 
ops$tkyte@ORA9IR2> insert into p values ( p_seq.nextval, null );
insert into p values ( p_seq.nextval, null )
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("OPS$TKYTE"."P"."CID")



the goal here was to enforce a 1:1 mandatory relationship.

In order for that to happen the foreign keys must be NOT NULL

But then it would be very hard to create a record in either table! So, at least one of the foreign keys needs to be NOT NULL DEFERRABLE in order to allow this to happen in a two step fashion easily.

Thanks

Vlado, May 13, 2005 - 2:49 pm UTC

Thank yuou for the explanation. I was not ware that you can defer not null constraints.


more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.