×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Different Primary Key Data Types for a Junction Table

Different Primary Key Data Types for a Junction Table

Different Primary Key Data Types for a Junction Table

(OP)
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.

RE: Different Primary Key Data Types for a Junction Table

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 2thumbsup
 

RE: Different Primary Key Data Types for a Junction Table

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.

RE: Different Primary Key Data Types for a Junction Table

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close