Two Part Primary Key with One Null Value

[LadyDi]

I have just inherited a database that currently has no primary key set up. I wanted to add a two part primary key, but have run into a couple problems. The purpose of the database is to keep track of parts that have come back for repair. I wanted to set the key up to be a combination of the call number and the part serial number. I should always have a serial number, but the serial number can be in the table more than once, as it could have needed repaired more than once. I will normally have a call number for each record. However, the call number could be repeated more than once, if more than one part came in on the call. The combination between call number and serial number should always be unique (each time a part comes in, it should be on a different call). My problem is arising, when a part is just pulled off the shelf of the factory and sent in for an upgrade. In a case like that, there is no call number. However, a serial number will never come in more than once without a call number. So the combination of call number and serial number should still remain unique. When I try to set this up in the database, it tells me that the primary key cannot contain a null value (referring to the times that a serial number comes in without a call number). I want to get this set up so that the people entering data in the database will not be able to enter the call number, serial number combination more than once. How can I do this if one part of the primary key can be null?

Any suggestions you have would be greatly appreciated.

 

[CJ_London]

You may be able to get this to work by setting defaults to 0 (numeric) or allow zero length strings with default="" (text) in your table

Alternatively you need to create an autonumber field as your primary key and then have some code on your form to control duplicates. Since serial number and call number can appear more than once, ensure both fields are indexed with duplicates OK and have some code along the following lines which appears in your form beforeupdate event (assumes both field type are numeric).

if dcount("*","MyTable","SerialNo=" & Me.SerialNo & " AND CallNo=" & nz(me.CallNo))>0 then
    msgbox "Duplicates not allowed, please review the serial and call numbers"
end if

Without knowing your form this in itself is insufficient - you need to ensure the user loops back and completes the form properly and also you have a method to ensure user cannot input a random number in just to be able to move forward (which you will have with your existing process)

 

[Mihail]

Because the null values you can't define a PK but you can define an index unique on this two fields.
(Hope I remember well because I can't verify that just now).

 

[Pat Hartman]

Let me expand on what Mihail said. Having compound PKs causes problems if you need to use combos. For this reason, I always use autonumbers as surogate primary keys. To enforce the business rules regarding uniqueness, add a Unique compound index that includes the two columns. The unique index does allow null values in one or more columns.

 

[ButtonMoon]

If the business rules are important to you then you could consider using two tables, one for each of the two cases (example below). Or perhaps you could simply use a default call number for calls which lack them.

CREATE TABLE Calls
(CallNum INT NOT NULL,
SerialNum INT NOT NULL,
PRIMARY KEY (SerialNum, CallNum),
CallDate DATE);

CREATE TABLE PartCalls
(SerialNum INT NOT NULL,
PRIMARY KEY (SerialNum),
CallDate DATE);

Certainly avoid nullable "unique" constraints (an oxymoron). They are potentially the worst kind of uncontrolled denormalization. In your case a nullable unique constraint would not enforce the business rules you mentioned - if the Call Number is nullable then a composite unique constraint would still allow the same Serial Number multiple times without a Call Number. Go figure why the ISO SQL committee ever permitted such a horrible feature!

 

[LadyDi]

Thank you everyone for the advice. The compound indexes work really well. I was also able to use the code CJ_London provided to give the users a warning when they were about to enter a duplicate record before they had the entire record loaded.