2

I'm trying to make a database structure of an online flight booking website. Its just a project of mine. I was wondering i could use a foreign key as my primary key on a table.

Here how one of my table looks like:

Table name: Customer_Account it has : Account_id (PK), username and password.

And my other table is: Customer_Info it has: Account_id ,first name,last name and other personal info.

What I had in mind was, is it OK if I use the (Account_id) on my Customer_info table as my Primary key since it is a Foreign key that references to Customer_Account 's (Account_id) ?

If this is possible is it alright if use the foreign key as my primary key all the time when it is the ID that I really need for that table?

Walter Mitty
  • 18,205
  • 2
  • 28
  • 58
TheAsker101
  • 95
  • 7

2 Answers2

2

This is perfectly acceptable, although I must wonder why you need two tables in the first place. If every (or even most) of the customers have the additional information, a simpler approach would be to just have all the columns in customer_account table.

Mureinik
  • 297,002
  • 52
  • 306
  • 350
2

Your question addresses the technique known as "shared primary key", so I added that tag to your question.

The answer, in brief, is yes. You make a foreign key the primary key when you want to enforce a one-to-one relationship between two tables. This is frequently the case in IS-A relationships, which are typical of class-subclass (type-subtype) situations.

It's often the case that you can acheive the same result by combining the two tables into one table. But there are situations where the subclass data is so different that it's more satisfactory to have two or more tables. See "class table inheritance" for a discussion of this situation.

Walter Mitty
  • 18,205
  • 2
  • 28
  • 58
  • I would look up more on class table inheritance to get more info. anyways thanks for the answer. it helped me a lot. – TheAsker101 Aug 06 '16 at 12:29