Results 1 to 14 of 14
  1. #1
    Join Date
    Apr 2010
    Posts
    3

    Question Problems Creating a Relationship in MS Access Using DDL SQL

    Can anyone tell me how to create a permanent relationship between a parent and child table that does not enforce referential integrity ever using only DDL SQL?

    Secondary question which might solve my problem:

    In a relationship where referential integrity is enforced is it possible to have NULL values in the Foreign Key of the Child table?

    Changing the database design is not an option as the Foreign Keys in certain cases are optional by design.

    here is a sample of the DDL command I am using to create relationships where ref integrity is auto enforced is there a simple syntax keyword to disable ref integrity in DDL?

    Code:
    ALTER TABLE services ADD CONSTRAINT FK_services_program_id FOREIGN KEY (program_id) REFERENCES programs (program_id);
    The MS ACCESS databases I am using are simple backups of production databases for read only Reporting purposes. There is no data entry ever being done so no cascade updating or deleting is required just a simple relationship.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,910
    1) A foreign key is a constraint. Its sole purpose is to enforce referential integrity. As such, if you don't require referential integrity, I would suggest you don't bother creating one - it has no purpose anyway. Other RDBMSs do allow you to enable and disable constraints and apply them with no checks of existing data but not Access.
    2) Absolutely you can. So in the DDL you provided, you will allow the program_id column of services to be NULLable.

  3. #3
    Join Date
    Apr 2010
    Posts
    3

    Thumbs up Thanks

    I will use NULL's it solves my problem.

    Thanks!

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,910
    Ah ok - so to confirm - a foreign key restricts the values you can place in the referencing (or child) column. It does not require you to actually put a value in the column.
    Glad you are sorted and we aren't rewriting relational standards!

  5. #5
    Join Date
    Apr 2010
    Posts
    3
    You got it.

    In MS ACCESS Referential Integrity a Foreign Key will accept NULL as a value.

    I initially expected it to give me an error because technically there is no primary key value in the parent table that corresponds to NULL but it worked like a charm was able to insert the NULL in the SQL where the numeric values were in and all was golden.

    Code:
    INSERT INTO programs(program_id,whatever) VALUES(25,'blah blah');
    Code:
    INSERT INTO services(service_id,program_id,whatever) VALUES(1,25,'blah blah');
    INSERT INTO services(service_id,program_id,whatever) VALUES(2,NULL,'blah blah');

  6. #6
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    740
    You need to be particularly careful about using nulls in foreign keys. Think about what the presence of a null actually means in your data model. It's hard to avoid getting incorrect results with nulls and that's doubly true if users of your database understand a constraint to be in place when in reality it isn't being enforced in the way they usually expect.

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,910
    Dave - you should probably declare here that you have a problem with NULLs period, irrespective of whether or not they are inserted into columns constrained by foreign keys.

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,910
    And following on from that point I disagree - I don't believe that foreign keys introduce any new problems for NULLs other than the well documented but (IMO) overstated general problems with NULLs.

  9. #9
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    740
    There are at least some null problems that deserve special consideration in the case of foreign keys.

    Suppose I want to enforce the referential constraint that a subscription must reference a valid member in a Members table (example below). Notice that membership number is nullable which in this case is supposed to represent the fact that we may not know a person's membership number at the time the subscription is paid. We will always know the member's branch number.

    Code:
    CREATE TABLE Members
    (BranchNumber INT NOT NULL,
     MembershipNumber INT NOT NULL, PRIMARY KEY (BranchNumber, MembershipNumber));
    
    INSERT INTO Members (BranchNumber, MembershipNumber) VALUES (1,101);
    INSERT INTO Members (BranchNumber, MembershipNumber) VALUES (1,102);
    INSERT INTO Members (BranchNumber, MembershipNumber) VALUES (2,101);
    INSERT INTO Members (BranchNumber, MembershipNumber) VALUES (2,108);
    
    CREATE TABLE Subscriptions
    (SubscriptionID INT NOT NULL PRIMARY KEY,
     BranchNumber INT NOT NULL,
     MembershipNumber INT NULL,
     SubscriptionAmount NUMERIC(10,2) NOT NULL,
     FOREIGN KEY (BranchNumber, MembershipNumber) REFERENCES Members (BranchNumber, MembershipNumber));
    
    INSERT INTO Subscriptions (SubscriptionID, BranchNumber, MembershipNumber, SubscriptionAmount)
    VALUES (1,1,101,123.00);
    Now let's insert a sub with a null membership number. The following INSERT is permitted:

    Code:
    INSERT INTO Subscriptions (SubscriptionID, BranchNumber, MembershipNumber, SubscriptionAmount)
    VALUES (2,9,NULL,100.00);
    Notice that the constraint on BranchNumber is not being enforced even though it is a non-nullable column! In a sense it is worse than that. If we understand the null to mean only that the membership number is "unknown" then no matter what the unknown membership number is, we know that the whole constraint across both columns is being violated. We know that simply because there is no member with branch number 9. However, SQL's logic does not interpret nulls to mean unknown so we get the wrong result - a result that does not match the reality of the constraint we wanted to enforce. As a consequence an inner join on BranchNumber may exclude some subscriptions even though the model appears to require only valid branch numbers.

    Obviously this problem is just a consequence of a mistaken assumption in my example, namely the assumption that null is an accurate way to model an unknown value. That's why I said that the OP should think carefully about what is the intended meaning of null in his model. If null is intended to mean "unknown" then the results are probably not going to be very satisfactory in terms of reality or logic.

    The OP may have some other intended meaning for null of course, but that's always part of the problem with nulls - there are no generally agreed semantics for null that make sense of the behaviour of nulls in SQL.

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,910
    Quote Originally Posted by dportas View Post
    The following INSERT is permitted
    Nope - it isn't. It fails due to the foreign key violation.

  11. #11
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    740
    Quote Originally Posted by pootle flump View Post
    Nope - it isn't. It fails due to the foreign key violation.
    Not in standard SQL it doesn't and not in most DBMSs that follow the SQL standard. What DBMS are you using?

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,910
    ACE, since we are in the Access forum. Remember that this is not dbconcepts & design.


    It does though fail in SQL Server and I admit I am shocked and did not know about this.

    However, this is a rather contrived example - in reality I can't conceive of a situation where one of the columns in a composite foreign key would be NULLable and the other not NULLable.

  13. #13
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    740
    Which neatly illustrates two more common problems with nulls:

    - Different systems will give you different and contradictory results

    - Even knowledgable and experienced people like you and I don't fully understand how nulls work. Average database end-users therefore stand no chance of knowing how to deal with them correctly.

    Quote Originally Posted by pootle flump View Post
    However, this is a rather contrived example - in reality I can't conceive of a situation where one of the columns in a composite foreign key would be NULLable and the other not NULLable.
    It doesn't seem unrealistic to me. In fact it's based on a real situation. It is of course true that in any circumstances nulls can give incorrect results (results that don't match the real world or common sense logic). That's not a problem exclusive to foreign keys. I just wanted to illustrate that there are special considerations that can apply with foreign keys.

  14. #14
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,910
    In short for the OP - this specific problem does not apply to his\ her situation.
    To other interested parties - it only occurs on composite foreign keys within SQL compliant RDBMSs.

Tags for this Thread

Posting Permissions

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