2009/4/11 Joe Atzberger <ohiocore@gmail.com>:
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.
Shifting to the devel list. Shouldn't we store the offline circs in their own table, with nullable fields if they need it. Then reconcile them into the issues table? That way we can put the constraints back on the issues table. Chris