I'm trying to create a relation where any of four different parts may be included, but any collection of the same parts should be handled as unique.
Example: An assignment must have an assigned company, may optionally have an assigned location, workgroup and program. An assignment may not have a workgroup without a location.
Let's assume we have companies A, B, C; locations X, Y, Z; workgroups I, J, K and programs 1, 2, 3.
So valid relations could include A - X - I - 1 A - Z - 2 B - Y C C - 3 B - Z - K
But invalid relations would include A - K (Workgroup without location) Y - K - 1 (No company)
So, to create my table, I've created
companyID INT NOT NULL,
FOREIGN KEY companyKEY (companyID) REFERENCES company (companyID),
locationID INT,
FOREIGN KEY locationKEY (locationID) REFERENCES location (locationID),
workgroupID INT,
FOREIGN KEY workgroupKEY (workgroupID) REFERENCES workgroup (workgroupID),
programID INT,
FOREIGN KEY programKEY (programID) REFERENCES program (programID),
UNIQUE KEY companyLocationWorkgroupProgramKEY (companyID, locationID, workgroupID, programID)
I figure this would handle all my relations besides the neccessity of an assignment to have a location if there is a workgroup (which I can happily do programatically or with triggers, I think)
However, when I test this schema, it allows me to enter the following...
INSERT INTO test VALUES (1, null, null, null), (1, null, null, null);
...without complaint. I'm guessing that (1, null, null, null) does not equal itself because nulls are included. If this is the case, is there any way I can handle this relation?
Any help would be appreciated!