Relationships and SQL 2005 (1 Viewer)

[jlathem]

I have a MS Access front end with a SQL 2005 back end.

We just started using the database and needed to make a change in one of the records. The Primary key for the Cash_Record table (the parent table) is a check control number (CR_CCN) and the data was keyed in wrong.

There is a sub record with the check control number to show a relationship and when I tried to change the parent table is when I received an error and found that the enforcement of the relationships were either not set or got dropped during the upsizing process.

Question I have is can I just go into the tables using the front end and reset them then, use SSMA to resave the tables. OR is there a better way to get this done?

The SQL tables do have data in them but it is not much and if it gets blown away we can reenter it.

Thanks in advance for any assistance you can offer.

James

 

[boblarson]

This is one example of why you should use an autonumber for a PRIMARY KEY and not rely on someone to input a number that is a key. The KEY is for maintaining the data relationships and integrity and you would be fine if someone typed in the wrong number you could just go fix it in the ONE table it was in. But now you have a problem.

Delete the child record first and then change the main record and then add the child in again.

(and don't use human input as primary keys in the future).

 

[jlathem]

Thanks for the information Bob.

This is the first (and LAST) time I did this!

I have a couple more fields that I need to enforce integrity on.

How would you suggest I do that now that the tables have been upsized?

 

[boblarson]

You can set a non-duplicate index on the fields in the SQL Server tables, but normally I would just set it up to handle it in my input forms by validating on the Before Update event. That way you catch it BEFORE it generates an error (and with SQL Server sometimes you don't just get an error unless you ask for it).

 

[jlathem]

Ah, thanks Bob!

James