Design Normal Form in sql server

Question

I have 2 table (FirstTable & SecondTable). My FirstTable is header table and SecondTable is detail table, but FirstTable has complex primary key. How I can have a reference in my SecondTable to first Table that be NORMAL and best choice.

Answer 1

There are at least three ways to do this in your case. The way you choose depends on information you haven't told us yet.

Think hard before you choose. The fact that you're asking such a very basic question suggests that you're likely to make mistakes in the implementation. (That's an observation, not a criticism.) Work on a scratch copy of your database first. Expect to destroy it.

1. Store the entire primary key of FirstTable in SecondTable. That means adding a column for Key_F2 in SecondTable, populating it with the right data, dropping the old constraint, and adding a new one, which will include a clause like foreign key (Key_F1, Key_F2) references FirstTable (Key_F1, Key_F2).
2. Declare a UNIQUE constraint on FirstTable.Key_F1. Then you can add a new constraint to SecondTable which will include a clause like foreign key (Key_F1) references FirstTable (Key_F1). Of course this will only work if values in FirstTable.Key_F1 are unique.
3. Add a column to SecondTable to store the value in FirstTable.ID, and populate that column with the right data. Then you can drop the column SecondTable.Key_F2, and set a foreign key reference from SecondTable.ID to FirstTable.ID.