Adding tables to existing database

zbird

Registered User.
Local time
Today, 09:04
Joined
Nov 21, 2011
Messages
28
I would like to add a table to an existing database. I have already put 300 records in the main table and I would like to add the new table connected to this main one with a one-to-one relationship. Is there anything that I have to be aware of if I tick the Enforce Referencial Integrity? Can I use the same ID from the main table as a connection point?

Many thanks,
Susanna
 
Is there anything that I have to be aware of if I tick the Enforce Referencial Integrity?

You'll have a data structure that is much harder to corrupt than not having it?

Without referential integrity you could have, for the sake of a very basic example

Code:
TblCustomers
------------
CustomerID   Name
1            Fred
2            Jane


tblOrders
---------
OrderID  customerID  Item
1           3        Ipod
2           2        Iphone

Looking at orders, you have a customerID of 3, but ID 3 doesn't exist in the customer table. How did that happen? did you accidentally add an order with the wrong ID or was customer 3 deleted from the table?

You can also delete Jane from the customers table. Now you have two Orders that apparently relate to customers that don't exist.

With Referential Integrity, you wouldn't be able to create an order with a customerID of 3 because it doesn't exist in the customer table and your application would raise an error.

Likewise you can't delete Jane from the Customer table because she has a related Order in the orders table. You'll also get an error that you can't delete Jane because there are related records elsewhere.

If you really want to delete Jane, you have to delete all her orders first.

You could turn on "cascade on update or delete". With this enabled you can delete Jane and the database will automatically delete all the related orders in the order table. This is a BAD thing.

I'm sure that you write fabulous SQL and never, ever make a mistake, but if you accidentally deleted Jane, perhaps your delete query said "DELETE from tblCustomers WHERE tblcustomers.ID = 2" instead of 23. Now you've not only accidentally deleted Jane but you've also deleted all of her orders as well.

That's an incredibly basic overview of it, but I hope it gives some insight into what referential integrity does.
 
Thank you for all these. Luckily, I need to keep all my 'customers' for future reference and I want to avoid that people who access the database, including volunteers, be able to delete any 'customers' and their data. So referencial integrity actually should be ticked in this case, shouldn't it?

My main problem is that I can't add a new table to my existing 'customers' table with the referencial integrity ticked.

If I don't tick referencial integrity, I need to enter the 'customer ID' on the form each time when I would like to add this new information to them, unless I create a Query as a base of my form, where I use the CustomerID and all fields from the new table. But there is only one problem left: the subform navigation buttons are doubled up and I don't understand why. The second one look like not a real one, as it doesn't go further than the second record. (?)

When I ticked the referencial integrity, I tried to enter each CustomerID in it before filling any other fields of this table, but it is writing an error message that 'Table can't contain Nill value'. The new table has its own ID as well, however it is empty so far. So at the end of the day it would be a one-to-many relationship.
 
Last edited:
why do you think the relation is 1 to 1?

if it IS 1 to 1, you do not actually need another table
 

Users who are viewing this thread

Back
Top Bottom