FK / PK, and FK null Question

I am designing some tables for my user content website and I am lost on the FK concept for this situation. What is the different in these two cases:

Case A:

Friend table

user_id (FK)

friend_id(FK)

Case B:

Friend table

id (PK)

user_id (FK)

friend_id(FK)

What I am trying to understand is one table has 2 FKs and 1 has 2 FKS + 1 PK. Most schema I have seen for friends have Case A that is only 2 FKs and no PK. But most other tables I see have case B of a PK. So what is the difference/advantage/disadvantage with both these tables an to use which one?

---

Second question I had is if I have a PK in table A and use it as a FK in table B, does it have to be NOT NULL in table B or can it be NULL even though in table A being a PK is has to be NOT NULL? An example of this is mapping City and State. State in state table is required开发者_StackOverflow as NOT NULL. In city table I am storing all values as NOT NULL DEFAULT 0 as not cities have states (world cities) but i dont want to store NULL in db so i am writing 0s for all null values just to keep the cells occupied.

---

In Case A, while user_id and friend_id are FK's they both are most probably a composite primary key. In Case B, there is a separate PK (id) to identify each unique friendship. They both actually do the same thing but according to my opinion, the id in 2nd case is redundant as the PK(user_id,friend_id) can act together to become a composite primary key which will uniquely identify each friendship. I suggest you read a good book on normalization :)

---

According to the information given, the difference between A and B is that B has an extra column and key constraint. Which design makes sense really depends on the requirements and what the data means to you.

It may be that in A the primary key is actually the compound key: (user_id, friend_id). In that case the difference between the two alternatives is that A does not allow duplicate combinations of user and friend whereas B does allow those duplicates. That's a significant difference.

Nullable foreign keys are best avoided. If you have an attribute that doesn't always apply then you could always redesign your tables such that you don't need to have a nullable column. Put the state in another table which you don't need to populate if the city doesn't have a state.

---

Case A is recommended.

Case B is often used when you need to migrate a long composite key into another table. For example, let's say that we need to create a Many-to-Many relationship (T3) between tables T1 and T2.

Case A (natural keys):
table T1(pk1, pk2, pk3, pk4, someValue)
table T2(pka, pkb, pkc, pkd, otherValue)
table T3(pk1, pk2, pk3, pk4, pka, pkb, pkc, pkd, create_date)

Three unique indexes are needed (one in each table). It is easy to query T3 directly.

Case B (surrogate keys):
table T1(t1_id, pk1, pk2, pk3, pk4, someValue)
table T2(t2_id, pka, pkb, pkc, pkd, otherValue)
table T3(t1_id, t2_id, create_date)

Five unique indexes are neeed. The same three as in case A, plus one for each surrogate key. T3 now becomes much smaller, but at the expense of no longer being able to query the M:M relationship without joining back to either T1 or T2. This may or may not be a big deal for any typical application.

I think that pretty much sums it up.