Problem solve Get help with specific problems with your technologies, process and projects.

When to use optional foreign key

I have a question regarding database design: Is there a scenario where the use of an "optional" foreign key is desirable? By optional the foreign key column can be NULL. Is this an application driven concern? I haven't received what I consider a good design approach for the occurrence of this scenario.
I've seen some design cases where a FK constraint can be considered "optional," but those cases are pretty rare. And to answer your question, let's look at a simple example.

ORA9I SQL> create table parent (
  2  parent_id number primary key);

Table created.

ORA9I SQL> create table child (
  2  child_id number primary key,
  3  parent_id number references parent(parent_id));

Table created.

I now have a parent table and a child table. The child table has a foreign key constraint to the parent table. And the following SQL statements work because I have a parent record for the child record I want to insert:

ORA9I SQL> insert into parent values (1);

1 row created.

ORA9I SQL> insert into child values (1,1);

1 row created.

So what happens when you want to insert a NULL value for the FK column?

ORA9I SQL> insert into child values (2,null);

1 row created.

ORA9I SQL> commit;

Commit complete.

As you can see, it works! 

I'm not sure why you would even want to do this though. In my mind, a child can't exist without a parent. And if you don't know the parent, how can you define the child. Like I said, there have been some designs that implement this, but they are rare.

Dig Deeper on Oracle database design and architecture

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.