Different Primary Key Data Types for a Junction Table 1

[dedren]

When I first designed this database several years ago I chose to make my main table’s primary key a hybrid of the year and an autonumber so that it looked like this: 11-1234. This means that its data type had to be Text. Now, in an effort to create a junction table it has come around to bite me.

My questions are:
If I add an autonumber to my main table and make it the primary key how will it affect the 20-30 other tables, queries, forms, reports, that rely on it? And do I have to find and change all of them?

OR

Can I use a composite primary key of a text field and a number field in a junction table and still keep referential integrity somehow?

My goal is that I am trying to allow users to select multiple sites/buildings related to a fraud case so that they can generate reports/graphs that can see the amount of cases happening at a particular building or region.

 

[BlueHorizon]

Hi,

I don't know if changing the PK on the main table will fix your problem but... Q: How would changing this one table affect 20-30 other tables? Is this one table's PK connected to foreign keys on all those other tables?

Best,
Blue Horizon

 

[dedren]

I am not sure what would happen, which is part of the problem. Yes, it is only seven tables (see attached image), but there are lots of forms, sub-forms, and reports.

 

[MajP]

1)Add an autonumber field to your primary table
2)Ensure you have relationships between your primary and all child tables
3)Ensure you check "Cascade Updates" checked. This says if you change the value of the primary key it will change the value in all child tables. So if you change 11-1234 to 22 all child tables would automatically change the FK to 22.
4)Run an update query on your primary key changing the value of the old pk to the value in the autonumber field.
Ex. 11-1234 would now become some auto number value of say 22
At the same time every child table (because of cascade updates) will also change to 22.
5)Now you would have an autonumber field and your old field with corresponding values.
6)Now delete all the relationships in the relationship window
6)Now make the autonumber field the PK
7)Now recreate the relationship to the new autonumber PK.

Now everything is related to the autonumber field and all parent child relations are saved.

 

[MajP]

forgot a step
6)Now make the autonumber field the PK
6.5) In the child tables change the FK from text to numeric.
7)Now recreate the relationship to the new autonumber PK