Enforce referential integrity

[HabibValil]

I have two simple tables: Tcustomers and Torders. I have related the CustomerID of Tcustomers to CustomerID in Torders. A one-to many relationship with Enforce Referential Integrity checked. And cascade update and cascade delete both checked.

Now I should be unable to enter a new Order in Torder unless I introduce its Customer in the same record. right? But without having any customers in Tcustomers, I can still add orders.
to be more precise: if CustomerID default value in Torder is set to zero ( as it is by default when you create a number field in table), I can not add an Order without a related CustomerID. But if I remove that 0 in the default value of CustomerID in Torders, then I can add Orders with the CustomerID field empty(Null?).

why can we add orphan Orders when the CustomerID field in Torders is empty(Null)?

I can set the "Required" property (for CustomerID in Torders) to "Yes" and prevent orphan Orders. But I expect the referential integrity to do that, Am I misunderstanding something?

 

[GinaWhipp]

Referential Integrity is to ensure the data from Tcustomers is available to Torders and exists in Tcustomers. It does not prevent you from not entering a customer. It only prevents you from entering a customer that does not yet exist. So you can make Users entering by setting up validation on the control on the Form or in the field in Torders. This might help to explain it better... http://www.functionx.com/access2003/Lesson14.htm

 

[HabibValil]

It does not prevent you from not entering a customer.

I know we can add customers. but why can we add orders without relating the order to a customer?

 

[theDBguy]

I know we can add customers. but why can we add orders without relating the order to a customer?

I think you already mentioned the answer, which is: "because you didn't require the order records to have a related customer value (by not setting it to "required").

To clarify, when you set the Default Value of the customer FK to 0, were you able to add any orders when you have an empty customers table?

 

[HabibValil]

I think you already mentioned the answer, which is: "because you didn't require the order records to have a related customer value (by not setting it to "required").

To clarify, when you set the Default Value of the customer FK to 0, were you able to add any orders when you have an empty customers table?

So, is it true to say:
"In order to avoid Orders without a customer, it is not enough to check referential integrity. I must also set the Required property to Yes, Or I must leave that default value to 0." ?

 

[theDBguy]

So, is it true to say:
"In order to avoid Orders without a customer, it is not enough to check referential integrity. I must also set the Required property to Yes, Or I must leave that default value to 0." ?

Well, you must answer my question first to help clarify things. When the default value is 0, were you able to add orders when the customers table is empty?

 

[HabibValil]

Well, you must answer my question first to help clarify things. When the default value is 0, were you able to add orders when the customers table is empty?

No. With the default value of 0, I could not add an Order

 

[GinaWhipp]

So, is it true to say:
"In order to avoid Orders without a customer, it is not enough to check referential integrity. I must also set the Required property to Yes, Or I must leave that default value to 0." ?

Correct because there is no Customer ID of 0 so you can't use that in Torders. Referential Integrity will prevent you from entering a Customer ID that does not exist but it will not prevent you from not entering a Customer ID. You need some sort of *other* validation for that.

 

[theDBguy]

No. With the default value of 0, I could not add an Order

Hi. Thanks for the confirmation. As others have already said. RI only enforces existing matches. It doesn't make sense to enter a 0 value for the FK (unless you actually have a 0 parent ID). Leaving the FK empty is allowed. But if you don't want orphaned records, then you must also set the FK field as NOT NULL or required.

 

[gemma-the-husky]

The problem will be if you don't enter a customer AT ALL for your order. Access won't enforce the RI against a null.

You need to set the additional constraint in the orders table that the customer ID is required and cannot be null.

Maybe that's already been noted. (which I see it has!)

Generally you will have a drop down on your order form to select a customer.
Alternatively, you can have a customers form, and select the customer using that, then the orders form can be a subform to the customers form related by the customer ID. If you do it that way, Access will automatically fill in the customer ID on the order.