How can I use the same ID number across a series of tables?

R

RichardCP

I need to create an ID number for each of our customers. Information
relating to them is spread across 6 tables. However, If I set the ID number
as a primary key the database will not allow me to use the same number in the
other tables. But I need to do this so that all tables are linked to one ID
number and one customer. Is there a way to do this?
 
R

Rick Brandt

RichardCP said:
I need to create an ID number for each of our customers. Information
relating to them is spread across 6 tables. However, If I set the ID
number as a primary key the database will not allow me to use the
same number in the other tables. But I need to do this so that all
tables are linked to one ID number and one customer. Is there a way
to do this?
Click to expand...

Setting a field as a Primary Key in one table imposes no restrictions on the use
of that value in other tables. Please explain further.

It sounds like you have multiple tables with a one-to-one relationship. While
not incorrect, the use of one-to-one relationships is somewhat rare. Is there a
particular reason why you don't just use one table?

If the reason is "too many fields" then this usually means that the data model
is flawed and simply spreading the data over multiple tables in a 1-1
relationship is sledom the correct "solution".
 
K

Ken Sheridan

I can only assume that by 'not allow me to use the same number in the
other tables' you mean that you can't use the same number in more than one
row of each of those tables. As an example, you might have a Customers table
with a primary key CustomerID. Related to this in a one-to-many relationship
might be an Orders table with one or more rows per customer. In the Orders
table the CustomerID column would be a foreign key, so should not be
designated as the primary key (have a separate OrderID for that) nor
otherwise indexed uniquely. Instead index it non-uniquely (allow
duplicates). You can then join the tables on the CustomerID columns and
enter as many rows per customer in the Orders table as you wish.

If you have any tables related one-to-one to Customers then CustomerID
should be the primary key for both, but if you use an autonumber CustomerID
in Customers don't do so in the other table; just use a long integer number
data type there. One-to-one relationships are used to model class/sub-class
relationships, e.g. Programmers would be a sub-class of Employees, so both
would have the EmployeeID column as the primary key. Classes and sub-classes
are where the latter shares all the attributes of the former, but the former
does not share the attributes of the latter.
 
Top