>I am confused now <g>. Up above you said that "the Referential integrity must be done via the PARENT table primary key and Foreign Key in the Child Table(s)". I agree with this statement and understand it.
>
>Then, in your last message you said "Candidate key is a Field in the Child table that hold a PK value of the Parent table". Unless I have been misunderstanding the term "candidate" field for a long time, I disagree with this. I would change the term "Candidate" in your sentence with "Foreign." My understanding of a Candidate key (and maybe I was wrong for a long time) is that it is any unique entry field in a table. And this unique field in the table can be "made" into a Primary key.


My mistake, it should be Foreign key :-)


>
>So in my database design a Primary key field is a field where user makes an entry (e.g. CustomerID). The child tables also have field CustomerID. And the Referential Integrity is built on this Primary key field (CustomerID in Parent to CustomerID in Child). Then, in the Child table there is a Primary (surrogate) key, identity field. And what adds to the confusing is that the Parent table (in my database) also has an identify field. And what I was saying is that the identify field in the Parent table may not be necessary; or, at least, I see no use for it right now.
>
>(The point of my discussing this with you, Borislav, is not to argue, but rather so that I finally have all these terms firmly understood in my head).

I do not like USER to enter values in the PK fields.
Also, you have two tables now and it is easy to change CustomerId, but think what could happens if you need to change its value to 20 or 30 tables?
So let user have its CustomerId to enter, but base table relations and RI to the Identity field. No need to have CustomerId field in other table(s), just that ID field.
Something like that:

             Customer table
             -------------------------------------------------------
      -------Id int identity PK, CustomerId int (or whatever), Name....
      |      -------------------------------------------------------
      |
      |      Child Table
      |      -------------------------------------------------------
      -----> CustFK int, Id int identity PK, other fields
             -------------------------------------------------------

That way when you must change CustomerId field you will change it only in the Parent Table. All other relations remain the same.