need help with primary keys and one to many relationships

[versuviusx]

ok
currently i have a table with these fields:

CustomerID
firstName
lastName
Billing address
city
State
PhoneNumber
EmailAddress
Sex
Username
password


ok my question is what kind of field should the CustomerID be?
should it be autonumber or just text
the reason i ask is that if i use autonumber and try to connect customerID to another Primary Key in a different table it doesn't work. so if i keep it all the primary keys text then it works.
the other problem is how do i ensure referential integrity with a 1 to many relationship. and what exactly is referential integrity?

 

[pbaldy]

Generally a number will be more efficient that text, but they must be consistent. I'd use number for all of them.

You set up referential integrity in the relationships window. It helps make sure you don't have orphaned records (a customer ID in a "many" table that doesn't exist in the "one" table).

 

[georgedwilkinson]

You should make all your keys (primary and foreign) number types. I saw your post earlier describing tables you had designed with text fields as the PKs and thought, he's/she's gonna run into trouble. And here we are.

If you need a text field for some reason to describe/define data in your table, make it a separate text field with a unique index.

 

[versuviusx]

ok so i have a table called customer table and a table called credit card
here is the customer table:
CustomerID*
firstName
lastName
city
state
email
phoneNumber

here is my credit card table
CustomerID*
creditcardNumber
ExpirationDate
SecurityCode
NameOnCard
CardType


ok so here is my question.
in each table i'm making CustomerID the primary key so i can link them together.
because you can only link primary keys together in a relationship right?
also shouldn't this be a 1-many relationship meaning that a customer can have many creditcards on file?
also if i make the CustomerID field an autoNumber in the customer table
should i make the CustomerID field in Creditcard table just a long integer number?

 

[pbaldy]

It does sound like a one to many relationship, yes. Also yes that it would be a long integer in the CC table.

 

[neileg]

CustomerID shouldn't be the PK in the credit card table. It is a foreign key. Customers can have more than one credit card so you have a one to many relationship.

 

[gemma-the-husky]

in your customer table you may need to have a customerref, as well as the customerid

then you can use the customerid (autonumber or not) as internal numeric to link your tables, and ALSO have the customer ref you normally use - ie sensible text A004 or whatever

doing it this way still lets you sort by the text customerref, but also lets you change this etc, if a customer gets taken over, changes name etc, which isnt so easy if the customerref is a foreign key in other tables