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.

 

[Pat Hartman]

Remove the 0 default from all ForeignKey fields.
Most FK's are required but not all. It depends on the actual relationship. For example tblOrders-tblOrderDetails. This is a hierarchical relationship and it makes no sense whatsoever to have a row in OrderDetails without a row in Orders Therefore, you would set the FK in tblOrderDetails to required. Therefore, you cannot enter a row without a FK value. The additional RI relationship will not allow you to enter a FK in tblOrderDetails that does not correspond to a valid row in tblOrders. That was what was happening when you had the default of 0 in the field. Since 0 can never be valid, it is poor practice to leave it as the default. There is no logical default because you have no such thing as a "blanket" order so you don't know ahead of time what the default might be.

If you were working on an employee database for a Connecticut company, you could set the FK default in the state field to CT since most of the employees will be residents of Connecticut. But if your company was located in Danbury, a large percentage would live in NY so you might decide to not have a default. The default would be null but the field would be required so you could not save the record without a valid state code.

The relationship between tblCustomers and tblOrders is not always hierarchical because in some cases, you might have a walk-in customer and not enter the customer in the database. Therefore tblOrders has a FK of CustomerID with a default of null but it is NOT set to required. That allows you to add an Order without a customer. However since there is a relationship defined and RI is enforced, you cannot add a record in tblOrders with a not null value in CustomerID UNLESS that value matches a record in tblCustomers.

So, RI doesn't do it all. Business rules and common sense need to be applied also.

 

[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.

 

[Pat Hartman]

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.

As I said in one of my examples, only YOU know if a customer is required. That isn't a cast in concrete rule for all order entry applications. When a customer is required for the order, you just make the FK required but, never default the FK to 0 unless you actually have a 0 record which you won't ever unless you use an append query to create it.