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.
dBforums
>
Problems Creating a Relationship in MS Access Using DDL SQL
04-07-10, 13:15
Problems Creating a Relationship in MS Access Using DDL SQL
Thanks
Linear Mode
