Enforcing Referenatial Integrity

[Stevefsn]

I am tring to build a relationship that will update other tables. When I update the primary key in the main table it does not update the same data in a secondary table. I have tryed to set the enforcing integrity, however the block is disabled?? How do I enable this box?

 

[Uncle Gizmo]

You can't enforce referential integrety if some of the date in the tables is duplicated. You will need to locate the dupicate data and remove it then the check box will be live.

 

[Pat Hartman]

Although Jet supports Cascade Update, many RDBMS' do not because it is generally considered poor practice to allow a key value to change. It is far better when you have a key field that is changable to use an autonumber as the pk and simply create a unique index on the other "key" field. That way you can change it at will and not worry about cascading the update. You can still search on it efficiently because you have created a unique index.

 

[Stevefsn]

Pat,

Thank you for your respones. The second table that I am building the relationship, I am not using a PK. The PK is a autonumber in the second table. the relationship type is "one to many". Do you have any thing us that I can look at that mite help me?

Steve

 

[Pat Hartman]

The primary key in the first table should also be an autonumber if that is the key that is changable.

tbl1:
tbl1ID (autonumber primary key)
UniqueID (indexed unique)
...

tbl2:
tbl2ID (autonumber primary key)
tbl1ID (foreign key to tbl1)
....


Notice that UniqueID does not appear in tbl2. That way you don't need to worry about cascading it if it changes. You can always retrieve it when you need it by joining tbl1 and tbl2.

 

[Stevefsn]

Pat,

Thanks for getting back to me. Let me give you more info on the tables that I am working with.

Table 1 Holds: unique info for people like names, address, and DOB. Each person is assigned a unique number (almost like a SSN) which is the PK. This unique numbers up date when they move to a differant department.

Table 2 Holds: holds the attendance for each of these people. The PK in the table is a autonumber which allows us to have dups in it. The changes that I am making are to the unique number when is chagnes in the first table, we want it to carrie all the attendance info as they move from department to department.

This is why I wanted to have the info to cascade from table 1 to table 2. The database has this button inactive.

Let me know your thoughts.

Steve

 

[Pat Hartman]

The primary key design of your application is left over from another era. Department should be a separate field in the table not embedded in the employee's ID. What happens to all the paperwork when an employee changes department? Do you have to retrieve all the written records and change them?

Take a long look at the structure that I suggested. It allows you to link the data in table 2 to the data in table 1 without having to change the foreign key in table 2. It allows you to continue to use the DeptEmployeeID as a lookup field. There is nothing that you have said that suggests to me that the structure I posted won't work for you.

Going back to your original post -

I have tryed to set the enforcing integrity, however the block is disabled?? How do I enable this box?

- The reason that you cannot enforce RI on this relationship is because you already have bad data in the tables. There is some data in table 2 that has no matching record in table 1. That data would need to be corrected or removed before RI can be established.

If you want to know how to convert your existing data to use the proper keys let me know and I'll tell you how.

 

[Stevefsn]

Pat,

I just tried what you said to, It still is not letting change the information in table 2 with out losing information.

Do you have anything else for me to try?

Steve

 

[Pat Hartman]

If you don't need to save the data, the easiest thing is to just delete it, create the relationships, and then add the data back in. If there is too much data, you'll need to use some "Find unmatched" queries to identify the "orphan" records in the many-side table. Correct the foreign key so the orphan points to its correct parent or delete the orphan row.

 

[Stevefsn]

Pat,

I wanted to let you know I found a way to make it work. In table 2 I had a autonumber as the PK, I took the PK for all together. Now there's no PK in table. When I change the data in table 1 it doesn't delete it from table 2 now.

Thank you for your help.

Steve

 

[Pat Hartman]

Removing the pk from table2 was not the solution. All tables should have a unique primary key. There is still something wrong with your tables. You don't have RI enforced and so you can create or make orphan records in table 2.