Changing primary key in tables

[CEV]

We have a database and three of the tables are setup in a relationship with
the EmployeeNumber as the primary key. I need to add a field for
SocialSecurityNumber to these tables and make that the primary key and setup
the relationship with those fields. Is it going to be as easy as it sounds
or will I run into other issues? I have different Queries and Reports
already setup but I'm thinking those will not be affected. Am I correct?
Please don't ask why I need to do this, that is another whole story.

Thanks,

CEV

 

[John W. Vinson]

We have a database and three of the tables are setup in a relationship with
the EmployeeNumber as the primary key. I need to add a field for
SocialSecurityNumber to these tables and make that the primary key and setup
the relationship with those fields. Is it going to be as easy as it sounds
or will I run into other issues? I have different Queries and Reports
already setup but I'm thinking those will not be affected. Am I correct?
Please don't ask why I need to do this, that is another whole story.

Thanks,

CEV

It may be a several-step process but it can certainly be done. You're probably
going to have to change the queries as well, but it's not hard.

Add the SocialSecurityNumber field (Text, 9 - *not* a Number datatype) to each
table, starting with the employee table. Enter all the SSN's if you haven't
already done so; it would be wise to run a Find Duplicates query to be sure
you don't have any dups.

Then run as many Update queries as you have child tables; join the tables on
the EmployeeNumber, and update the child table SocialSecurityNumber to

[MainTable].[SocialSecurityNumber]

The brackets are essential; use your own table name of course. Check to be
sure that there are no NULL values left in the tables.

You should then be able to delete all the relationships using your
EmployeeNumber, and create new ones using SocialSecurityNumber. Enforce
referential integrity of course!

You will probably need to select the Queries using these tables and change the
Join from joining on EmployeeNumber to joining on SocialSecurityNumber.

John W. Vinson [MVP]

 

[CEV]

Thanks for the reply John. I will give that a try.

Thanks,

CEV

We have a database and three of the tables are setup in a relationship
with
the EmployeeNumber as the primary key. I need to add a field for
SocialSecurityNumber to these tables and make that the primary key and
setup
the relationship with those fields. Is it going to be as easy as it sounds
or will I run into other issues? I have different Queries and Reports
already setup but I'm thinking those will not be affected. Am I correct?
Please don't ask why I need to do this, that is another whole story.

Thanks,

CEV

It may be a several-step process but it can certainly be done. You're
probably
going to have to change the queries as well, but it's not hard.

Add the SocialSecurityNumber field (Text, 9 - *not* a Number datatype) to
each
table, starting with the employee table. Enter all the SSN's if you
haven't
already done so; it would be wise to run a Find Duplicates query to be
sure
you don't have any dups.

Then run as many Update queries as you have child tables; join the tables
on
the EmployeeNumber, and update the child table SocialSecurityNumber to

[MainTable].[SocialSecurityNumber]

The brackets are essential; use your own table name of course. Check to be
sure that there are no NULL values left in the tables.

You should then be able to delete all the relationships using your
EmployeeNumber, and create new ones using SocialSecurityNumber. Enforce
referential integrity of course!

You will probably need to select the Queries using these tables and change
the
Join from joining on EmployeeNumber to joining on SocialSecurityNumber.

John W. Vinson [MVP]