Primary key naming?

  • Thread starter Thread starter Marco Simone
  • Start date Start date
M

Marco Simone

I have two tables in relatinship 'one to one'. Is it mistake that they have
same name of primary key?
Could primary keys of different tables have same name?

Thanks for helping, Marco
 
Yes they can, but it can get confusing. Some people are okay with this,
however I personally prefer to give fields meaningful names. For example,
the ID field on a Customers table would be lngCustomerID ... on an Account
table it would be lngAccountID.
--
Scott McDaniel
CS Computer Software
Visual Basic - Access - Sql Server - ASP

"Smash forehead on keyboard to continue ... "
 
No problems but it can get confusing. I prefer to use a different name or
at least add a prefix "oto_" to the Field in the second Table just like I
use the prefix "frg_" for the ForeignKey Field.
 
I have two tables in relatinship 'one to one'. Is it mistake that they have
same name of primary key?
Could primary keys of different tables have same name?
Click to expand...

The name is irrelevant in this context: Access doesn't care whether
the linked field is named [ID] or [This Table Has No Primary Key] or
[zqXX23]. The reason it's giving you a one to one relationship is that
you have a unique Index (such as a primary key) on both fields.

For a one to many index you should link from the primary key of the
"one" side table to a "foreign key" - a field OTHER THAN the primary
key - of the second table. The datatype must match; if the "one" side
table has an Autonumber, the foreign key must be a Long Integer.
 
Virtually all my tables use the autonumber default of ID.

This means I don't have to guess, or even worry about what the key name is.

For all related tables (forighen keys), you use the tablename +id.


tblPeople pk:id


tblChildren pk:id
fk:tblPeople_id


Using the above means you have 100% consistency in your designs, never need
to guess what the pk is, and if you know the name of the parent table, then
you instantly know the name of fk. What could be more simple? So, in
tblChildren, if I do have a relation to tblPeople (the parent table), then I
instantly know that a key of tblPeople_Id is the fk.
 
You probably want linked PK and FK fields in different tables to have a
similar fieldname. PK fields in different tables would not have similar
names. I'd question why you would in any circumstances be linking 1:1 tables
instead of building those fields into 1 table, although there may be valid
reasons.
 
Back
Top