Hi

I have two tables, User and Book, in Book is a foreign key to UserID
in the User table. (Think of it as an extremely simple library
system).

How would I integrate Integrity Constraints so that 1 user can only
loan/belong to 6 books? I was looking into Stored Procedures/
Functions but was not sure how to go about incorporating this into the
database...

I would be extremely grateful for any help


Thanks

Lawrence

On Mar 15, 9:59 am, "Lawrence" <lawrence.h...@gmail.com> wrote:
> Hi
>
> I have two tables, User and Book, in Book is a foreign key to UserID
> in the User table. (Think of it as an extremely simple library
> system).
>
> How would I integrate Integrity Constraints so that 1 user can only
> loan/belong to 6 books? I was looking into Stored Procedures/
> Functions but was not sure how to go about incorporating this into the
> database...
>
> I would be extremely grateful for any help
>
> Thanks
>
> Lawrence

I posted the following response previously - but I can't see it here
so I'll post it again. Apologies if it now appears twice...

Just thinking out loud I think it's a mistake to have the FK in Book.
Instead I'd structure it something like this:

book(book_id*,title,etc)
user(user_id*,name,allowance,no_on_loan)
loan(user_id,book_id,out_date,due_date)

Maybe a statement like this could then be used to maintain an FK
constraint. (It's a redundancy but I'm not sure if there's any way of
tying an FK to derived data):

UPDATE user SET no_on_loan = (SELECT count(*) loans FROM loan WHERE
user_id = myuser GROUP BY user_id) WHERE user_id = myuser;

FWIW, personally, I'd manage the constraint in my interface (I'm
imagining some php front-end to this db) - but that's more to do with
my general ignorance of mysql constraints than anything else.