May 21, 2014 at 8:20 am
i have migrated a database from ms access to sql server and application developer is not available. i was told migrate database from access to sql server and I have to create primary and foreign key relationships. I don't even know the whole uses of the some of the tables.
there are 31 tables and there is one column that they share all of them, called UID that is it. goes like this
is it possible to make UID both PRIMARY of all the tables and FOREIGN KEYS OF ALL THE TABLE. THANKS
customers
UID FnameLnameDOB
1JakeJazzy 1/2/1977
2JakeJazzy 1/3/1977
3JakeJazzy 1/4/1977
4JakeJazzy 1/5/1977
5JakeJazzy 1/6/1977
6JakeJazzy 1/7/1977
7JakeJazzy 1/8/1977
orders
*****************
UID FnameLnameDOB
1JakeJazzy 1/2/1977
2JakeJazzy 1/3/1977
3JakeJazzy 1/4/1977
4JakeJazzy 1/5/1977
5JakeJazzy 1/6/1977
6JakeJazzy 1/7/1977
7JakeJazzy 1/8/1977
bending orders
*********************
UID FnameLnameDOB
1JakeJazzy 1/2/1977
2JakeJazzy 1/3/1977
3JakeJazzy 1/4/1977
4JakeJazzy 1/5/1977
5JakeJazzy 1/6/1977
6JakeJazzy 1/7/1977
7JakeJazzy 1/8/1977
status
************
UID FnameLnameDOB
1JakeJazzy 1/2/1977
2JakeJazzy 1/3/1977
3JakeJazzy 1/4/1977
4JakeJazzy 1/5/1977
5JakeJazzy 1/6/1977
6JakeJazzy 1/7/1977
7JakeJazzy 1/8/1977
....
May 21, 2014 at 10:10 am
Nassan (5/21/2014)
i have migrated a database from ms access to sql server and application developer is not available. i was told migrate database from access to sql server and I have to create primary and foreign key relationships. I don't even know the whole uses of the some of the tables.there are 31 tables and there is one column that they share all of them, called UID that is it. goes like this
is it possible to make UID both PRIMARY of all the tables and FOREIGN KEYS OF ALL THE TABLE. THANKS
customers
UID FnameLnameDOB
1JakeJazzy 1/2/1977
2JakeJazzy 1/3/1977
3JakeJazzy 1/4/1977
4JakeJazzy 1/5/1977
5JakeJazzy 1/6/1977
6JakeJazzy 1/7/1977
7JakeJazzy 1/8/1977
orders
*****************
UID FnameLnameDOB
1JakeJazzy 1/2/1977
2JakeJazzy 1/3/1977
3JakeJazzy 1/4/1977
4JakeJazzy 1/5/1977
5JakeJazzy 1/6/1977
6JakeJazzy 1/7/1977
7JakeJazzy 1/8/1977
bending orders
*********************
UID FnameLnameDOB
1JakeJazzy 1/2/1977
2JakeJazzy 1/3/1977
3JakeJazzy 1/4/1977
4JakeJazzy 1/5/1977
5JakeJazzy 1/6/1977
6JakeJazzy 1/7/1977
7JakeJazzy 1/8/1977
status
************
UID FnameLnameDOB
1JakeJazzy 1/2/1977
2JakeJazzy 1/3/1977
3JakeJazzy 1/4/1977
4JakeJazzy 1/5/1977
5JakeJazzy 1/6/1977
6JakeJazzy 1/7/1977
7JakeJazzy 1/8/1977
....
??? Based on the above, all of your tables have the same structure and data.
May 23, 2014 at 7:14 am
sorry, I did not mention that this tables are just for demo and not actual tables
and I was trying to show you that UID exists all the tables 31 of them.
can that UID be used as foreignkeys and primary keys?
May 23, 2014 at 9:49 am
Nassan (5/21/2014)
i have migrated a database from ms access to sql server and application developer is not available. i was told migrate database from access to sql server and I have to create primary and foreign key relationships. I don't even know the whole uses of the some of the tables.there are 31 tables and there is one column that they share all of them, called UID that is it. goes like this
is it possible to make UID both PRIMARY of all the tables and FOREIGN KEYS OF ALL THE TABLE. THANKS
customers
UID FnameLnameDOB
1JakeJazzy 1/2/1977
2JakeJazzy 1/3/1977
3JakeJazzy 1/4/1977
4JakeJazzy 1/5/1977
5JakeJazzy 1/6/1977
6JakeJazzy 1/7/1977
7JakeJazzy 1/8/1977
orders
*****************
UID FnameLnameDOB
1JakeJazzy 1/2/1977
2JakeJazzy 1/3/1977
3JakeJazzy 1/4/1977
4JakeJazzy 1/5/1977
5JakeJazzy 1/6/1977
6JakeJazzy 1/7/1977
7JakeJazzy 1/8/1977
bending orders
*********************
UID FnameLnameDOB
1JakeJazzy 1/2/1977
2JakeJazzy 1/3/1977
3JakeJazzy 1/4/1977
4JakeJazzy 1/5/1977
5JakeJazzy 1/6/1977
6JakeJazzy 1/7/1977
7JakeJazzy 1/8/1977
status
************
UID FnameLnameDOB
1JakeJazzy 1/2/1977
2JakeJazzy 1/3/1977
3JakeJazzy 1/4/1977
4JakeJazzy 1/5/1977
5JakeJazzy 1/6/1977
6JakeJazzy 1/7/1977
7JakeJazzy 1/8/1977
....
Still not sure what it is you are doing here, but if the UID column uniquely identifies a single row then yes it can be a primary key in that table. If there is relationship between tables based on the UID, then yes it can be used as a foreign key.
Please note, I am NOT telling you that this will in fact work for your particular situation as I have no knowledge of what you are trying to accomplish or if that relationship makes sense between all the tables.
May 23, 2014 at 9:50 am
Without seeing the correct table structures you have, it is impossible to give you a full answer BUT it looks like the UID is a unique surrogate key for each record in EACH table. So the UID in Customers is not the same field in Orders.
You should probably set the UID as the primary key in each table.
You will need to set up foreign key relationships based on linking the UID to another field, for example, I hope your Orders table has a field that is an integer that represents the UID in the Customer table.
How were these relationships defined in Access?
I'm only guessing because you haven't given us your table structures.
June 10, 2014 at 11:34 am
thanks.
the question was can one column that exists in all the tables in a database be used as a primary key for each table and foreign key for each table since this column exists in all the tables and is int data type
thanks
... sorry i cant share the real table structure as this is gov project and I'm only contractor little guy here
June 10, 2014 at 11:48 am
Yes, that IS possible, but highly unlikely.
It appears that the original designer of the database named all of the ID fields in all of the tables the same. If you check out the structure of the table in Access, this is probably set as a long integer as the datatype, and the field is set to increment.
There is likely no correlation between UID #1 in table X, and UID #1 in table Y.
However, if table X contained a field tableY_UID, then it is likely that there is a relation in place.
You need to run some queries to determine if the various tables are in fact related. Do you have access to the user interface? Look up a record in the UI, and then attempt to query the database to find all of the associated records. You can then extrapolate the relationships.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply