On Fri, Apr 10, 2009 at 11:19 AM, Jeffrey LePage <jeffrey_lepage@yahoo.com>wrote:

Greetings,

We've just discovered 4 records in the issues table that have nulls where there should never be nulls. Specifically, 2 records with null borrowernumber and 2 records with null itemnumber. As a result, there are two overdue books checked out to a NULL patron and two patrons who've checked out NULL books.

I notice that the issues table is constructed thus: mysql> desc issues; +-----------------+-------------+------+-----+-------------------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------------+-------------+------+-----+-------------------+-------+ | borrowernumber | int(11) | YES | MUL | NULL | | | itemnumber | int(11) | YES | MUL | NULL | | | date_due | date | YES | | NULL | | | branchcode | varchar(10) | YES | | NULL | | | issuingbranch | varchar(18) | YES | | NULL | | | returndate | date | YES | | NULL | | | lastreneweddate | date | YES | | NULL | | | return | varchar(4) | YES | | NULL | | | renewals | tinyint(4) | YES | | NULL | | | timestamp | timestamp | NO | | CURRENT_TIMESTAMP | | | issuedate | date | YES | | NULL | | +-----------------+-------------+------+-----+-------------------+-------+

Notice that borrowernumber and itemnumber are NULL'able. Isn't this a _bad_ idea? I don't know why/how the null itemnumbers and borrowernumbers got into the database, but it seems that making them non-nullable would be a good idea.

Comments?

This is a known issue, basically a compromise to allow offline circulation to work in 3.0 (where the borrowernumber or itemnumber might not actually exist when the insertions are done). But in short, yes, I agree DB constraints need to be imposed for itemnumber and borrowernumber in issues. -- Joe Atzberger LibLime - Open Source Library Solutions