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

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.