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.