Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2003
    Posts
    1,074

    Unanswered: FK can contain NULL values

    --Just so that I'm not missing anything:

    I was reading that you could have a NULL value in a foreign key field:

    "When no other constraints are defined on the foreign key, any number of rows in the child table can reference the same parent key value. This model allows nulls in the foreign key."

    In order to pull back these NULL FK records in a query, along with the results you would expect from an equi-join, is there a specific data retrieval method you can employ besides using an outer join or a union query?

    -Chuck
    Last edited by chuck_forbes; 06-24-04 at 19:35.

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Surely outer join is the specific retrieval method you need for that case? If not, why not?
    Last edited by andrewst; 06-25-04 at 11:59.

  3. #3
    Join Date
    Dec 2003
    Posts
    1,074
    Without explaining the addt'l requirements in a bunch of detail:

    If you disable Referential Integrity constraints during a dataload, and you load orphan records into a child table where FK is a non-NULL value, and then you re-enable the constraints, will Oracle throw an error?

    -Thanks,
    Chuck

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Whether you load the data whith the constraints enabled, or load the data with them disabled and then enable them, makes no difference.

    But are we talking about a single-column foreign key here or a composite one?

    For a single-column foreign key the answer is yes: all non-null values in the foreign key column must match a value from the referenced parent column when the constraint is enabled (unless you use NOVALIDATE of course).

    For composite keys where nulls are allowed, things can get very messy - in fact you would almost certainly need to add a check constraint to the child table to ensure that the FK columns are either all null or all non-null, otherwise anything goes (almost):

    Code:
    SQL> create table t1 (a int, b int, primary key (a,b));
    
    Table created.
    
    SQL> insert into t1 values (1,1);
    
    1 row created.
    
    SQL> create table t2 (a int, b int, foreign key (a,b) references t1);
    
    Table created.
    
    SQL> insert into t2 values (1,1);
    
    1 row created.
    
    SQL> insert into t2 values (1,2);
    insert into t2 values (1,2)
    *
    ERROR at line 1:
    ORA-02291: integrity constraint (TANDREWS.SYS_C00159991) violated - parent key
    not found
    
    
    SQL> insert into t2 values (1,null);
    
    1 row created.
    
    SQL> insert into t2 values (99,null);
    
    1 row created.
    i.e. provided you leave one of the FK cols null, you can put what you like in the others! I have no idea why this would ever be desirable.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •