Primary Key & Foreign Key Question

[irasmith]

Hello one and all,

 

I've got a question regarding primary key and foreign key theory.

 

This will be a simple example to get across what I am trying to ask.

 

Say you have table 1 with a primary key.

 

Talbe 2 has a foreign key that ties back to table 1.

 

Do you really also need to declare for table 2 a primary key if really what you would normally think of as the primary key is really the foreign key?

 

Or another way of asking the question is if the foreign key relates back to another table on the primary key, is there any need of declaring a primary key in the second table?

 

Hope this wasn't too confusing. I know it's late this evening and perhaps my brain has gone mushy on me;)

[Nerseus]

If Table2's Foreign Key is unique, then you have a one to one join between Table1 (with the Primary Key) and Table2 (or a one to zero depending on your needs).

 

If you have a one to many (or one-to-zero-to-many) relationship then you would normally have a primary key in Table2. Technically, if Table2 never gets updated or deleted from you might not need it, but if you do update or delete then you need a way to locate one exact row.

 

Now a Primary Key in Table2 doesn't necessarily have to be one field. A common solution is to use two fields: the first field is the foreign key and the second field is a sequence. The combination of the two fields in Table2 would make a unique key. Personally, I hate those - it just makes it harder to update and insert, especially when users get to see the Sequence. Pretty soon they want the sequence kept in sequence even as some records are deleted or moved around. I'd rather have Table2 have an identity column used for a Primary Key and have a separate field for the Sequence (if you even need a sequence). That's another story though :)

 

-Nerseus

[XyBoy]

Well, I don't quite agree with this. The relationship between Primary Key and Foreign Key depends on your "object structure".

 

Look at this scenarii :

 

1. An object "contains" other objects. For instance, a building contains flats. In this case, if the buildings are identified by "Building_IDs", I would identify each flat with "Building_ID", "Flat_ID" : the foreign key (building_id) is part of the primary key. Flat_ID is unique within a given building.

Advantage : when your application works on a given building, it will load the tables "buildings" and "flats" with the same 'where' clause ("where building_id=xxx"). Although a subset of the "flats" table is loaded, you can add a new flat for this building, and still compute its key : "building_id", "max(flat_id)+1" (computed on the loaded rows, and not on all the table ! This is important !). So you don't need an round-trip to the database (as you would if you used a Sequence or a Primary Key that does not contain Building_id).

 

2. An object contains "references" to other objects (one-to-many). For instance, a team "loosely" contains employees, it contains references to employees. It looks a little like the flats example, but although an employee belongs to only one team, it can be moved from one team to another (a flat cannot be moved from one building to another ;-) ).

You would not like to have to change an employee's ID when you move him from a team to another. So the team ID should not be part of the employee primary key. Hence the primary key for the employee would be "Employee_ID", and the foreign key would be "team_id", in an other field. In this case you would need to use a sequence to generate the "Employee_IDs". If you don't, you will have to compute the employee_id based on the full "employee" table ("select max(employee_id) + 1 from employee;"). In either case you get a round-trip to the database.

 

3. An object contains references to other objects (many-to-many). For instance, an order contains products. In this case, a product can be part of several orders, so it's a many-to-many relationship, and you need a separate ("intermediate") table : "OrderDetails". In fact it is a combination of cases 2 & 3 : the Order object contains OrderDetails objects, so the Primary Key of the OrderDetails table would be "Order_ID", "OrderDetail_ID" (the foreign key to the order table is part of the PK of the orderDetail table ; OrderDetail_ID is unique within a given order), and the foreign key to the product would be stored in another field, "Product_ID".

 

Other cases are probably a combination of these cases. In any case, you always have to think of what data will be loaded by your application, and when. The datamodel is not to be too dependent on your application, but thinking of the way your application will handle objects will help you better determine the boundaries of the objects and the relationships between them.

 

Good luck !

Olivier.

[irasmith]

I would like to thank both of you for your thoughts and input on my question.

 

Actually, between the combination of responses I was able to learn a lot more and it has put some new light onto my project. I will take the advice from both of you as I review my existing data sturcture to make sure it is what I want to work with at the start of this project. I realize structure may change as you move into a project and that is fine, I just try my best to remove all the initial problems so things go smoother later on.

 

Thanks and I'm sure I'll be posting more as time rools on. I'm really having fun working with VB.NET.:)