<br><br><div class="gmail_quote">On Fri, Apr 10, 2009 at 11:19 AM, Jeffrey LePage <span dir="ltr"><<a href="mailto:jeffrey_lepage@yahoo.com">jeffrey_lepage@yahoo.com</a>></span> wrote:<br><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
<br>
Greetings,<br>
<br>
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.<br>
<br>
I notice that the issues table is constructed thus:<br>
mysql> desc issues;<br>
+-----------------+-------------+------+-----+-------------------+-------+<br>
| Field | Type | Null | Key | Default | Extra |<br>
+-----------------+-------------+------+-----+-------------------+-------+<br>
| borrowernumber | int(11) | YES | MUL | NULL | |<br>
| itemnumber | int(11) | YES | MUL | NULL | |<br>
| date_due | date | YES | | NULL | |<br>
| branchcode | varchar(10) | YES | | NULL | |<br>
| issuingbranch | varchar(18) | YES | | NULL | |<br>
| returndate | date | YES | | NULL | |<br>
| lastreneweddate | date | YES | | NULL | |<br>
| return | varchar(4) | YES | | NULL | |<br>
| renewals | tinyint(4) | YES | | NULL | |<br>
| timestamp | timestamp | NO | | CURRENT_TIMESTAMP | |<br>
| issuedate | date | YES | | NULL | |<br>
+-----------------+-------------+------+-----+-------------------+-------+<br>
<br>
Notice that borrowernumber and itemnumber are NULL'able.<br>
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.<br>
<br>
Comments?</blockquote><div><br>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. </div>
</div><br>--<br>Joe Atzberger<br>LibLime - Open Source Library Solutions<br>