relationships

[donnti]

i'm new to access, i am trying to create a relationship between two tables. I'm trying to Connect the primary key of Books (Book_Number) to the (Book_Number) in Borrowed_Books table.
it wont let me enforce referential integrity and i don't know why. i have all the same numbers so i don't really understand the popup that i am getting.


can anyone tell me what is wrong with the two fields that is not letting me create a relationship between them with referential integrity?
Library Database.accdb

[dniezby]

Is this the error you're getting?



What are you trying to cascade? If you delete a book from the library it deletes all the books with the same ID from Borrowed books?

[donnti]

i'm not sure what it means to cascade.
i'm only trying to enforce referential integrity bc they are meant to be the books from the table.
the employee id and customer id worked so i dont know why book id is not letting me create a relationship the same way

[dniezby]

i'm not sure what it means to cascade.
i'm only trying to enforce referential integrity bc they are meant to be the books from the table.
the employee id and customer id worked so i dont know why book id is not letting me create a relationship the same way

What i meant by "cascade" is that when you delete a record from the main table (books) it cascades through and deletes all the references from the related table.

So when you delete a book, it deletes it from the borrowed books table as well.

I'm assuming your borrowed books table is the books from your inventory that are loaned out?




Sent from my iPhone using Tapatalk

[donnti]

yes the borrowed books has Book ID to show which book was borrowed, so i have the book table that is supposed to be where the IDs are coming from, so i am trying to create a relationship but i'm getting that popup
i was trying to recreate this database DVDRENTALS (2).zip
i pretty much followed exactly what is in these tables with my own data and they were able to have DVDs related to the DVD ID in the rental table so i just was wondering if there was something wrong with my tables

[donnti]

should i have set the relationships before i filled the tables?

[John_G]

The problem is this:

Your Borrowed_Book table shows book number 24 has been borrowed, but there is no book 24 in the Books table. That is the situation causing a "violation of referential integrity" message.

In a one-to-many relationship (which you have), referential integrity means that every record on the "Many" side (Borrowed_Books) must have a matching record on the "One" side (Books), when looking at the field(s) which define the relationship. If you ask Access to "Enforce Referential Integrity", it will not let you add a record to the Borrowed_Books table if the book is not in the Books table first.

[John_G]

should i have set the relationships before i filled the tables?

No, it doesn't matter. Referring to my post #8 above, if you add a Book #24 to Books (or delete the record for book 24 from Borrowed_Books), you should be able to create the relationship with no problem.

[donnti]

thank you!

[dniezby]

Good catch John G! I didn't even think to look at the actual data as the source of the problem. Donnti, make sure to set this list to "solved" if that fixed it for you.


Sent from my iPhone using Tapatalk